COUNTIFS with multiple criteria

[KuraiChikara]

I have an issue with COUNTIFS with multiple criteria. Example:

Column A has 4 John Doe’s
Column B has 4 fruits: Apple, Apple, Strawberry, Watermelon
Column C is my Formula: If I just include 1 countifs it works

=COUNTIFS($A$2:$A$5,D2,$B$2:$B$5,E2) returns 2 Apples found

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Fruit[/TD]
[TD]Formula[/TD]
[TD]Name[/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Apple[/TD]
[TD](enter here)[/TD]
[TD]John Doe[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD]Strawberry[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Strawberry[/TD]
[TD][/TD]
[TD][/TD]
[TD]Watermelon[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Watermellon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


However, if I wanted to find ALL fruit next to John Doe by including all options in the COUNTIFS (which adds to a total of 6 criteria; John Doe is looked at 3 times and there are 3 fruits specifically looked at), it returns 0

=COUNTIFS($A2:A$5,D2,$B$2:$B$5,E2,$A$2:$A$5,D2,$B$2:$B$5,E3,$A$2:$A$5,D2,$B$2:$B$5,E4)

For this example, I would want 4 to return because there are 4 fruits next to John Doe but it doesn’t.

Same result happens if I replace the Column E cell data in the formula with the actual text of the fruit.

=COUNTIFS($A2:A$5,D2,$B$2:$B$5,"Apple",$A$2:$A$5,D2,$B$2:$B$5,"Strawberry",$A$2:$A$5,D2,$B$2:$B$5,"Watermelon")

This also returns 0, when there are a total of 4 fruits in Column B.

 

[Nine Zero]

So in this case you are kinda saying it not only has apple next to it but it also has strawberry and watermelon.... in the same cell which none of them do

What you wanna do is SUM the instance of each fruit

=SUM(COUNTIFS($A$2:$A$5,D2,$B$2:$B$5,E2),COUNTIFS($A$2:$A$5,D2,$B$2:$B$5,E3),COUNTIFS($A$2:$A$5,D2,$B$2:$B$5,E4))

This will be like summing 2 + 1 + 1 = 4

Try that

 

[KuraiChikara]

That's exactly what I was looking for. Thank you.
I'm also proud of myself because I knew a SUM would fix the issue too, just didn't know how to incorporate it. You helped with that.

 

[Nine Zero]

That's exactly what I was looking for. Thank you.
I'm also proud of myself because I knew a SUM would fix the issue too, just didn't know how to incorporate it. You helped with that.

jaja no problem any time glad i could help

 

[jtakw]

Hi,

A couple of different ways to write the formula.

C2 formula Normally entered,
C3 formula to be confirmed by CSE, instructions below:

Book1
	A	B	C	D	E
1	Name	Fruit	Formula	Name	Fruit
2	John Doe	Apple	4	John Doe	Apple
3	John Doe	Apple	4		Strawberry
4	John Doe	Strawberry			Watermellon
5	John Doe	Watermellon
Sheet328

Cell Formulas
Range	Formula
C2	=SUM(COUNTIFS(A2:A5,D2,B2:B5,{"Apple","Strawberry","Watermellon"}))
C3	{=SUM(COUNTIFS(A2:A5,D2,B2:B5,E2:E4))}
Press CTRL+SHIFT+ENTER to enter array formulas.