| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Won't apply to all worksheets | Setting up and Configuration of Excel | |||
| apply changes to all sheets | Excel Worksheet Functions | |||
| How to apply a weighting | Excel Discussion (Misc queries) | |||
| How can I apply this calculation | Excel Discussion (Misc queries) | |||
| Apply to next X cells to the right... | Excel Worksheet Functions | |||
Linear Mode
