How to count cells

[vsr_kmb]

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 |
+-------------------------------------------------------------------+

 

[Max]

One play ..

We're going to use formulas which read straight off
the summary table labels in D2125, 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 D2125 would
be consistent with the actuals within the col "Entity",
it's best just to use your DV list within D2125.
(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

 

[vsr_kmb]

Thanks very much and it is excellent.

Thanks a lot.

Regards,
Ravi

 

[Max]

You're welcome, Ravi !
Thanks for the feedback ..