count if

[gregork]

I need a formula to return text based on that text being in all the cells in
a range. I want the formula to ignore blank cells in the range. I have the
following formula but it is not working ...I think because of the blank
cells in the range.

IF(COUNTIF(I124:I139,"RED")=COUNTA(I124:I139),"RED","")

gregorK

 

[JulieD]

Hi Gregork

this works for me
=IF(COUNTIF(B3:B11,"red")=COUNTA(B3:B11),"red","")
but only when the blank cells are truely blank (ie no space in there, or no
formula evaluating to blank etc) - do your "blank" cells have anything in
them?

Cheers
JulieD

 

[AlfD]

Hi!

Have you put data in col I yet?

The formula works for me except when the target range is empty, when i
read RED.

Al

 

[gregork]

Hi Julie,
Thanks for the reply. Yes my cells have a lookup formula with an
argument....if false it returns ""

gregorK

 

[RagDyeR]

Try this:

=IF(COUNTIF(I134:I139,"RED")=(COUNTA(I134:I139)-COUNTBLANK(I134:I139)),"RED"
,"")

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi Julie,
Thanks for the reply. Yes my cells have a lookup formula with an
argument....if false it returns ""

gregorK

 

[gregork]

Yes it works, many thanks RD.

regards
gregorK

Try this:

=IF(COUNTIF(I134:I139,"RED")=(COUNTA(I134:I139)-COUNTBLANK(I134:I139)),"RED"
,"")

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi Julie,
Thanks for the reply. Yes my cells have a lookup formula with an
argument....if false it returns ""

gregorK

 

[RagDyeR]

Thanks for the feed-back!
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Yes it works, many thanks RD.

regards
gregorK