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)


[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Cake[/TD]
[TD]Fruit Salad[/TD]
[TD]Cake[/TD]
[TD]Juice[/TD]
[TD]Fruit Salad[/TD]
[TD]Juice[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]Blank[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Blank[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Yes[/TD]
[TD]Blank[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Blank[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Blank[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Bank[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Blank[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD]Yes[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Blank[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Juice[/TD]
[TD]Juice[/TD]
[TD]Juice[/TD]
[TD]Fruit Salad[/TD]
[TD]Fruit Salad[/TD]
[TD]Fruit Salad[/TD]
[TD]Cake[/TD]
[TD]Cake[/TD]
[TD]Cake[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Blank[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Blank[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Blank[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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