Tricky Countif

[gregork]

Hi,
I need help with a formula for a countif:
Based on the range A23:A39....
If one or more cells in the range = "apples", cell value = "apples"
If only all cells in the range (ignoring blanks) = "oranges" , cell value =
"oranges"
Otherwise , cell value = ""

I think I need a countif formula for this but I am not sure how to write it.

Regards
gregork

 

[Bob Phillips]

Hi Gregor,

Try

=IF(COUNTIF(A23:A39,"apples")=COUNTA(A23:A39),I1,"")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Transcription error, should be

=IF(COUNTIF(A23:A39,"apples")=COUNTA(A23:A39),A23,"")


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Aladin Akyurek]

Interpreting literally...

=IF(ISNUMBER(MATCH("apples",A2:A7,0)),"apples",IF(COUNTIF(A2:A7,"<>oranges")
=COUNTIF(A2:A7,"="),"oranges",""))

if you mean only empty cells by "blanks", otherwise:

=IF(ISNUMBER(MATCH("apples",A2:A7,0)),"apples",IF(COUNTIF(A2:A7,"<>oranges")
=SUMPRODUCT(--(A2:A7="")),"oranges",""))