Using SUMIFS with ISBLANK

Calli Vasilarakis

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!

Jon Mark H

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

Jon Mark H

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

Calli Vasilarakis

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 = ""))

Jon Mark H

Excellent!