How can I count distinct values under a same month?

Hi everyone,

I currently have a data set for which I would like to count all unique values that are under a same month and a same year. For example, the following table shows two duplicate values (TR ID's) on two different dates. In this case I would only like to count the unique TR ID's for the month of August, 2020 (which should be 6):

I have been trying to use the =COUNT(DISTINCT(COLLECT(.....))) expression, however, it returns "1", when it should count "6".

Thanks in advance,

Carolina

Find more posts tagged with

Sheets

Formulas

Support

Solution Center

Accepted answers

All comments

Paul Newcome

Try something like this...

=COUNT(DISTINCT(COLLECT([TR ID]:[TR ID], Date:Date, AND(IFERROR(MONTH(@cell), 0) = 8, IFERROR(YEAR(@cell), 0) = 2020))

AHANSON

Hi Paul, I tried re-creating this formula for my purposes and I'm getting an "#unparseable" when I adjust the equation for my needs. The following formula should return "2":

=COUNT(DISTINCT(COLLECT([Patient number]1:[Patient number]3, [Dosing date]1:[Dosing date]3, AND(IFERROR(MONTH(@row),0) = 1, IFERROR(YEAR(@row),0) = 2022))))

Can you point out what I'm doing wrong? Thank you!

Genevieve P.

Hi @AHANSON

Instead of using @row inside of your MONTH and YEAR functions, you'll want to use @cell.

Try:

=COUNT(DISTINCT(COLLECT([Patient number]1:[Patient number]3, [Dosing date]1:[Dosing date]3, AND(IFERROR(MONTH(@cell),0) = 1, IFERROR(YEAR(@cell),0) = 2022))))

Cheers,

Genevieve

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.