Count Distinct Collect with Month and Year returning 1

Options

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.

Best Answer

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

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

    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.

Answers

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

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

    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.

  • hayleed
    hayleed ✭✭
    Options

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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!