I wish to count column A with a fixed value, with a unique date in Column B
Column A Column B
Smith 5/27/21
Jones 5/27/21
Smith 5/29/21
Smith 5/27/21
I want to count column A with "Smith" and a unique date in Column B
Best Answer
-
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.
Try this:
=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.
Cheers,
Genevieve
Answers
-
correct answer for the above is 2
i.e. there are two rows of Smith with unique dates
-
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.
Try this:
=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.
Cheers,
Genevieve
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!