Column A Column B
I want to count column A with "Smith" and a unique date in Column B
Hi @Art Davida
You can use a DISTINCT(COLLECT formula for this! The COLLECT function finds all the rows with a specific person, then the DISTINCT checks through the dates for unique dates. Then wrap this in a COUNT function and you should receive the number you're looking for.
=COUNT(DISTINCT(COLLECT([Column B]:[Column B], [Column A]:[Column A], [Column A]@row)))
You would apply this to an entire column. However, if you're looking to have this just once, like in a Sheet Summary formula, then you can identify the criteria as data "in quotes" instead of saying [Column A]@row.
=COUNT(DISTINCT(COLLECT([Column B]:[Column B], [Column A]:[Column A], "Smith")))
Let me know if this works for you or if you'd like to see screen capture examples.
correct answer for the above is 2
i.e. there are two rows of Smith with unique dates
It sure did work. The function nesting wasn't entirely intuitive, but can't argue with results. Thank you very much
No problem! Thanks for letting me know.