Total cost to previous Wednesday

Indi

Hi everyone,

I have a question and hope someone can help me with this.

If I have a new task & I want to get the total cost until the previous Wednesday, is there any formula that I can use in Smartsheet?

For example, if I open the file today, it will show the total from the start date to April 3rd.

Thank you.

Paul Newcome

I would suggest setting up a table like this (column headers in bold):

.

Weekday     Wednesday

      1                     -4

      2                     -5

      3                     -6

      4                    -7

      5                    -1

      6                   -2

      7                   -3

.

=TODAY(INDEX(Wednesday:Wednesday, MATCH(WEEKDAY(TODAY()), Weekday:Weekday, 0)))

This will give you the date for the previous Wednesday. From there you could plug it into a SUMIFS like so...

 

=SUMIFS(Cost:Cost, Date:Date, AND(@cell >= TODAY(INDEX(Wednesday:Wednesday, MATCH(WEEKDAY(TODAY()), Weekday:Weekday, 0))), @cell <= TODAY()))