count order totals by day in that week

Jason P

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.

Kelly Moore

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

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

Kelly Moore

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

Hi @Kelly Moore

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

Jason P

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

Kelly Moore

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

Thanks @Kelly Moore

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

Kelly Moore

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

Thankyou.