Count Distinct Collect with Month and Year returning 1

I have been trying to figure out why this formula isn't working for me, it works perfectly if it is just the year but I need to count distinct individuals based on month and year in a sheet that has data spanning multiple years and months. This formula returns a 1 which I know means there's an error in it somewhere and I've just been staring at it so long I don't see it. I've got the just year one in a sheet summary at the moment and am trying to use this one in a sheet summary too. I have a separate metric sheet where I'll eventually place it with the external sheet links for columns.

=COUNT(DISTINCT(COLLECT([Student Name]:[Student Name], [Date Removed]:[Date Removed], AND(IFERROR(YEAR(@cell), 0) = 2024), IFERROR(MONTH(@cell), 0) = 5)))

I've checked all the data to make sure the dates are all dates and the names it's trying to count are all letters with no numbers.

Find more posts tagged with

Accepted answers

Mark.poole

@hayleed

Your formula looks correct. How ever when I copied it over to my dummy sheet to test it. It did indeed return a 1. I deleted the month portion and retyped it. It started returning an 11. This is the second time I have seen this issue.

=COUNT(DISTINCT(COLLECT([Student Name]:[Student Name], [Date Removed]:[Date Removed], AND(IFERROR(YEAR(@cell), 0) = 2024, IFERROR(MONTH(@cell), 0) = 5))))

I am not so sure what's different as to why it isn't working until retyped, however this formula should be able to be copy and pasted to work for you.

All comments

Mark.poole

@hayleed

=COUNT(DISTINCT(COLLECT([Student Name]:[Student Name], [Date Removed]:[Date Removed], AND(IFERROR(YEAR(@cell), 0) = 2024, IFERROR(MONTH(@cell), 0) = 5))))

I am not so sure what's different as to why it isn't working until retyped, however this formula should be able to be copy and pasted to work for you.

hayleed

Thank you! That works perfect, looks like I was missing a parentheses somewhere.

Mark.poole

https://community.smartsheet.com/discussion/comment/438364#Comment_438364

Yes Looking back. You was missing one Parentheses on the end of the formula.

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.