SUMIFS function

07/25/18 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.





  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try taking out the quotes.

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    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.


    =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.

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


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Glad to be of assistance.

Sign In or Register to comment.