# Count months

Options
✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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))

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

Hi Mike

i got thre below

looks very close ;)

• ✭✭✭✭✭✭
Options

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))

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

Awesome.

• ✭✭✭✭✭✭
Options

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!