Home |
Search |
Today's Posts |
#1
|
|||
|
|||
countif two conditions
I have seen multiple posts on this but for some reason I can't seem to get it
to work. I have two columns and need to countif two conditions are satisfied. Gender (COL A) School (COL H) Male Carter Middle School Female Carter Middle School Female Centennial Female Carter Middle School I want to know the number of Females at Carter Middle School. My data is on one worksheet and I want the formula to be on another. I have this but get an error (#NUM) =SUMPRODUCT(--('8th Grade Field Test 1 Michigan'!H:H="Carter Middle School"),--('8th Grade Field Test 1 Michigan'!A:A="Female")) Thanks, Penny |
#2
|
|||
|
|||
SUMPRODUCT doesn't allow entire col references (H:H, A:A, etc) to be used.
Try changing the references to say, H1:H200 and A1:A200 instead, in the formula .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Penny" wrote in message ... I have seen multiple posts on this but for some reason I can't seem to get it to work. I have two columns and need to countif two conditions are satisfied. Gender (COL A) School (COL H) Male Carter Middle School Female Carter Middle School Female Centennial Female Carter Middle School I want to know the number of Females at Carter Middle School. My data is on one worksheet and I want the formula to be on another. I have this but get an error (#NUM) =SUMPRODUCT(--('8th Grade Field Test 1 Michigan'!H:H="Carter Middle School"),--('8th Grade Field Test 1 Michigan'!A:A="Female")) Thanks, Penny |
#3
|
|||
|
|||
Thanks! Now I am wondering if you could help me with something else?? If I
use the same data as before but add a column L for GPA. Is there a way for me to calculate the mean GPA for those students at Carter Middle School? Thanks. Gender (COL A) School (COL H) GPA (COL L) Male Carter Middle School 3.5 Female Carter Middle School 2.5 Female Centennial 2.5 Female Carter Middle School 3.8 "Max" wrote: SUMPRODUCT doesn't allow entire col references (H:H, A:A, etc) to be used. Try changing the references to say, H1:H200 and A1:A200 instead, in the formula .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Penny" wrote in message ... I have seen multiple posts on this but for some reason I can't seem to get it to work. I have two columns and need to countif two conditions are satisfied. Gender (COL A) School (COL H) Male Carter Middle School Female Carter Middle School Female Centennial Female Carter Middle School I want to know the number of Females at Carter Middle School. My data is on one worksheet and I want the formula to be on another. I have this but get an error (#NUM) =SUMPRODUCT(--('8th Grade Field Test 1 Michigan'!H:H="Carter Middle School"),--('8th Grade Field Test 1 Michigan'!A:A="Female")) Thanks, Penny |
#4
|
|||
|
|||
Hi Penny,
SUMPRODUCT cannot process a whole column, you have to define its range =SUMPRODUCT(--('8th Grade Field Test 1 Michigan'!H1:H1000="Carter Middle School"),--('8th Grade Field Test 1 Michigan'!A1:A1000="Female")) -- HTH RP (remove nothere from the email address if mailing direct) "Penny" wrote in message ... I have seen multiple posts on this but for some reason I can't seem to get it to work. I have two columns and need to countif two conditions are satisfied. Gender (COL A) School (COL H) Male Carter Middle School Female Carter Middle School Female Centennial Female Carter Middle School I want to know the number of Females at Carter Middle School. My data is on one worksheet and I want the formula to be on another. I have this but get an error (#NUM) =SUMPRODUCT(--('8th Grade Field Test 1 Michigan'!H:H="Carter Middle School"),--('8th Grade Field Test 1 Michigan'!A:A="Female")) Thanks, Penny |
#5
|
|||
|
|||
One way ...
Put in M1: Carter Middle School Put in M2: Female Put in M3, and array-enter (press CTRL+SHIFT+ENTER): =AVERAGE(IF($H$2:$H$5=M$1,$L$2:$L$5)) M3 will return the mean GPA for "Carter Middle School" Put in M4, and array-enter (press CTRL+SHIFT+ENTER): =AVERAGE(IF(($H$2:$H$5=M$1)*($A$2:$A$5=M$2),$L$2:$ L$5)) M4 will return the mean GPA for "Females" in "Carter Middle School" Adjust the ranges to suit .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Penny" wrote in message ... Thanks! Now I am wondering if you could help me with something else?? If I use the same data as before but add a column L for GPA. Is there a way for me to calculate the mean GPA for those students at Carter Middle School? Thanks. Gender (COL A) School (COL H) GPA (COL L) Male Carter Middle School 3.5 Female Carter Middle School 2.5 Female Centennial 2.5 Female Carter Middle School 3.8 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF with two conditions | Excel Worksheet Functions | |||
COUNTIF - 2 conditions | Excel Worksheet Functions | |||
How can I use COUNTIF to sum for two conditions? | Excel Worksheet Functions | |||
COUNTIF using multiple conditions? | New Users to Excel | |||
setting two conditions for countif | Excel Worksheet Functions |