Using Sum if in summary field to add costs

Kate Hayes

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

Debbie Sawyer

=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

Hi Debbie,

thanks for the response, unfortunately not, same response

Thanks

Kate

Debbie Sawyer

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

Kate Hayes

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

=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

Hi Debbie

yes that worked, thank you so much for your help

Kate

Debbie Sawyer

You're welcome - Yay!