Using COUNTIF/INDIRECT to evalute multiple non contiguous columns

[lnagel]

I have multiple columns ( that are not side by side) that I need to evaluate for the test "MARKET" in any of the cells

I have created the following formula for a specific cell : (have also tried the INDIRECT function in a Named Range - also no luck)

=IF(COUNTIF(INDIRECT({"I2:I36","T2:T36"}),"MARKET"),"*MARKET","")

The formula will only evaluate the 1st Column (I2:36) correctly - Subsequent columns (T2:36) seem to be ignored

The exact same behavior happened when I tried to set up and use a NAMED RANGE using INDIRECT

Any ideas?

 

[Fluff]

How about
=IF(SUM(COUNTIF(INDIRECT({"I2:I36","T2:T36"}),"MARKET")),"*MARKET","")
or a non volatile method
=IF(SUMPRODUCT((I2:I36="Market")+(T2:T36="Market")),"*Market","")

 

[jasonb75]

Maybe one of

=COUNTIF(I2:I36,"MARKET")+COUNTIF(T2:T36,"MARKET")

=COUNTIFS(I2:I36,"MARKET",T2:T36,"MARKET")

From your post it is not clear what the exact requirement is, only what hasn't worked.

 

[lnagel]

How about
=IF(SUM(COUNTIF(INDIRECT({"I2:I36","T2:T36"}),"MARKET")),"*MARKET","")
or a non volatile method
=IF(SUMPRODUCT((I2:I36="Market")+(T2:T36="Market")),"*Market","")

=IF(SUM(COUNTIF(INDIRECT({"I2:I36","T2:T36"}),"MARKET")),"*MARKET","") worked perfect - Thanks so much

You mentioned that this was a "volatile" method - I'm not sure I know what that means in this application?

 

[Fluff]

A volatile function will recalculate whenever any cell on that sheet changes, whereas a non volatile function will only recalc if a cell it looks at changes.

 

[lnagel]

Maybe one of

=COUNTIF(I2:I36,"MARKET")+COUNTIF(T2:T36,"MARKET")

=COUNTIFS(I2:I36,"MARKET",T2:T36,"MARKET")

From your post it is not clear what the exact requirement is, only what hasn't worked.

Hey Jason,

Thanks for the reply

The original requirement was to determine if the text "MARKET" was found in ANY of the cells in the specified columns

If the answer to that is YES then I want to put the text "MARKET" in the formula cell

 

[jasonb75]

In that case, try =IF(COUNTIF(I2:I36,"MARKET")+COUNTIF(T2:T36,"MARKET") ,"MARKET","")

If it is not possible for "MARKET" to be found in J2:S36 then you could simply use =IF(COUNTIF(I2:T36,"MARKET"),"MARKET","")
Note that the formula can not be located inside the COUNTIF range.

 

[lnagel]

In that case, try =IF(COUNTIF(I2:I36,"MARKET")+COUNTIF(T2:T36,"MARKET") ,"MARKET","")

If it is not possible for "MARKET" to be found in J2:S36 then you could simply use =IF(COUNTIF(I2:T36,"MARKET"),"MARKET","")
Note that the formula can not be located inside the COUNTIF range.

Yea that was my original formula - I have run into a situation where the columns in between I2 and T36 could (and sometimes do) contain the text "Market". This was identified by a user and was an unforeseen issue in the original formula. In those cases I do not want the formula to trigger - so I needed a Plan B