Summarize work per person using CountIF, DCountA or what?

[mrstaggart]

I think this is close to what was in thread "thread68-1595108", but not quite.

I have a work page from which 3 different people are assigned orders to fill and, as they complete them, they enter the text result of its status - "D" for done, "M" for missing or "H" for hold. I want to summarize this on a separate sheet in total (total count of M's) and detail by person.

So, if my work data looks like this:
ColA ColB ColC
Order# Assigned Status
2010-1 Bob D
2010-2 Bob M
2010-3 Bob D
2010-4 Mike D
2010-5 Mike D
2010-6 Tom H

and my summary is structured like this:
ColA ColB ColC ColD
Total Done (D) Missing(M) Hold(H)
Bob 3 2 1
Mike 2 2
Tom 1 1

What formula can I use to get the summary total in column B?
Then, how to get the detail per person?
I've got the summary by result - M, D, H - but can't get it per person.

I've tried array formula in summary sheet cell B2:
=IF('workdata'!$B$2:$B$100 = 'Summary'!$A3, COUNTA('workdata'!$C$2:$C$100), "0")
which counts all results into Bob's column; his summary total is 6 and Mike & Tom are blank.

I've tried DCountA but can't make it work.

I've tried
=COUNTIFS('Day 1'!E5:E81, A8,'Day 1'!$F$5:$F81, istext)
but get 0. I've replaced the "istext" with "<>""" but that gave an error.

Your help and shared knowledge is greatly appreciated.

 

[SkipVought]

Hi,

1. Use Named Ranges, based on your headings in the first row of your table. The easiest way is to a) SELECT the entire table, b) Insert > Name > Create -- Create names in TOP row

2. The TOTALS column formula (my summary table upper- left cell is E2...
[tt]
F3: =COUNTIF(Assigned, $E3)
[/tt]
and copy down.

3. The next 3 formulas to copy down...
[tt]
G3: =SUMPRODUCT((Assigned=$E3)*(Status="D"))
H3: =SUMPRODUCT((Assigned=$E3)*(Status<>"M"))
I3: =SUMPRODUCT((Assigned=$E3)*(Status="H"))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

A pivot table would also do the job.

Gavin

 

[mrstaggart]

Thanks. This is very helpful. The CountIF is exactly what I needed.

I can't quite get the SUMPRODUCT to work. if I enter it with ...(Status="D") I get "#NAME" error. If I enter...("Status"="D") I get 0.

BTW, I forgot to mention I'm on Excel 2007, but that shouldn't make a difference.

 

[SkipVought]

I get "#NAME" error

You did not name the Status range!!!

IT WORKS!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}