Count months

Hi


I am trying to count number of entries for the month of Jan (for example)


but cant work out the formula?? any help would be appreciated

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Paul,

    The formula for January would look like this:

    =COUNTIF([expected comp date]:[expected comp date], IFERROR(MONTH(@cell), 0) = 1)

    For each month, you would change the "= 1)" to the corresponding month number. So, February would be =2, March would be =3, etc.

    You could also have a Month column with the month numbers listed, then use the following formula:

    =COUNTIF([expected comp date]:[expected comp date], IFERROR(MONTH(@cell), 0) = month@row)



    Hope this helps!

    Best,

    Heather

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Are you counting by month and year? If so substitute "Column Name" with the actual name of your column. This example is looking at the month of May and the Year of 2020. You can use this formula in the sheet summary section to count each month in the year.

    =COUNTIFS([Column Name]:[Column Name], AND(Month(@cell)=5, Year(@cell)=2021))

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Paul,

    The formula for January would look like this:

    =COUNTIF([expected comp date]:[expected comp date], IFERROR(MONTH(@cell), 0) = 1)

    For each month, you would change the "= 1)" to the corresponding month number. So, February would be =2, March would be =3, etc.

    You could also have a Month column with the month numbers listed, then use the following formula:

    =COUNTIF([expected comp date]:[expected comp date], IFERROR(MONTH(@cell), 0) = month@row)



    Hope this helps!

    Best,

    Heather

  • Paul Molloy
    Paul Molloy ✭✭✭✭

    Hi Mike

    Thanks for your quick resonse

    i got thre below


    looks very close ;)

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Oh Man, heather added the valuable IFERROR. I have added it to my formula below. This should get you a total. Basically, any rows without a date will calculate as 0 and not be counted.

    =COUNTIFS([Column Name]:[Column Name], AND(IFERROR(Month(@cell),0)=5, IFERROR(Year(@cell),0)=2021))

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Basically, without the IFERROR, any blanks in the column will give you invalid data.

  • Paul Molloy
    Paul Molloy ✭✭✭✭

    honestly this community is great. thank you so much for help, now working fine ;)

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
  • Cleversheet
    Cleversheet ✭✭✭✭✭✭

    Here's a generic solution for counting the months from today (or other referenced date)...

    In column [Months from Today] this column-formula will show the number of months before/-after the value in [TestDate], where prior months are positive and future months are negative. If [TestDate] is in the current month the result is zero (0).

    =IF([TestDate]@row < TODAY(), (VALUE(MONTH(TODAY()))) + (13 - VALUE(MONTH([TestDate]@row))) + ((VALUE(YEAR(TODAY())) - VALUE(YEAR([TestDate]@row)) - 1) * 12) - 1, (VALUE(MONTH(TODAY()) - 12)) + (-VALUE(MONTH([TestDate]@row))) + ((VALUE(YEAR(TODAY())) + 1 - VALUE(YEAR([TestDate]@row))) * 12))

    To compare a given date against a particular date instead of against today, replace all instances of TODAY() with that date’s cell (or summary field) reference.

    Note that this does not take into account the number days, only month values. Thus from 4/30/21 to 5/1/21 yields 1 "month" even though there's only one day difference between those dates.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!