Count if all both apply

[Guest]

I have a column (E) that contains names of districts, and column a column (H)
that contains names of teachers and I need to summarize how many times a
certain teacher is connected to a district. Both columns have multiple
entries in both columns. What equation would I use to find out how many
times teacher S is listed with an entry of district C (in other words, if the
district was Camas, how many times would teacher S show up connected to that
district). Clear as mud, I'm sure
District Teacher
Camas S
Camas D
Camas P
Camas S
Riddle S
Riddle P
Camas S
My result should be 3

thanks in advance

 

[Ron Coderre]

With your posted data in A1:B8

And:
C1: (a District...eg Camas)
D1: (a Teachre..eg S)

This formula returns the count of Camas/S combinations:
E1: =SUMPRODUCT((A2:A10=C1)*(B2:B10=D1))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Alan]

=SUMPRODUCT(--(E2:E8="Camas"),--(H2:H8="S"))
Adjust the ranges to suit. 'Camas' and 'S' can be put into cells whish make
it easier, eg if you have 'Camas in A1 and 'S' in A2 then:-
=SUMPRODUCT(--(E2:E8=A1),--(H2:H8=A2))
To check 'Riddle', all you have to do is enter 'Riddle' into A1 and 'D' or
whatever to see other results,
Regards,
Alan.

 

[Guest]

Not sure I understand how to change your formula to match what is actually in
my table.
The column with District names is E (rows 1 through 200) and the column with
the teacher names is H (rows 1 through 200). So how would I change the
formula to get the correct count of ALL entries that have BOTH a specific
teacher (S) and a specific district (Camas).
Brain dead here....can I change the C1 and D1 to something like "Camas" and
"S" and still have it work?

 

[Guest]

Returned a zero....
Grams

=SUMPRODUCT(--(E2:E8="Camas"),--(H2:H8="S"))
Adjust the ranges to suit. 'Camas' and 'S' can be put into cells whish make
it easier, eg if you have 'Camas in A1 and 'S' in A2 then:-
=SUMPRODUCT(--(E2:E8=A1),--(H2:H8=A2))
To check 'Riddle', all you have to do is enter 'Riddle' into A1 and 'D' or
whatever to see other results,
Regards,
Alan.

 

[Ron Coderre]

Try this:
Cell C1 contains the name of the district you want to match
Cell D1 contains the name of the teacher you want to match

This formula returns the count of that combination
=SUMPRODUCT((E1:E200=C1)*(H1:H200=D1))

or...a simpler approach:

=SUMPRODUCT((E1:E200="Camas")*(H1:H200="S"))

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Here is where my actually data is (range of both columns)
=SUMPRODUCT((E2:E200="Camas"),(H2:H200="Sonja Freitas"))

 

[RagDyer]

Your formula is missing the operators.

You need either an asterisk between the arguments, as Ron suggested:

=SUMPRODUCT((E2:E200="Camas")*(H2:H200="Sonja Freitas"))

OR, the unary, as Alan suggested:

=SUMPRODUCT(--(E2:E200="Camas"),--(H2:H200="Sonja Freitas"))

 

[Guest]

=SUMPRODUCT((E2:E200="Camas")*(H2:H200="S"))
Got this one to work...thanks for all your help.

 

[Guest]

OK. So now I tried to sumproduct two other columns using the same
formula...and changing the column letters to match the new count I am looking
for - but it always returns a zero even though there is data there.
I want to find how many kids in each district have been evaluated for OI,
OHI, TBI, and Autism.
The District names (like "Camas") are in column E, rows 2 through 200
The Categories (OI, OHI, etc) are in column J, rows 2 through 200
I tried =SUMPRODUCT((E2:E200="Camas")*(J2:J200="TBI"))
Didn't work.
Any suggestions?
Thanks

 

[Alan]

Your formula, =SUMPRODUCT((E2:E200="Camas")*(J2:J200="TBI")) is good. If it
doesn't work it for you it may be something to do with the data itself, like
leading or trailing spaces,
Regards,
Alan.