Using Sum if in summary field to add costs

Options

Hi

I am trying to total up a budget value, if the submitted line is between correct dates, see below

=SUMIFS([TOTAL TRIAL COSTS]:[TOTAL TRIAL COSTS], [DATE SUBMITTED]:[DATE SUBMITTED], >=[BUDGET START]#, <= [BUDGET END]#)

but getting the #unparseable

Any help would be appreciated

Thanks

Kate

Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    =SUMIFS([TOTAL TRIAL COSTS]:[TOTAL TRIAL COSTS], [DATE SUBMITTED]:[DATE SUBMITTED], >=[BUDGET START]#, [DATE SUBMITTED]:[DATE SUBMITTED],<= [BUDGET END]#)

    Does this work for you?

    Kind regards

    Debbie

  • Kate Hayes
    Options

    Hi Debbie,

    thanks for the response, unfortunately not, same response

    Thanks

    Kate

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    I know this sounds silly but you are substituting the # for a row number aren't you...

  • Kate Hayes
    Options

    Hi Debbie

    Yes, basically I need to add total trial costs for projects submitted between certain dates, anything outside of the dates would non forecasted, and as such should not appear in the total budget

    I hope that makes sense

    I did try replacing the columns for budget start & end with dates - but didn't like that either

    Thanks Kate

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    =SUMIFS([TOTAL TRIAL COSTS]:[TOTAL TRIAL COSTS], [DATE SUBMITTED]:[DATE SUBMITTED], >=[BUDGET START]#, [DATE SUBMITTED]:[DATE SUBMITTED],<= [BUDGET END]#)

    The way this reads is:

    Sum the Total Trial Costs column if any value in the Date Submitted column is greater than or equal to the date in cell Budget Start # AND the Date Submitted is less than or equal to the date in the cell Budget End #.

    (where # is a row number)

    Lets say the date you are comparing to is on row1 then the formula would be:

    =SUMIFS([TOTAL TRIAL COSTS]:[TOTAL TRIAL COSTS], [DATE SUBMITTED]:[DATE SUBMITTED], >=[BUDGET START]1, [DATE SUBMITTED]:[DATE SUBMITTED],<= [BUDGET END]1)

    I have recreated this logic and as long as the column you are summing is a number column and the Dates are declared as Date Types then the above should work (if the logic is what you wanted...)

    Are you sure your dates are declared as dates? What is the error message? Did you copy and paste my version into your sheet (as I did add a second Range2 area from your original post)

    You are so close to this working, I am sure we can get there!

    Kind regards

    Debbie

  • Kate Hayes
    Options

    Hi Debbie

    yes that worked, thank you so much for your help

    Kate

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    You're welcome - Yay!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!