Using SUMIFS with ISBLANK
I have a working SUMIFS function already that has multiple criterions and is working on references from other sheets, however I am trying to add one last criterion that is related to a range that already has a criterion associated with it.
=SUMIFS({Pivot - SFDC Opportunities wRVUs Mod/Est}, {Pivot - SFDC Opportunities Closed Date (Year)}, 2022, {Pivot - SFDC Opportunities Probability}, >0, {Pivot - SFDC Opportunities State - Region}, $[Primary Column]@row, {Pivot - SFDC Opportunities IMD Deal Close Year}, >2021)
I am trying to add another criterion that says: sum the range if the {Pivot - SFDC Opportunities IMD Deal Close Year}, >2021 AND if it is BLANK. So I want to sum the range if the value in the IMD Deal Close Year column is EITHER BLANK or GREATER THAN 2021.
Can someone please help me with this? I have tried IF, AND, OR, COUNTIF, but nothing I try seems to work. Thankyou!
Best Answer
-
Hey Calli,
Give this a try:
=SUMIFS({Pivot - SFDC Opportunities wRVUs Mod/Est}, {Pivot - SFDC Opportunities Closed Date (Year)}, 2022, {Pivot - SFDC Opportunities Probability}, >0, {Pivot - SFDC Opportunities State - Region}, $[Primary Column]@row, {Pivot - SFDC Opportunities IMD Deal Close Year}:{Pivot - SFDC Opportunities IMD Deal Close Year}, OR(@cell>2021,@cell<>""))
This works with only that IMD deal close column, not sure if it'll work with all the other conditions.
Let me know if it works or you need any other help!
-Jon Mark
Answers
-
Hey Calli,
Give this a try:
=SUMIFS({Pivot - SFDC Opportunities wRVUs Mod/Est}, {Pivot - SFDC Opportunities Closed Date (Year)}, 2022, {Pivot - SFDC Opportunities Probability}, >0, {Pivot - SFDC Opportunities State - Region}, $[Primary Column]@row, {Pivot - SFDC Opportunities IMD Deal Close Year}:{Pivot - SFDC Opportunities IMD Deal Close Year}, OR(@cell>2021,@cell<>""))
This works with only that IMD deal close column, not sure if it'll work with all the other conditions.
Let me know if it works or you need any other help!
-Jon Mark
-
Thanks so much, Jon! I made this slight tweak below and it worked.
=SUMIFS({Pivot - SFDC Opportunities wRVUs Mod/Est}, {Pivot - SFDC Opportunities Closed Date (Year)}, 2022, {Pivot - SFDC Opportunities Probability}, >0, {Pivot - SFDC Opportunities State - Region}, $[Primary Column]@row, {Pivot - SFDC Opportunities IMD Deal Close Year}, OR(@cell > 2021, @cell = ""))
-
Excellent!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 60 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 40 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!