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

Art Davida
Art Davida ✭✭
in Formulas and Functions

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

· Share on FacebookShare on Twitter

Best Answer

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

    · Share on FacebookShare on Twitter

Answers

  • Art Davida
    Art Davida ✭✭

    correct answer for the above is 2

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

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    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

    · Share on FacebookShare on Twitter
  • Art Davida
    Art Davida ✭✭

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

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    No problem! Thanks for letting me know.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!