# Count of prior month dates

Options

I am using the following formula to count the dates in this row that are equal to the current month. However, I am trying to make adjustments to this formula to count dates that appear in the prior month and keep getting errors. I believe part of my challenge is accounting for the end of the year.

=COUNTIFS([First Date]@row:[Eighth Date]@row, >=DATE(YEAR(TODAY()), MONTH(TODAY()), 1), [First Date]@row:[Eighth Date]@row, <IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)))

Tags:

• ✭✭✭✭✭✭
Options

Hey Michelle

When I do this I use an IF statement to look for January.

=IF(MONTH(TODAY())=1,COUNTIFS([First Date]@row:[Eighth Date]@row, ISDATE(@cell), [First Date]@row:[Eighth Date]@row, YEAR(TODAY())-1, [First Date]@row:[Eighth Date]@row, MONTH(@cell)=12), COUNTIFS([First Date]@row:[Eighth Date]@row, ISDATE(@cell), [First Date]@row:[Eighth Date]@row, YEAR(TODAY()), [First Date]@row:[Eighth Date]@row, MONTH(@cell)=MONTH(TODAY())-1)

Does this work for you?

Kelly

• ✭✭✭✭✭✭
Options

Counting data from the previous month in January is always difficult to automate. You might try modifying the TODAY functions to find the YEAR and MONTH for TODAY(-31). That will almost always give you the year and day of the previous month, except on a few 1st days of the month.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

Hey Michelle

When I do this I use an IF statement to look for January.

=IF(MONTH(TODAY())=1,COUNTIFS([First Date]@row:[Eighth Date]@row, ISDATE(@cell), [First Date]@row:[Eighth Date]@row, YEAR(TODAY())-1, [First Date]@row:[Eighth Date]@row, MONTH(@cell)=12), COUNTIFS([First Date]@row:[Eighth Date]@row, ISDATE(@cell), [First Date]@row:[Eighth Date]@row, YEAR(TODAY()), [First Date]@row:[Eighth Date]@row, MONTH(@cell)=MONTH(TODAY())-1)

Does this work for you?

Kelly

• Options

Thank you! This is very helpful.

• ✭✭✭✭✭✭
Options

I didn't even think about just giving it different parameters for January within the same formula. Nice approach!

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

I generally try to nest the IF statements inside of the COUNTIFS because the IF statements are (usually) shorter than the COUNTIFS which will save you some keystrokes.

=COUNTIFS([First Date]@row:[Eighth Date]@row, AND(@cell< DATE(YEAR(TODAY()), MONTH(TODAY()), 1), @cell>= DATE(YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1), IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), 1)))

or

=COUNTIFS([First Date]@row:[Eighth Date]@row, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1), IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1)))

The first establishes a date range of less than the first of the current month and greater than or equal to the first of the previous month. The second one is a YEAR and MONTH comparison as opposed to a date range. Both show how replicating the shorter portion of the formula can shorten things quite a bit even with adding in the additional AND function in both and IFERROR functions in the second.