Forum Discussion

AllanPritchard's avatar
AllanPritchard
Occasional Reader
May 11, 2026

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 many times we went to specific locations that are "In area". there is a list of 47 locations and 13 of them are considered "In area" 

I have a list of jobs in sperate sheets named based on years (2024, 2025, 2026) in those sheets I have a column that lists the location the job was in, which is pulled from a list of locations as a list in the sheet "DataLists". Each year there are approximately 250 jobs and of that 150 are probably in area.

In the DataList sheet where the list of locations are pulled from, next to each location listed in column E I have a 1 or 0,  in column D, 1 for "in area" and 0 for "out of area". 

I also have a "Report" sheet that generates a report based on the selected year as a drop down in cell C2

I want to count how many times we went to jobs the were "in area". I tried a quick solution to get me through today and generated a new list in column Q in the DataList sheet with: 

=IF(D2=1,E2,"") this made a list of only "in area" locations in column Q.

Then in one of the year sheets I tried the following formula

=SUMPRODUCT(COUNTIF(H3:H200, DataLists!Q2:Q47)) where H3:H200 is the locations of the jobs and DataList!Q2:Q7 is the list of "in Area" and got 3335 which is very wrong as there are only 97 jobs so far in 2026 and maybe 50-60 of those are "In Area"

I was going to then expand it to the following in the "Report" sheet. 

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$C$2&"'!h:h"),DataLists!Q2:Q50)) so it pulls the data from the selected year entered into cell c2

What am I doing wrong here, am I using the wrong function. Sorry only have a few hours before presentation.

Ideally I would like the formula in the "Report" sheet, to sum the total number of occurrences from the "year" sheet column H:H for any match from "Datalist" sheet column E:E but only if D:D = 1   

I'll take any quick fix at this stage.

Cheers

Al

 

 

excel
Formulas and Functions

1 Reply

  • Olufemi7's avatar
    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.