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