Countif from a Pivot Table for one unique value being greater than another

[excelhelp1a]

I have a pivot table in which I need to count the number of occurrences that certain criteria are met. The data is below, the criteria that I need met is this: Green is greater than 0, the state is CA, and at least one other item (Red/Blue) has a value greater than 0. I think I need to do some sort of countif and sumproduct but am not sure how to get the desired result. Any information is super appreciated!

State	Red	Blue	Green	Grand Total
CA	1​	2​	3​	6​
CA	0​	0​	3​	3​
CA	0​	0​	1​	1​
NM	1​	3​	2​	6​
NM	1​	2​	1​	4​
CA	1​	2​	3​	6​
NM	1​	2​	1​	4​
NY	3​	4​	2​	9​
NY	2​	1​	1​	4​
CA	1​	1​	0​	2​

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F	G
1	State	Red	Blue	Green	Grand Total		Count
2	CA	1	2	3	6		2
3	CA	0	0	3	3
4	CA	0	0	1	1
5	NM	1	3	2	6
6	NM	1	2	1	4
7	CA	1	2	3	6
8	NM	1	2	1	4
9	NY	3	4	2	9
10	NY	2	1	1	4
11	CA	1	1	0	2
Sheet13

Cell Formulas
Range	Formula
G2	G2	=SUMPRODUCT(--(A2:A11="CA"),--(D2:D11>0),--(B2:B11+C2:C11>0))

 

[excelhelp1a]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F	G
1	State	Red	Blue	Green	Grand Total		Count
2	CA	1	2	3	6		2
3	CA	0	0	3	3
4	CA	0	0	1	1
5	NM	1	3	2	6
6	NM	1	2	1	4
7	CA	1	2	3	6
8	NM	1	2	1	4
9	NY	3	4	2	9
10	NY	2	1	1	4
11	CA	1	1	0	2
Sheet13

Cell Formulas
Range	Formula
G2	G2	=SUMPRODUCT(--(A2:A11="CA"),--(D2:D11>0),--(B2:B11+C2:C11>0))

Thanks Eric, this is super helpful! To expand upon this let's say there are 5 columns of colors (Red, Blue, Green, Yellow, White) what would the formula be if I were to want to have Red be >0 and then at least 2 other colors be >0

 

[Eric W]

Consider:

Book1
	A	B	C	D	E	F	G	H	I
1	State	Red	Blue	Green	Yellow	White	Grand Total		Count
2	CA	1	2	3	1	1	6		3
3	CA	0	0	3	0	1	3		3
4	CA	0	0	1	1	0	1		3
5	NM	1	3	2	0	0	6
6	NM	1	2	1	1	1	4
7	CA	1	2	3	0	1	6
8	NM	1	2	1	1	0	4
9	NY	3	4	2	0	0	9
10	NY	2	1	1	1	1	4
11	CA	1	1	0	0	1	2
Sheet13

Cell Formulas
Range	Formula
I2	I2	=SUMPRODUCT(--(A2:A11="CA"),--(B2:B11>0),--((C2:C11>0)+(D2:D11>0)+(E2:E11>0)+(F2:F11>0)>=2))
I3	I3	=SUMPRODUCT(--(A2:A11="CA"),--(B2:B11>0),--(MMULT(--(C2:F11>0),{1;1;1;1})>=2))
I4	I4	=SUMPRODUCT(--(A2:A11="CA"),--(MMULT(--(B2:F11>0),{10;1;1;1;1})>=12))

The I2 formula is just an extension of the existing formula, and it's probably the easiest to understand. The I3 formula shows a way to shorten that formula by using MMULT. It requires that the Blue-White columns are all adjacent to each other though.

The I4 formula is a way I found to include the B column in the MMULT as well, shortening it even further. But I admit it's a bit cutesy, and harder to understand/maintain for someone looking at it cold.

Let me know if you have any questions. Good luck!

 

[excelhelp1a]

Eric this is exactly what I was looking for! Hoping to expand it a bit further though. Let's say each state has a customer ID associated with it. Another table in the workbook denotes whether that specific ID should be counted or not. Is there a way for me to reference that second table check whether or not the above conditions are met AND the customer ID should be counting?

State	ID	Red	Blue	Green	Yellow	White	Grand Total
CA	1	1	2	3	1	1	8
CA	2	0	0	3	0	1	4
CA	3	0	0	1	1	0	2
NM	4	1	3	2	0	0	6
NM	5	1	2	1	1	1	6
CA	6	1	2	3	0	1	7
NM	7	1	2	1	1	0	5
NY	8	3	4	2	0	0	9
NY	9	2	1	1	1	1	6
CA	10	1	1	0	0	1	3

ID	Count
1​	Yes
2​	No
3​	Yes
4​	Yes
5​	No
6​	No
7​	Yes
8​	No
9​	Yes
10​	No

Thank you so much!!

 

[Eric W]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	State	ID	Red	Blue	Green	Yellow	White	Grand Total		Count			ID	Count
2	CA	1	1	2	3	1	1	6		1			1	Yes
3	CA	2	0	0	3	0	1	3		1			2	No
4	CA	3	0	0	1	1	0	1		1			3	Yes
5	NM	4	1	3	2	0	0	6					4	Yes
6	NM	5	1	2	1	1	1	4					5	No
7	CA	6	1	2	3	0	1	6					6	No
8	NM	7	1	2	1	1	0	4					7	Yes
9	NY	8	3	4	2	0	0	9					8	No
10	NY	9	2	1	1	1	1	4					9	Yes
11	CA	10	1	1	0	0	1	2					10	No
Sheet4

Cell Formulas
Range	Formula
J2	J2	=SUMPRODUCT(--(A2:A11="CA"),--(COUNTIFS($M$2:$M$11,$B$2:$B$11,$N$2:$N$11,"Yes")>0),--(C2:C11>0),--((D2:D11>0)+(E2:E11>0)+(F2:F11>0)+(G2:G11>0)>=2))
J3	J3	=SUMPRODUCT(--(A2:A11="CA"),--(COUNTIFS($M$2:$M$11,$B$2:$B$11,$N$2:$N$11,"Yes")>0),--(C2:C11>0),--(MMULT(--(D2:G11>0),{1;1;1;1})>=2))
J4	J4	=SUMPRODUCT(--(A2:A11="CA"),--(COUNTIFS($M$2:$M$11,$B$2:$B$11,$N$2:$N$11,"Yes")>0),--(MMULT(--(C2:G11>0),{10;1;1;1;1})>=12))

It's the same 3 formulas as before, just with an added COUNTIFS condition.