Countifs and indirect references question


New Member
Oct 31, 2017
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics