Formula to add two separate columns with MONTH and YEAR criteria (blank dates too)

mariahneu

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!

Mike TV

@mariahneu

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

Mike TV

@mariahneu

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

Mike TV

@mariahneu

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

Mike TV

@mariahneu

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

mariahneu

https://community.smartsheet.com/discussion/comment/333773#Comment_333773

Perfect! Thank you!!!