I am trying to get a total count of matching dates based and running into issues

My formula is coming back #UNPARSEABLE and can't seem to figure out how to make this work:

=SUMPRODUCT(({Compass Incident Mgmt System Range 2} = {Compass Incident Mgmt System Range 13}) * (EXACT([Primary Column]16, {Compass Incident Mgmt System Range 3})) * ({Compass Incident Mgmt System Range 2} <> ""))

I am trying to ultimately get a total count of dates that match between two date columns but only when the value (in this case a name) is present in the Range 3 column...ignoring blanks in either date column.

In excel I accomplished this with this formula:

=SUM(--('Vision Incident Mgmt System'!AJ:AJ<>"")*('Vision Incident Mgmt System'!AJ:AJ='Vision Incident Mgmt System'!AB:AB)*('Vision Incident Mgmt System'!AC:AC=A11))

AJ and AB were my date columns and A11 was the cell with the name. However, this doesn't appear supported in smartsheet.

Any help would be appreciated.

Find more posts tagged with

Accepted answers

Paul Newcome

You will need a helper column on the source sheet that indicates when the dates are the same.

=IF(Resolved@row = [R&R Date Assigned]@row, 1)

Then in the metrics sheet you would incorporate this helper column into a COUNTIFS.

All comments

Paul Newcome

There is no SUMPRODUCT function in Smartsheet. Are you able to provide a screenshot for context?

Pablo

Hopefully this helps. I want a total count where the R&R Date Assigned is the exact same as the Date Resolved for each of my Issue Owners. Trying to get a total count of items that were resolved the same date as assigned for each of the agents. I have the two date columns and the name column but not sure how to do it in smartsheet.

Paul Newcome

You will need a helper column on the source sheet that indicates when the dates are the same.

=IF(Resolved@row = [R&R Date Assigned]@row, 1)

Then in the metrics sheet you would incorporate this helper column into a COUNTIFS.

Pablo

Great! Thank you so much! This worked and I was able to complete my calculations.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.