SUM IF Formula
I'm struggling a bit with a SUM IF Formula and hoping you all can help. I'd like a formula to sum the total cost column of my sheet for rows 236-246 if the start date of rows 236-246 is before the start date of the formula cell row, 220 but after the row above it, 219. Thanks in advance.
Answers
-
As you have multiple criteria you will need to use a SUMIFS function rather than SUMIF.
The syntax is
=SUMIFS(range, criterion_range1, criterion1, criterion_range2, criterion2,…)
So in your case,
- the range is your total cost column which I assume is called "Total Cost" between cells 236 and 246.
- [Total Cost]236:[Total Cost]246
- the 1st criterion range is the same rows but in the start date column. I have called this column "Start Date".
- [Start Date]236:[Start Date]246
- the 1st criterion is that the value in the 1st criterion range is less than the start date in row 220. Start Date must be a date formatted column for this to work.
- <[Start Date]$220
- the 2nd criterion range is the same as the first
- [Start Date]236:[Start Date]246
- the 2nd criteria is that the value in that range is greater than the start date in row 219
- >[Start Date]219
The formula is then:
=SUMIFS([Total Cost]236:[Total Cost]246, [Start Date]236:[Start Date]246, <[Start Date]$220, [Start Date]236:[Start Date]246, >[Start Date]219)
Note - this does not sum rows where the start date is equal to either of the 219 or 220 dates.
In this example the total would be 16
Hope this helps.
- the range is your total cost column which I assume is called "Total Cost" between cells 236 and 246.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!