COUNTING MTD ENTRIES

Jason Egles

I have been able to get YTD results with this formula:

=COUNTIFS({DATE SOLD}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

However, I am unable to get a formula right for MTD. Any assistance would be much appreciated

Thanks,

Jason

Paul Newcome

Your YTD formula actually does not need the AND statement since you only have one criteria set for that range.

=COUNTIFS({DATE SOLD}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

And now for the answer to your actual question haha.

We DO need the AND for this one because we are going to specify a year AND a month. The month is actually going to look almost identical to the YEAR portion with the exception that MONTH and YEAR are obviously spelled different.

=COUNTIFS({DATE SOLD}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), IFERROR(MONTH(@cell), 0) = MONTH(TODAY())))

Paul Newcome

Ah. I didn't realize you were wanting to do that. In that case we just need to add in a DAY function in the MTD criteria section and a YEARDAY function in the YTD.

YTD:

=COUNTIFS({DATE SOLD}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1, IFERROR(YEARDAY(@cell), 400) <= YEARDAY(TODAY())))

MTD:

=COUNTIFS({DATE SOLD}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(DAY(@cell), 32) <= DAY(TODAY())))

Paul Newcome

Your YTD formula actually does not need the AND statement since you only have one criteria set for that range.

=COUNTIFS({DATE SOLD}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

And now for the answer to your actual question haha.

We DO need the AND for this one because we are going to specify a year AND a month. The month is actually going to look almost identical to the YEAR portion with the exception that MONTH and YEAR are obviously spelled different.

=COUNTIFS({DATE SOLD}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), IFERROR(MONTH(@cell), 0) = MONTH(TODAY())))

Jason Egles

Paul, you saved me again. Thanks so much.

Paul Newcome

Happy to help! 👍️

Jason Egles

Paul, Is there a modification that can be made to these two formulas to display: YTD count of prior year and MTD count of prior year?

=COUNTIFS({DATE SOLD}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

=COUNTIFS({DATE SOLD}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), IFERROR(MONTH(@cell), 0) = MONTH(TODAY())))

Paul Newcome

Yes. We just subtract 1 from the year criteria in each of them

=COUNTIFS({DATE SOLD}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1)

=COUNTIFS({DATE SOLD}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1, IFERROR(MONTH(@cell), 0) = MONTH(TODAY())))

Jason Egles

Thanks Paul. Unfortunately, the 1st formula counts all of 2019 entries rather than stopping at today's date and month of 2019

The second formula counts all April 2019 entries rather than stopping at today's date (4/9) of 2019

Paul Newcome

Ah. I didn't realize you were wanting to do that. In that case we just need to add in a DAY function in the MTD criteria section and a YEARDAY function in the YTD.

YTD:

=COUNTIFS({DATE SOLD}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1, IFERROR(YEARDAY(@cell), 400) <= YEARDAY(TODAY())))

MTD:

=COUNTIFS({DATE SOLD}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(DAY(@cell), 32) <= DAY(TODAY())))

Jason Egles

Awesome! Thanks again Paul!

Paul Newcome

Sure thing!