count double occurences
 

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)

count double occurences
 

=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



"Henderson" wrote in message
...
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)

count double occurences
 

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

"Henderson" wrote:

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)

count double occurences
 

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

"Peo Sjoblom" wrote:

=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



"Henderson" wrote in message
...
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)

count double occurences
 

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

"Ashish Mathur" wrote:

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

"Henderson" wrote:

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)