Count Distinct Collect with Month and Year returning 1

hayleed
hayleed ✭✭
in Formulas and Functions

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)))

Screenshot 2024-05-30 083856.png

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.

· Share on FacebookShare on Twitter

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/30/24 Answer ✓

    @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.

    image.png

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

    · Share on FacebookShare on Twitter

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/30/24 Answer ✓

    @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.

    image.png

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

    · Share on FacebookShare on Twitter
  • hayleed
    hayleed ✭✭

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

    · Share on FacebookShare on Twitter
  • Mark.poole
    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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!