CODEWITHSUNDEEP
excel
bsquared82
bsquared82

Reputation: 13

Excel countifs with multiple conditions

I'm trying to count all the people in an excel file that scored less than 75 on exam 1 but scored over 900 on either exam 2 OR 3. I tried using countifs but if both exam 2 and exam 3 are over 900, it counts them twice. Any suggestions? Please help!

I've tried sumproduct as well but I can't figure out how to include an OR condition that's based on 2 separate columns

=COUNTIFS($C:$C, "<75",$E:$E,">900",$F:$F,">900")

I would expect to keep a count of all the students that meet this criteria.

Upvotes: 0

Views: 80

Answers (5)

Error 1004
Error 1004

Reputation: 8230

You could use SUMPRODUCT

Formula:

=SUMPRODUCT((C:C<75)*(E:E>900)*(F:F>900))

Results:

enter image description here

Upvotes: 0

Chronocidal
Chronocidal

Reputation: 8081

Just subtract when both are over 900 from the sum of when each is over 900:

=COUNTIFS($C:$C, "<75",$E:$E,">900",$F:$F) + COUNTIFS($C:$C, "<75",$F:$F,">900") - COUNTIFS($C:$C, "<75",$E:$E,">900",$F:$F,">900")

Why does this work?

Exam1 | Exam2 | Exam3 ::: CountIfs1 + CountIfs2 - CountIfs3 === Result
 100  |   75  |   75   :       0    +      0    -      0     =     0
  70  |   75  |   75   :       0    +      0    -      0     =     0
  70  |  901  |   75   :       1    +      0    -      0     =     1
  70  |   75  |  901   :       0    +      1    -      0     =     1
  70  |  901  |  901   :       1    +      1    -      1     =     1

Upvotes: 0

K.H
K.H

Reputation: 11

=countifs("*Exam 1 cell*","<75","*Exam 2 cell*",">900","*Exam 3 cell*","<900")

+countifs("*Exam 1 cell*","<75","*Exam 3 cell*",">900","*Exam 2 cell*","<900")

+countifs("*Exam 1 cell*","<75","*Exam 2 cell*",">900","*Exam 3 cell*",">900")

I was just very specific in my conditions for what I wanted the formula to count:

1) exam 1 < 75 , exam 2 > 900 , exam 3 < 900

2) exam 1 < 75 , exam 3 > 900 , exam 2 < 900

3) exam 1 < 75 , exam 2 > 900 , exam 3 > 900

Upvotes: 1

Jenn
Jenn

Reputation: 647

This formula works for what you need:

=SUM(COUNTIF($D:$D,"<75"),IF(SUM(COUNTIF($E:$E,">900"),COUNTIF($F:$F,">900"))>1,1,0))

enter image description here

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166790

As an array formula (entered with Ctrl+Shift+Enter)

=SUM( (C3:C5<75)*
      (1*(E3:E5>900)+1*(F3:F5>900)>0) )

Upvotes: 0

Related Questions