count order totals by day in that week

Options

Hi all.

I'm needing a formula that counts the number of order scheduled by date for dispatch for each day of the current week. No issue with =COUNTIF([Ship Date]:[Ship Date], =TODAY()) however live daily totals would trigger notifications that service levels may not be met say if Friday had "89" etc.

Thanks all.


Cheers.

Forever forwards Backwards never.

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Jason P

    Try this. Note that the WEEKDAY function begins on Sunday (Sunday = 1) so you will need to modify the formula for each of your days of the week. I also added a filter so you are only looking at the current year, since Days of Week and Weeknumbers repeat for every year.

    =COUNTIFS([Shipping Date]:[Shipping Date], AND(ISDATE(@cell), IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), IFERROR(WEEKDAY(@cell), 0) = 2))

    Will this work for you?

    Kelly

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    lol, my bad. The +1 was supposed to be outside the parenthesis. As written, I have it saying to look at weeknumber of tomorrow.

    =COUNTIFS([Shipping Date]:[Shipping Date], AND(ISDATE(@cell), IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY())+1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), IFERROR(WEEKDAY(@cell), 0) = 2))

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Jason P

    Try this. Note that the WEEKDAY function begins on Sunday (Sunday = 1) so you will need to modify the formula for each of your days of the week. I also added a filter so you are only looking at the current year, since Days of Week and Weeknumbers repeat for every year.

    =COUNTIFS([Shipping Date]:[Shipping Date], AND(ISDATE(@cell), IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), IFERROR(WEEKDAY(@cell), 0) = 2))

    Will this work for you?

    Kelly

  • Jason P
    Jason P ✭✭✭✭
    Options

    Hi @Kelly Moore

    Many thanks, grateful for your time - works a treat.

    Cheers.

    Forever forwards Backwards never.

  • Jason P
    Jason P ✭✭✭✭
    Options

    Hi @Kelly Moore

    This is working great and is already yielding benefits. Is there a way of getting the same data but for days in the following week? would further help with scheduling.

    Regards

    Cheers.

    Forever forwards Backwards never.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Jason P

    Yes, we would do this by adding on the WEEKNUMBER. The formula below says to compare the shipping date to Today's Weeknumber+1, which is next week. WEEKNUMBER function begins on Mondays, so a smartsheet week is Mon-Sun.

    =COUNTIFS([Shipping Date]:[Shipping Date], AND(ISDATE(@cell), IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()+1), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), IFERROR(WEEKDAY(@cell), 0) = 2))

    Does this work for you?

    Kelly

  • Jason P
    Jason P ✭✭✭✭
    Options

    Thanks @Kelly Moore

    The +1) had no affect! - I still get today's totals, I changed the days, 2, 3 etc and same result.

    Cheers.

    Forever forwards Backwards never.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    lol, my bad. The +1 was supposed to be outside the parenthesis. As written, I have it saying to look at weeknumber of tomorrow.

    =COUNTIFS([Shipping Date]:[Shipping Date], AND(ISDATE(@cell), IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY())+1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), IFERROR(WEEKDAY(@cell), 0) = 2))

  • Jason P
    Jason P ✭✭✭✭
    Options

    Thankyou.

    Cheers.

    Forever forwards Backwards never.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!