Count number of rows with multiple criteria

**spinx** · 09-16-2010, 09:49 AM

Hi!

Hope someone can help me with this issue ... First, a description of my dataset:

Column A: Dates (30 days)
Row 1: Names of students (100 names in column B to CW)
Row 2: Each students age
Row 3: A notice of whether the student is internal or external.

Range (B4:CW33) : A number of classes the student has attended (0-?)

Ex.

Name John Brian Danny Eric
Age 21 22 21 23
Type Int Ext Ext Ext

Date
21. 0 0 0 0
22. 2 0 2 1
23. 0 1 0 0
24. 0 0 0 0
25. 0 2 1 1
26. 4 2 0 2
27. 0 0 0 2

Now the calculation:

In a cell I want to count the number of rows (dates in column A) where there are at least one student with a positive number of classes, and meting a criteria of age and internal/external.
Ex. for the criteria (Age = 21) and (Type = Ext) the result is 3 (dates 22, 25 and 26).

How is that possible within the SUMPRODUCT formula?

Thanks!

**JBeaucaire** · 09-16-2010, 10:22 AM

If you want me to test this, click GO ADVANCED and use the paperclip icon to post an actual workbook.

The SUMPRODUCT() construct you want goes like this:

=SUMPRODUCT(--(FirstRange=FirstVal), --(SecondRange=SecondVal))

You could add a 3rd the same way. Resist the urge to use whole columns for your ranges.

**spinx** · 09-17-2010, 02:48 AM

Thanks, I've tried to post a workbook with test data. Can't figure out how to count the rows where at least one column matches the criteria (not calculating the sum)

TestData.xlsx

**DonkeyOte** · 09-17-2010, 05:18 AM

Based on your example:

=SUMPRODUCT(($B$2:$P$2=$C$37)*($B$3:$P$3=$C$36)*SIGN(SUBTOTAL(9;OFFSET($B$4;ROW($B$4:$B$33)-ROW($B$4);COLUMN($B$4:$P$4)-COLUMN(B4)))))

where C36 holds "Ext" & C37 holds 21.

translate SUMPRODUCT, SIGN, OFFSET, ROW & COLUMN as and if appropriate (SUMPRODUKT, FORTEGN, FORSKYDNING, RÆKKE & KOLONNE)

**spinx** · 09-17-2010, 06:40 AM

Thanks for the reply - Almost there now

The problem is if there is more than one column matching the conditions in a row, than the number of values are counted - not only the number of rows.
If I put a number greater than 0 in cell O33, the result for Age=21/Ext with the formula gives 5, but I was hoping for 4 = the count of rows (7, 24, 29 and 33)

Can this be solved?