SUMIFS function

duggo65 · 07/25/18

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.

Paul Newcome · 07/25/18

Try taking out the quotes.

duggo65 · 07/25/18

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?

Paul Newcome · 07/26/18

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.

duggo65 · 07/27/18

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

Paul Newcome · 07/27/18

Glad to be of assistance.

SUMIFS function

Comments

Help Article Resources

Categories