Forum Discussion

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
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.

Olufemi7

Steel Contributor

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.