Sum of a text from table when met two criterias

[Dilshan Anandan]

Hi All,

I am being trying to get the some of "Blanks, Yes and No" from a table. But it has to match the "A" Column and the "1" Row. (More like Count ifs)

Name	Cake	Fruit Salad	Cake	Juice	Fruit Salad	Juice
Apple	Blank	No	No	Yes	Blank	No
Apple	No	No	No	Yes	No	No
Apple	Yes	Blank	No	Yes	Blank	No
Orange	Yes	No	Blank	No	Yes	Yes
Orange	Bank	No	Yes	No	Yes	Yes
Grapes	No	Yes	Blank	Yes	No	No
Grapes	Yes	Blank	Blank	No	No	yes
Grapes	Yes	No	Blank	No	Yes	Yes

<tbody>
</tbody>

The order of the columns and the rows can be changed.
Apple, Orange and Grapes can be in multiple rows, Cake, Fruit Salad and Juice can be in multiple columns as well.

I want to get how many "Yes" are there when Apple and Cakes are met. And How many "Blanks" when Fruit Salad and Orange are met.
I have put down all possible outcome in a table.

	Juice	Juice	Juice	Fruit Salad	Fruit Salad	Fruit Salad	Cake	Cake	Cake
	Blank	Yes	No	Blank	Yes	No	Blank	Yes	No
Apple
Orange
Grapes

<tbody>
</tbody>

If I can fill down the Table two with a formula, Would be very helpful,
I tried using Index,Match,Countifs together. No Luck
And the Data used is and example.


Thanks in Advance for the help.

Cheers
Dilshan

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Cake	Fruit Salad	Cake	Juice	Fruit Salad	Juice
2	Apple		No	No	Yes		No
3	Apple	No	No	No	Yes	No	No
4	Apple	Yes		No	Yes		No
5	Orange	Yes	No		No	Yes	Yes
6	Orange	Bank	No	Yes	No	Yes	Yes
7	Grapes	No	Yes		Yes	No	No
8	Grapes	Yes			No	No	yes
9	Grapes	Yes	No		No	Yes	Yes
10
11
12
13		Juice	Juice	Juice	Fruit Salad	Fruit Salad	Fruit Salad	Cake	Cake	Cake
14			Yes	No		Yes	No		Yes	No
15	Apple	0	3	3	3	0	3	1	1	4
16	Orange	0	2	2	0	2	2	1	2	0
17	Grapes	0	3	3	1	2	3	3	2	1
2

Cell Formulas
Range	Formula
B15	=SUM(SUMPRODUCT((B$13=$B$1:$G$1)*($A15=$A$2:$A$9)*($B$2:$G$9=B$14)))

 

[Dilshan Anandan]

Hi AlanY ,

Thanks for the help. That was really helpful.

 

[AlanY]

you're welcome