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

Dorota Dunne

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

Leibel S

@Dorota Dunne

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

Eric Law

https://community.smartsheet.com/discussion/114271/count-number-of-events-this-wtd-mtd-qtd-and-ytd

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.

Leibel S

@Dorota Dunne

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

Dorota Dunne

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

MPath

https://community.smartsheet.com/discussion/comment/409405#Comment_409405

Sooooooo helpful! Thanks so much!