Count months

Paul Molloy

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

Heather Duff

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

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

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

Hi Mike

Thanks for your quick resonse

i got thre below

looks very close ;)

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

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

Paul Molloy

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

Paul Molloy

Mike Wilday

Awesome.

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.