count double occurences

H

Henderson

hello
i am trying to create a worksheet that will count how many times one
criteria is met in one column and another is met in a second column

the point is to count the times students have late assignments in the
various different subjects

example:

Adam History
Adam Math
Bob History
Adam Math

how many times did Adam have a late assignment in Math?

there a ppeared to be formula in Help that i could use but I couldn't get it
to work - not even by recreating their example
=COUNT(IF((A2:A11="Adam")*(B2:B11="Math"),D2:D11))
(i don't know why they needed a D range here)
 
P

Peo Sjoblom

=SUMPRODUCT(--(A2:A11="Adam"),--(B2:B11="Math"))

but it's better to use

=SUMPRODUCT(--(A2:A11=C2),--(B2:B11=D2))

where you would put the criteria in the cells thus you don't have to edit
the formula to change student/subject

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
A

Ashish Mathur

Hi,

Although sumproduct is the best solution, you may also want to try this
array formula (Ctrl+Shift+Enter)

=sum(if((A2:A11="Adam")*(B2:B11="Math"),1,0))

Regards,

Ashish Mathur
 
Top