Formula to add two separate columns with MONTH and YEAR criteria (blank dates too)
The formula below for March works for rows where [Date Recd] and [DateCompleted] both have dates; however, it errors out when [DateCompleted] is blank. How do I update the formula below to also sum [Received Count] where [DateCompleted] is blank and [Date Recd]'s Year = 2022 and Month = 3? Need the formula below to add either (or both) Received Count and Completed Count based on the year and month.
So for March's column above, it should equal .5 for March for all five of the above example rows. But for rows (not pictured) where [Date Recd] is March 2022 and [DateCompleted] is also March 2022, March's column should = 1 since it needs to add .5 from [Received Count] and .5 from [Completed Count].
Original Formula:
=IF(AND(YEAR([Date Recd]@row) = 2022, MONTH([Date Recd]@row) = 3), [Received Count]@row) + IF(AND(YEAR(DateCompleted@row) = 2022, MONTH(DateCompleted@row) = 3), [Completed Count]@row)
Thanks!
Best Answers
-
Ok. This should work I believe:
=IF(ISDATE(DateCompleted@row), IF(AND(YEAR([Date Recd]@row) = 2022, MONTH([Date Recd]@row) = 3), [Received Count]@row) + IF(AND(YEAR(DateCompleted@row) = 2022, MONTH(DateCompleted@row) = 3), [Completed Count]@row), IF(AND(YEAR([Date Recd]@row) = 2022, MONTH([Date Recd]@row) = 3), [Received Count]@row))
-
If you want to see zeros instead of blank spaces in some of those months, switch to using this instead:
=IF(ISDATE(DateCompleted@row), IF(AND(YEAR([Date Recd]@row) = 2022, MONTH([Date Recd]@row) = 3), [Received Count]@row) + IF(AND(YEAR(DateCompleted@row) = 2022, MONTH(DateCompleted@row) = 3), [Completed Count]@row), IF(AND(YEAR([Date Recd]@row) = 2022, MONTH([Date Recd]@row) = 3), [Received Count]@row, 0))
Answers
-
Ok. This should work I believe:
=IF(ISDATE(DateCompleted@row), IF(AND(YEAR([Date Recd]@row) = 2022, MONTH([Date Recd]@row) = 3), [Received Count]@row) + IF(AND(YEAR(DateCompleted@row) = 2022, MONTH(DateCompleted@row) = 3), [Completed Count]@row), IF(AND(YEAR([Date Recd]@row) = 2022, MONTH([Date Recd]@row) = 3), [Received Count]@row))
-
If you want to see zeros instead of blank spaces in some of those months, switch to using this instead:
=IF(ISDATE(DateCompleted@row), IF(AND(YEAR([Date Recd]@row) = 2022, MONTH([Date Recd]@row) = 3), [Received Count]@row) + IF(AND(YEAR(DateCompleted@row) = 2022, MONTH(DateCompleted@row) = 3), [Completed Count]@row), IF(AND(YEAR([Date Recd]@row) = 2022, MONTH([Date Recd]@row) = 3), [Received Count]@row, 0))
-
Perfect! Thank you!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 412 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 140 Just for fun
- 57 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!