# Count number of events this WTD, MTD, QTD and YTD

Options

Hi,

I'm looking for a formula that I could use to count number of events that were reported in the current week, month quarter and year to date. The reported date is autopopulated in a standard date format and the count will be in the Sheet Summary. thank you for all your suggestions

• ✭✭✭✭✭✭
Options

This could be easier with helper columns and/or 'Helper Sheet Summary Fields' but can be done as shown below

WTD:

=COUNTIFS(Date:Date, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))

MTD:

=COUNTIFS(Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

QTD:

=COUNTIFS(Date:Date, AND(IFERROR(ROUNDUP(MONTH(@cell) / 3, 0), 0) = ROUNDUP(MONTH(TODAY()) / 3, 0), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

YTD:

=COUNTIFS(Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

• ✭✭✭✭✭✭
Options

Can you share some screen shots of your columns and it's content? When you say reported date is autopopulated, is it the created by date type or is it a date column that has an automation to capture the current date? Are the different events columns? Please provide more details since there are many ways to set up a sheet to do something like this.

• ✭✭✭✭✭✭
Options

This could be easier with helper columns and/or 'Helper Sheet Summary Fields' but can be done as shown below

WTD:

=COUNTIFS(Date:Date, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))

MTD:

=COUNTIFS(Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

QTD:

=COUNTIFS(Date:Date, AND(IFERROR(ROUNDUP(MONTH(@cell) / 3, 0), 0) = ROUNDUP(MONTH(TODAY()) / 3, 0), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

YTD:

=COUNTIFS(Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

• Options

thank you so much Eric and Leibel. I used your formulas Leibel and it works fine for me. Thank you!

• ✭✭✭
Options