Counting Based on Pivot Table Summary

[leefletcher]

My data looks like this:

Name	Food
Blue	Bag
Blue	Can
Buddy	Bag
Bella	Homemade
Luna	Bag
Luna	Can
Luna	Homemade

My pivot table looks like this:

Name	Bag	Can	Homemade
Bella			1
Blue	1	1
Buddy	1
Luna	1	1	1

I want to know how many animals fit into the different combination of categories. These are the relevant ones:

Category	Count
Bag Only	1
Can Only	0
Homemade Only	1
Bag and Can	1
Bag, Can, and Homemade	1

Please help!

 

[RobP]

Do you mean like this : ?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Food					Count of Food	Column Labels
2	Blue	Bag					Row Labels	Bag	Can	Homemade	Grand Total
3	Blue	Can					Bella			1	1
4	Buddy	Bag					Blue	1	1		2
5	Bella	Homemade					Buddy	1			1
6	Luna	Bag					Luna	1	1	1	3
7	Luna	Can					Grand Total	3	2	2	7
8	Luna	Homemade
9
10
11
Sheet1

 

[RoryA]

You can do it with a series of COUNTIFS formulas looking at the pivot table. Assuming the pivot is in cols A:D

Bag Only: =COUNTIFS(B:B,">0",C:C,0,D:D,0)
Can Only: =COUNTIFS(B:B,0,C:C,">0",D:D,0)
Homemade Only: =COUNTIFS(B:B,0,C:C,0,D:D,">0")
Bag and Can: =COUNTIFS(B:B,">0",C:C,">0",D:D,0)
Bag, Can, and Homemade: =COUNTIFS(B:B,">0",C:C,">0",D:D,">0")

 

[leefletcher]

You can do it with a series of COUNTIFS formulas looking at the pivot table. Assuming the pivot is in cols A:D

Bag Only: =COUNTIFS(B:B,">0",C:C,0,D:D,0)
Can Only: =COUNTIFS(B:B,0,C:C,">0",D:D,0)
Homemade Only: =COUNTIFS(B:B,0,C:C,0,D:D,">0")
Bag and Can: =COUNTIFS(B:B,">0",C:C,">0",D:D,0)
Bag, Can, and Homemade: =COUNTIFS(B:B,">0",C:C,">0",D:D,">0")

Thank you. This works great. I was going about it the wrong way by trying to create the 5 columns (i.e., bag only, can only, etc) each checking the data by rows (e.g., Bag=1, Can=0, Homemade=0).

The formulas above were not counting the empty cells using 0, so I substituted "" and they work great.

Thank you, again.