count double occurences

[Guest]

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"),D211))
(i don't know why they needed a D range here)

 

[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

 

[Guest]

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

 

[Guest]

this seems to being do the trick. thank you. i am amazed people would do this

 

[Guest]

thanks for the thought. i am excited to get this working.