Count if all both apply
 

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

Count if all both apply
 

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)


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

Count if all both apply
 

=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.
"Grams" wrote in message
...
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

Count if all both apply
 

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?

"Ron Coderre" wrote:

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)


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

Count if all both apply
 

Returned a zero....
Grams

"Alan" wrote:

=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.
"Grams" wrote in message
...
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

Count if all both apply
 

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)


"Grams" wrote in message
...
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?

Count if all both apply
 

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

"Alan" wrote:

=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.
"Grams" wrote in message
...
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

Count if all both apply
 

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"))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Grams" wrote in message
...
Here is where my actually data is (range of both columns)
=SUMPRODUCT((E2:E200="Camas"),(H2:H200="Sonja Freitas"))

"Alan" wrote:

=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.
"Grams" wrote in message
...
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

Count if all both apply
 

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

"RagDyer" wrote:

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"))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Grams" wrote in message
...
Here is where my actually data is (range of both columns)
=SUMPRODUCT((E2:E200="Camas"),(H2:H200="Sonja Freitas"))

"Alan" wrote:

=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.
"Grams" wrote in message
...
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

Count if all both apply
 

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

"Grams" wrote:

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

"RagDyer" wrote:

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"))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Grams" wrote in message
...
Here is where my actually data is (range of both columns)
=SUMPRODUCT((E2:E200="Camas"),(H2:H200="Sonja Freitas"))

"Alan" wrote:

=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.
"Grams" wrote in message
...
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

Count if all both apply
 

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

"Grams" wrote:

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

"RagDyer" wrote:

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"))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Grams" wrote in message
...
Here is where my actually data is (range of both columns)
=SUMPRODUCT((E2:E200="Camas"),(H2:H200="Sonja Freitas"))

"Alan" wrote:

=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.
"Grams" wrote in message
...
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