# Using COUNTIF/INDIRECT to evalute multiple non contiguous columns

#### lnagel

##### Board Regular
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?

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Fluff

##### MrExcel MVP, Moderator
=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

##### Well-known Member
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

##### Board Regular
=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

##### MrExcel MVP, Moderator

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

##### Board Regular
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,

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

##### Well-known Member
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

##### Board Regular
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

Replies
2
Views
174
Replies
3
Views
609
Replies
1
Views
174
Replies
2
Views
265
Replies
1
Views
81

1,127,832
Messages
5,627,150
Members
416,223
Latest member
RichardHell

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back