sum if conditions in two columns are met?

[Guest]

I need to find the total if conditions in two columns are met. I'm doing a
school lottery for enrollment and need to see how many students met the
criteria "yes" in a specific grade level "1".

Example:

Column A Column B
Yes 1
3
Yes 1
1

I'm looking for the total number of times yes and 1 are in the same row.
Here it is 2 times.

Thanks for any help anyone can give me.

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(A1:A100="Yes"),--(B1:B100=1))

Biff

 

[Guest]

Biff,

Thanks. This worked however now I would like to place the totals on a
separate tab in the same worksheet. What additional information do I need to
add to this string. Below is what I tried but I received a #VALUE! error.

=SUMPRODUCT(--('Current Students'!K2:K1000="YES"),--('Current
Students'!N2:N1000=1))

 

[Guest]

Biff,

Tried again and it worked. Thanks so much for your help. You have made my
life much easier. Will this same function work if the second product
(B1:B100=1) is text not numeric? Example: (B1:B100=KG)? or do I need to have
" around the KG as "KG"?

 

[Biff]

If the logical test value is TEXT, yes, you need to enclose it in quotes:

(B1:B100="KG")

If the logical test value is numeric DO NOT use quotes.

A better way to do this would be to use cells to hold the criteria and then
refer to those cells:

A1 = Yes
B1 = 1 or KG (or whatever)

=SUMPRODUCT(--(A5:A100=A1),--(B5:B100=B1)

Biff