SUMIFS question
On one of my sheets, I am summing a range based off of another columns date range and another columns value (true/false).
Currently, the formula I am using is:
=SUMIFS({JOBS AWARDED Lighting}, {JOBS AWARDED Bid Date}, AND(@cell > DATE(2020, 1, 1), @cell < DATE(2020, 12, 31)), {JOBS AWARDED Range 4}, @cell = 1)
However, there are times when the data I need is another another column other than {JOBS AWARDED Lighting}. So for a given row, we might have a value in the column {JOBS AWARDED Lighting}, but IF a value exist in another column, I need it to use that value instead of the one in {JOBS AWARDED Lighting} for that row.
How can I do that? I am unable to wrap my head around it with the syntax Smartsheet's uses.
Answers
-
Hey @Travis Myers
If I understand, you need to check a cross sheet reference to see if a cell has a value. If it does, Sum using that, if it doesn't, use the sumifs you have written.
One solution is using the COUNTIFS to see if the other cell is blank (or, as I have written, not blank)
Not knowing if the criteria were the same, I only illustrated the strategy.
IF(COUNTIFS({Another column},@cell<>"")>1, SUMIFS(for another column + criteria), =SUMIFS({JOBS AWARDED Lighting}, {JOBS AWARDED Bid Date}, AND(@cell > DATE(2020, 1, 1), @cell < DATE(2020, 12, 31)), {JOBS AWARDED Range 4}, @cell = 1))
Kelly
-
I believe you are correct.
To try and explain it a bit further, they are bid/buy columns. Sometimes the buy amount is the exact same as the bid, so the buy is left blank. In that instance I would sum using the bid value. But if the buy column has a value, I would need the value in bid ignored.
I will try your formula and see if it does what I am after.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!