COUNTIFS return to 0

[xdenama]

Hi all,

I have formula that's not working...

=COUNTIFS(A1:F5,"FARM A",A1:F5,"Orange")
return "0"
Required answer is 2

Please help...

	A	B	C	D	E	F
1​		STOR A	STOR B	STOR C	STOR D	STOR E
2​	FARM A	Orange	Avocados	Bananas	Cherry	Orange
3​	FARM B	Grape	Cherry	Guava	Bananas	Bananas
4​	FARM C	Orange	Bananas	Orange	Bananas	Avocados
5​	FARM D	Grape	Guava	Grape	Guava	Cherry

 

[mrshl9898]

You can't COUNTIF across multiple columns, you'd need =COUNTIFS(A1:A5,"FARM A",B1:B5,"Orange")+COUNTIFS(A1:A5,"FARM A",C1:C5,"Orange")+etc...

 

[JoeMo]

Easier way:
=SUMPRODUCT(($A$2:$A$5="Farm A")*($B$2:$F$5="Orange"))

 

[xdenama]

Easier way:
=SUMPRODUCT(($A$2:$A$5="Farm A")*($B$2:$F$5="Orange"))

Yess.. excellent, thank you...

But if I change the fruit name with code, example "Orange" with "1176", text convert to number, then answer is BLANK...

 

[JoeMo]

Yess.. excellent, thank you...

But if I change the fruit name with code, example "Orange" with "1176", text convert to number, then answer is BLANK...

You are welcome. If you're counting instances of a number, enter it as a number, not text, like:

=SUMPRODUCT(($A$2:$A$5="Farm A")*($B$2:$F$5=1176))