How to count cells
 

Hi,
I want to put a summary of Job orders in a table. (Excel (Zip) file
attached)
I am having a problem of total.
I want to make a total of only approved job orders not the all job
orders.
Also if the column contains hot / cold type, I need red and bold for
hot job order of complete row.
Also I want to be counted by job type and entity. For example, in the
total approved 7 permits, CONST entity 3 cold jobs and 1 hot job. how
can i display in the abstract table.
Can anyone help me please.


+-------------------------------------------------------------------+
|Filename: count.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4525 |
+-------------------------------------------------------------------+

--
vsr_kmb
------------------------------------------------------------------------
vsr_kmb's Profile: http://www.excelforum.com/member.php...o&userid=32834
View this thread: http://www.excelforum.com/showthread...hreadid=526634

How to count cells
 

One play ..

We're going to use formulas which read straight off
the summary table labels in D21:D25, and in E20:F20

Reference sheet: X in the sample construct at:
http://cjoint.com/?dBhWdb7LSU
vsr_kmb_count.xls

To ensure that the entities listed in D21:D25 would
be consistent with the actuals within the col "Entity",
it's best just to use your DV list within D21:D25.
(This has been done in the sample)

Similarly, we'll also change the labels in E20:F20 to read as just: H, C

Put in E21:
=SUMPRODUCT(($G$7:$G$16<"")*($B$7:$B$16=E$20)*($D $7:$D$16=$D21))
Copy across to F21, fill down to F25 to populate

Then just change G21:G25 to compute the horizontal totals
Put in G21, copy to G25: =SUM(E21:F21)

The above should return the correct figures within the summary table

(Think there were a few inconsistencies described in your post
versus what was in your actual source table)

------------------
Also if the column contains hot / cold type, I need red and bold for
hot job order of complete row.

Reference sheet: X (2) in the sample

To conditionally format lines
within the source table (red/bold font)
if the type is "H"

(Remove the current cell formatting so that
we can see the CF formatting work)

Select A7:G16
Click Format Cond Formatting
Formula is: =$B7="H"
Click Format button Font tab Red/bold OK
Click OK at the main dialog
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"vsr_kmb" wrote in
message ...

Hi,
I want to put a summary of Job orders in a table. (Excel (Zip) file
attached)
I am having a problem of total.
I want to make a total of only approved job orders not the all job
orders.
Also if the column contains hot / cold type, I need red and bold for
hot job order of complete row.
Also I want to be counted by job type and entity. For example, in the
total approved 7 permits, CONST entity 3 cold jobs and 1 hot job. how
can i display in the abstract table.
Can anyone help me please.


+-------------------------------------------------------------------+
|Filename: count.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4525 |
+-------------------------------------------------------------------+

--
vsr_kmb
------------------------------------------------------------------------
vsr_kmb's Profile:
http://www.excelforum.com/member.php...o&userid=32834
View this thread: http://www.excelforum.com/showthread...hreadid=526634

How to count cells
 

Thanks very much and it is excellent.

Thanks a lot.

Regards,
Ravi


--
vsr_kmb
------------------------------------------------------------------------
vsr_kmb's Profile: http://www.excelforum.com/member.php...o&userid=32834
View this thread: http://www.excelforum.com/showthread...hreadid=526634

How to count cells
 

You're welcome, Ravi !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"vsr_kmb" wrote in
message ...

Thanks very much and it is excellent.

Thanks a lot.

Regards,
Ravi


--
vsr_kmb
------------------------------------------------------------------------
vsr_kmb's Profile:
http://www.excelforum.com/member.php...o&userid=32834
View this thread: http://www.excelforum.com/showthread...hreadid=526634