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

Best Answer

  • Genevieve P.Genevieve P. admin
    Accepted 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

  • Genevieve P.Genevieve P. admin
    Accepted 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

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

Sign In or Register to comment.