Countifs and indirect references question
Results 1 to 2 of 2

Thread: Countifs and indirect references question
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Countifs and indirect references question

    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,

  2. #2
    New Member
    Join Date
    Oct 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs and indirect references question

    Bump for help!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •