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