# SUMIFS function

Options
edited 12/09/19

I would like to the sum of one column based on a specific date range in another column.

e.g Need to total costs for June 2018.  I am using the following formula

=SUMIFS(Cost3:Cost29,[End Date]3:[End Date]29,”>=01/06/2018″,[End Date]3:[End Date]29,”<=30/06/2017″)

However it is coming back with an error #UNPARSEABLE.

Tags:

• ✭✭✭✭✭✭
Options

Try taking out the quotes.

• Options

Thanks Paul - removing the quotes did not work.  I am now trying a simpler SUMIF function.  I want to total costs with an end date greater than 1 June 2018 and using the following function:

=SUMIF([End Date]3:[End Date]29, " >1 / 6 / 18", Cost3:Cost29)

this is returning a zero sum. (at least its not an error!)   I have tried with and without quotes.  Could it be something to do with the column data?

• ✭✭✭✭✭✭
Options

Ugh. Totally missed it the first time. When entering formulas in smartsheet, if you surround it in quotes it will look for EXACTLY that. =1+1 in a cell will give you 2. whereas ="1+1" will show as 1+1. When referencing a date you have to use a DAT function. DATE(yyyy,mm,dd). If you put in 7/15/2018 smartsheet will read it as 7 divided by 15 divided by 2018.

Therfore...

=SUMIF([End Date]3:[End Date]29, " >1 / 6 / 18", Cost3:Cost29)

changes to

=SUMIF([End Date]3:[End Date]29, >DATE(2018,06,01), Cost3:Cost29)

Notice: No quotes because we are not looking for that specific text. Date format changed to proper Smartsheet language.

I must have been a little extra out of it yesterday. My apologies.

• Options

Thanks so much Paul - works brilliantly!!!!   Also incorporated this into my original query - problem solved!

• ✭✭✭✭✭✭
Options