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.

 

Snip20180725_23.png

Tags:

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try taking out the quotes.

    thinkspi.com

  • 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?

     

     

    Snip20180726_26.png

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

    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.

    thinkspi.com

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

     

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Glad to be of assistance.

    thinkspi.com

Sign In or Register to comment.