Forum Discussion

Noyman's avatar
Noyman
Copper Contributor
Sep 24, 2025

Countif confused

EHi - I know this will be simple but it I cant figure it out. I want to count the number of values in column F (for example "apples") but only for the current twelve months, column B has mmm yy. I keep getting too many values when i try it. Any ideas folks? Thanks,. 

Formulas and Functions

1 Reply

  • alenezi's avatar
    alenezi
    MCT
    Sep 24, 2025

    To count the number of values in column F (e.g., "apples") only for the current last twelve months based on dates in column B formatted as "mmm yy", you can use an Excel formula with COUNTIFS and date functions like this:

    1. Ensure the dates in column B are actual date values (not text). If they appear as "mmm yy", Excel might recognize them as dates but formatted.
    2. Use a formula like the following, assuming data starts in row 2 and goes down to row 1000 (adjust as needed):

     

    =COUNTIFS(F2:F1000, "apples", B2:B1000, ">=" & EDATE(TODAY(), -12), B2:B1000, "<=" & TODAY())

     

    • F2:F1000, "apples" counts only if column F equals "apples"
    • B2:B1000, ">=" & EDATE(TODAY(), -12) ensures the date is within the last 12 months from today
    • B2:B1000, "<=" & TODAY() ensures the dates are up to today

    This counts the occurrences of "apples" in column F where the related date in column B falls within the last 12 months exactly from today's date backward.

    If the data in column B is text like "Apr 24", convert it to dates or use a helper column with a formula like:

     

    =DATEVALUE("01 " & B2)

    to convert text to an actual date for comparison.

    This method prevents counting beyond the last 12 months and filters by the specific value "apples" in column F correctly.

Resources