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

✭✭✭✭
edited 06/22/22

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!

Tags:

## Best Answers

• ✭✭✭✭✭✭
edited 06/21/22 Answer ✓

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

• ✭✭✭✭✭✭
Answer ✓

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

• ✭✭✭✭✭✭
edited 06/21/22 Answer ✓

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

• ✭✭✭✭✭✭
Answer ✓

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!