nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Help with a cross sheet formula: counting unique values when certain parameters are met

B Fantozzi

Hi there - I'm in need of a little help with a formula for a metrics sheet I'm creating.

Metrics sheet looks like this:

And I'd like the Units cell to display the a count of how many times a ID Number shows up in another sheet for the month and the year.

In this case, the result for Jan 2025 would be 7 (since EQN187742 is repeated and should only be counted once).

Thanks in advance for sharing your time and talents!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

You can also do this without the helper columns by using the MOTNH and YEAR functions directly in your formula.

=COUNT(DISTINCT(COLLECT({Source Sheet ID Column}, {Source Sheet Date Column}, AND(IFERROR(YEAR(@cell), 0) = Year@row, IFERROR(MONTH(@cell), 0) = 1))))

Just change the 1 at the end to reflect the month number you want to count for.

All comments

Andy Kolden

Hi B,

The quickest way I was able to accomplish this was by creating two helper columns on your Claims - Database sheet to parse the year and month from your Claims File Date column.

Column Formulas:

Year Helper is =YEAR([Claims File Date]@row)
Month Helper is =Month([Claims File Date]@row)

Then, on the Claims Metric Sheet, you'll use the following formula in the Jan column:

=COUNT(DISTINCT(COLLECT({Data Range 1}, {Data Range 2}, Year@row, {Data Range 3}, 1)))

{Data Range 1} is the Equipment Serial/ID column.

{Data Range 2} is the Year Helper column.

{Data Range 3} is the Month Helper column.

You'd then copy that formula into the rest of the month columns and change the final digit in the formula to correspond to the appropriate month where January is 1, February is 2, etc.

Edit: Once the Year and Month helper columns are set up, they can of course be hidden to keep the sheet cleaner.

B Fantozzi

Success! That combination of COUNT(DISTINCT(COLLECT was just what I needed. I really appreciate you taking the time to help and I hope you have a super day!

Paul Newcome

You can also do this without the helper columns by using the MOTNH and YEAR functions directly in your formula.

=COUNT(DISTINCT(COLLECT({Source Sheet ID Column}, {Source Sheet Date Column}, AND(IFERROR(YEAR(@cell), 0) = Year@row, IFERROR(MONTH(@cell), 0) = 1))))

Just change the 1 at the end to reflect the month number you want to count for.

Andy Kolden

Thank you, @Paul Newcome! I figured there was a way to do it without helpers, but kept getting errors. I'm certain my sheets have numerous unnecessary helper columns, but if it ain't broke… ;)

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