Forum Discussion

AllanPritchard's avatar
AllanPritchard
Copper Contributor
May 11, 2026
Solved

Need quick help counting values from a list for presentation.

Hi All I could usually work this out by searching google and the forum if I had time but its run out so looking for some quick community assistance to get me through the day.  I need to count how m...
excel
Formulas and Functions
  • Olufemi7's avatar
    Olufemi7
    May 12, 2026

    HelloAllanPritchard​,

    The issue is caused by the helper column returning blank values, which COUNTIF then treats as valid criteria, resulting in inflated counts.

    You don’t need the helper column. Use the in-area flag directly from DataLists and count matches in the selected year sheet:

    =SUMPRODUCT((DataLists!D2:D47=1)*COUNTIF(INDIRECT("'"&$C$2&"'!H3:H200"),DataLists!E2:E47))

    If you are on Microsoft 365, a cleaner option is:

    =LET(loc,FILTER(DataLists!E2:E47,DataLists!D2:D47=1),SUM(COUNTIF(INDIRECT("'"&$C$2&"'!H3:H200"),loc)))

    Both return the correct count of “in area” jobs for the selected year.

AllanPritchard's avatar
AllanPritchard
Copper Contributor
May 17, 2026

Thanks, this worked perfectly. I knew there would be a way to use the flags but couldn't get my head around it in the rush. Its now a permanent report so have fixed it using your formula. 

Cheers

Al