Forum Discussion

Noyman's avatar
Noyman
Copper Contributor
May 15, 2026

Countif problem

Hi - could someone advise please? 

 

I have a workbook and am trying count the number of "Apples" in Col F, using $F$3:$F$81,"Apples" as the second criteria range and criteria. The first range and criteria is column B "Column1" is has mmm-yy and goes back a couple of years from Apr-24. I have tried COUNTIFS($B$3:$B$81,[@Column1],$f$3:$f$18,"Apples". Just doesnt seem to want to work. Any ideas? perhaps mmm-yy is an issue? thanks Noyman

 

excel

2 Replies

  • RodrigoMoraesRMC's avatar
    RodrigoMoraesRMC
    Copper Contributor
    May 15, 2026

    First, I would correct the inconsistent range in your formula, since COUNTIFS requires all ranges to have exactly the same size. In your example, $F$3:$F$18 does not match $B$3:$B$81, which can lead to incorrect results or even return zero. The corrected formula should be:

    =COUNTIFS($B$3:$B$81,[@Column1],$F$3:$F$81,"Apples")

    If that still doesn’t produce the expected result, the next step is to consider how the values in column B are stored. Even though they are displayed as mmm-yy, they may actually contain full date values (including the day). In this situation, using an exact match with [@Column1] can fail, because the underlying dates are not identical.

    To make the calculation more reliable, I would switch to a month-based comparison, counting all rows where the date falls within the same month and year. That can be done using a date range:

    =COUNTIFS($F$3:$F$81,"Apples",$B$3:$B$81,">="&EOMONTH([@Column1],-1)+1,$B$3:$B$81,"<"&EOMONTH([@Column1],0)+1)

    This approach ensures that all entries within the same month are counted correctly, regardless of the specific day stored in the cells.