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
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!