Summarize data based on multiple criteria

**sigil** · 01-06-2010, 03:44 PM

I have a worksheet with several columns of data that I have to summarize elsewhere on the sheet. Here are the columns:

ID
Status
Joined
Name

Name can take on 4 possible values. For each value in Name, I want to count the number of cells in a column that fit certain criteria. Here are the counts I want to make:

Status OK: Status is a date.
Joined OK: Status is a date and Joined is a "1".

There are going to be about 500 records to count. I've already implemented these counts in a Windows scripting language that interfaces with Excel, but I know there must be a more direct and faster way to do it entirely in Excel, whether with VBA or writing functions into cells.

I have attached a copy of the sheet with sample data on it.

**NBVC** · 01-06-2010, 03:54 PM

In G4:

=SUMPRODUCT(--($D$2:$D$9=$F4),--ISNUMBER($B$2:$B$9))

in H4:

=SUMPRODUCT(--($D$2:$D$9=$F4),--ISNUMBER($B$2:$B$9),--($C$2:$C$9=1))

copied down

**sigil** · 01-06-2010, 07:49 PM

Thanks, that works great for the finite case where I already know how many records I have, but I also want it to work if I add more records to my list.

I tried replacing

=SUMPRODUCT(--($D$2:$D$9=$F4),--ISNUMBER($B$2:$B$9))

with

=SUMPRODUCT(--(D:D=$F4),--ISNUMBER(B:B))

so that I could include all entries in columns B & D no matter how many I added, but I got a #NUM! error as a result.