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

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

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!