or Explore Discussions

# I wish to count column A with a fixed value, with a unique date in Column B

05/28/21
Accepted

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

• 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

• correct answer for the above is 2

i.e. there are two rows of Smith with unique dates

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