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

Art Davida

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

Genevieve P.

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

Art Davida

correct answer for the above is 2

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

Genevieve P.

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

Art Davida

It sure did work. The function nesting wasn't entirely intuitive, but can't argue with results. Thank you very much

Genevieve P.

No problem! Thanks for letting me know.