SUMIFS function
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.
Comments
-
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?
-
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.
-
Thanks so much Paul - works brilliantly!!!! Also incorporated this into my original query - problem solved!
-
Glad to be of assistance.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!