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

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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!