Countifs and indirect references question

collin8579

New Member
Joined
Oct 31, 2017
Messages
19
First: Thank you for reading.

My issue, I have a formula on a waterfall type report that uses a lot of countif functions to determine if a site is forecasted/actualized, and counts it if it is. I bring in data to the workbook through a series of queries, and unfortunately, the client changes their data occasionally and the columns get mixed up on my reports. This caused the need for indirect functions, and now it seems to work just fine.

=COUNTIFS(INDIRECT("Combined_Data"&"["&"On Air Date"&"]"),F25,INDIRECT("Combined_Data"&"["&"Software"&"]"),"N",INDIRECT("Combined_Data"&"["&$R35&"]"),">="&H$2,INDIRECT("Combined_Data"&"["&$R35&"]"),"<="&H$3,INDIRECT("Combined_Data"&"["&$S35&"]"),"",INDIRECT("Combined_Data"&"["&"National Program 2"&"]"),D25,INDIRECT("Combined_Data"&"["&"District"&"]"),R25,INDIRECT("Combined_Data"&"["&"Job Status"&"]"),"Active",INDIRECT("Combined_Data"&"["&"Crown District"&"]"),"<>""")+G35

While I can choose one district with this using INDIRECT("Combined_Data"&"["&"District"&"]"),R25 that instruction, I have a problem choosing multiple districts using a variable. This is what I have now>

=SUM(COUNTIFS(INDIRECT("Combined_Data"&"["&"On Air Date"&"]"),F$4,INDIRECT("Combined_Data"&"["&"Software"&"]"),"N",INDIRECT("Combined_Data"&"["&$R18&"]"),">="&G$2,INDIRECT("Combined_Data"&"["&$R18&"]"),"<="&G$3,INDIRECT("Combined_Data"&"["&$S18&"]"),"",INDIRECT("Combined_Data"&"["&"National Program 2"&"]"),D$4,INDIRECT("Combined_Data"&"["&"District"&"]"),{"HOU","AL","FLA","AUS","GA","SCA"},INDIRECT("Combined_Data"&"["&"Job Status"&"]"),"Active",INDIRECT("Combined_Data"&"["&"Crown District"&"]"),"<>"""))+F18

It chooses Houston, Alabama, Florida, Austin, Georgia, and South Carolina > INDIRECT("Combined_Data"&"["&"District"&"]"),{"HOU","AL","FLA","AUS","GA","SCA"}
And works just fine,, but I can't find a way to use a variable to encompass all those districts.
I don't want to have to alter 140 formulas each time I want to move the report around the country to other sets of districts. Is there a way to represent that grouping using a variable? I tried just tossing it in as a variable and a few other thoughts, but nothing seems to work.

Any help would be appreciated,
Thank you,
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Watch MrExcel Video

Forum statistics

Threads
1,126,932
Messages
5,621,676
Members
415,849
Latest member
PhoenixRising2015

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