Zero value to return blank cell
I want to tweak the below formula to return a blank cell if the value is zero:
=SUMIFS({Small Projects Value}, {Small Projects Client}, [Primary Column]@row, {Small Projects Status}, OR(@cell = "Projected", @cell = "Schedule", @cell = "Not Started"), {Small Projects Start}, AND(IFERROR(MONTH(@cell), 0) = 9, IFERROR(YEAR(@cell), 0) = 2024))
I previously used the formula below which works great.
=IF(SUMIFS({Archive Sales Amount}, {Archive Sales Client}, [Primary Column]@row, {Archive Sales Stage}, AND(@cell = "4 - Closed Won"), {Archive Sales Close Date}, IFERROR(YEAR(@cell), 0) = 2023) = 0, "", SUMIFS({Archive Sales Amount}, {Archive Sales Client}, [Primary Column]@row, {Archive Sales Stage}, AND(@cell = "4 - Closed Won"), {Archive Sales Close Date}, IFERROR(YEAR(@cell), 0) = 2023))
However, if I follow the same method with the formula above, it comes up with error #INCORRECT. Not sure what I am missing.
=IF(SUMIFS({Small Projects Value}, {Small Projects Client}, [Primary Column]@row, {Small Projects Status}, OR(@cell = "Projected", @cell = "Schedule", @cell = "Not Started"), {Small Projects Start}, AND(IFERROR(MONTH(@cell), 0) = 9, IFERROR(YEAR(@cell), 0) = 2024) = 0, "", SUMIFS({Small Projects Value}, {Small Projects Client}, [Primary Column]@row, {Small Projects Status}, OR(@cell = "Projected", @cell = "Schedule", @cell = "Not Started"), {Small Projects Start}, AND(IFERROR(MONTH(@cell), 0) = 9, IFERROR(YEAR(@cell), 0) = 2024)))
Best Answer
-
I think you might be missing a closing parenthesis before the =0
=IF(
SUMIFS({Small Projects Value}, {Small Projects Client}, [Primary Column]@row, {Small Projects Status}, OR(@cell = "Projected", @cell = "Schedule", @cell = "Not Started"), {Small Projects Start}, AND(IFERROR(MONTH(@cell), 0) = 9, IFERROR(YEAR(@cell), 0) = 2024))= 0, "",
SUMIFS({Small Projects Value}, {Small Projects Client}, [Primary Column]@row, {Small Projects Status}, OR(@cell = "Projected", @cell = "Schedule", @cell = "Not Started"), {Small Projects Start}, AND(IFERROR(MONTH(@cell), 0) = 9, IFERROR(YEAR(@cell), 0) = 2024)))
Answers
-
I think you might be missing a closing parenthesis before the =0
=IF(
SUMIFS({Small Projects Value}, {Small Projects Client}, [Primary Column]@row, {Small Projects Status}, OR(@cell = "Projected", @cell = "Schedule", @cell = "Not Started"), {Small Projects Start}, AND(IFERROR(MONTH(@cell), 0) = 9, IFERROR(YEAR(@cell), 0) = 2024))= 0, "",
SUMIFS({Small Projects Value}, {Small Projects Client}, [Primary Column]@row, {Small Projects Status}, OR(@cell = "Projected", @cell = "Schedule", @cell = "Not Started"), {Small Projects Start}, AND(IFERROR(MONTH(@cell), 0) = 9, IFERROR(YEAR(@cell), 0) = 2024))) -
Works well now. Thank you very much. @KPH
-
Happy to help @Michelle Maas
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!