Total cost to previous Wednesday

Options
Indi
Indi
edited 12/09/19 in Formulas and Functions

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.

Tags:

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!