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!

Find more posts tagged with

Formulas

Accepted answers

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:

All comments

Mike TV

@mariahneu

Ok. This should work I believe:

Mike TV

@mariahneu

If you want to see zeros instead of blank spaces in some of those months, switch to using this instead:

mariahneu

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

Perfect! Thank you!!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.