Using COUNTIF/INDIRECT to evalute multiple non contiguous columns

lnagel

Board Regular
Joined
May 28, 2018
Messages
117
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?
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,670
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2018
Messages
117
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,670
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 28, 2018
Messages
117
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2018
Messages
117
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top