Countifs(Range,Criteria,AND(OR(range2,criteria2,range3,criteria3))

[john316swan]

I am trying to insert an and/or argument into countifs but can't get it to work. I tried SUMPRODUCT(ISNUMBER(MATCH,RANGE,CRITERIA,0))*ISNUMBER(ETC))

Can't figure out how to do it. For example

[TABLE="width: 200"]
<tbody>[TR]
[TD]Student A[/TD]
[TD]GPA[/TD]
[TD]SAT[/TD]
[TD]ACT[/TD]
[/TR]
[TR]
[TD]Student B[/TD]
[TD]3.0[/TD]
[TD]900[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Student C[/TD]
[TD]3.4[/TD]
[TD]1200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student D[/TD]
[TD]3.0[/TD]
[TD]1080[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Student E[/TD]
[TD]2.9[/TD]
[TD]1300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student F[/TD]
[TD]2.0[/TD]
[TD][/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Student G[/TD]
[TD]2.4[/TD]
[TD]950[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student H[/TD]
[TD]4.0[/TD]
[TD][/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Student I[/TD]
[TD]4.0[/TD]
[TD]1450[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]Student J[/TD]
[TD]3.8[/TD]
[TD]1300[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to count all the students who have >= 3.0 GPA, AND (>=1060 SAT OR >=20 ACT).

Thank you for your help.

 

[jtakw]

Hi,

I assume you don't want a student counted Twice if he/she is >=1060 SAT, And, >=20 ACT

So you can do separate COUNTIFS like F2 formula, OR, SUMPRODUCT, like G2 formula:

Book1
	A	B	C	D	E	F	G
1	Student A	GPA	SAT	ACT
2	Student B	3	900	21		6	6
3	Student C	3.4	1200
4	Student D	3	1080	25
5	Student E	2.9	1300
6	Student F	2		16
7	Student G	2.4	950
8	Student H	4		34
9	Student I	4	1450	35
10	Student J	3.8	1300
Sheet492

Cell Formulas
Range	Formula
F2	=COUNTIFS(B2:B10,">=3",C2:C10,">=1060")+COUNTIFS(B2:B10,">=3",D2:D10,">=20")-COUNTIFS(B2:B10,">=3",C2:C10,">=1060",D2:D10,">=20")
G2	=SUMPRODUCT((B2:B10>=3)*((C2:C10>=1060)+(D2:D10>=20)>0))

 

[DanteAmor]

With Sumproduct you can only put conditions and, you can not put or, This also applies with countif and sumif.
To work, both results must be added.

[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]F2[/TD]
[TD]=SUMPRODUCT((B2:B10>=3)*(C2:C10>=1060))+SUMPRODUCT((B2:B10>=3)*(C2:C10<1060)*(D2:D10>=20))[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 320"]
<colgroup><col width="80" span="4" style="width:60pt"> </colgroup><tbody>[TR]
[TD="width: 80"][/TD]
[TD="width: 80, align: center"]A[/TD]
[TD="width: 80, align: center"]B[/TD]
[TD="width: 80, align: center"]C[/TD]
[TD="width: 80, align: center"]D[/TD]
[/TR]
[TR]
[TD="width: 80"]1[/TD]
[TD="width: 80"]STUDENT[/TD]
[TD="width: 80"]GPA[/TD]
[TD="width: 80"]SAT[/TD]
[TD="width: 80"]ACT[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD="align: right"]3.0[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]1200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD="align: right"]3.0[/TD]
[TD="align: right"]1080[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]D[/TD]
[TD="align: right"]2.9[/TD]
[TD="align: right"]1300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]E[/TD]
[TD="align: right"]2.0[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]F[/TD]
[TD="align: right"]2.4[/TD]
[TD="align: right"]950[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]G[/TD]
[TD="align: right"]4.0[/TD]
[TD][/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]H[/TD]
[TD="align: right"]4.0[/TD]
[TD="align: right"]1540[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]I[/TD]
[TD="align: right"]3.8[/TD]
[TD="align: right"]1300[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

With the first part of the formula, you get the quantity 4 (blue numbers)
SUMPRODUCT((B2:B10>=3)*(C2:C10>=1060))

With the second part of the formula, you get the quantity 2 (green numbers)
SUMPRODUCT((B2:B10>=3)*(C2:C10<1060)*(D2:D10>=20))

But in the second part you have to discard the first 4 numbers, that's why you add the condition:
C2:C10<1060



In total there are 6 students who meet the conditions.

Try and tell me.

 

[jtakw]

See my G2 formula in Post # 2, the + (plus sign) acts as an OR operator for SUMPRODUCT...

 

[DanteAmor]

See my G2 formula in Post # 2, the + (plus sign) acts as an OR operator for SUMPRODUCT...

That's right, while I was working to put the example you put the formula, I looked at your answer and I learned something new. Thank you

 

[jtakw]

Thank you DanteAmor, it's always a learning process for me too, I learn something new here almost everyday...