Counting based on values from two columns while removing repeated entries

Options

Hi,

I'm trying to count the number of entries based on two columns. In column A is the year, in column B are study numbers (eg AB-1, TH-2, etc) but some study numbers are repeated (eg TH-2, TH-2). I'm trying to count the number of unique entries in column B that contain TH that occur in a given year. I've tried this: =COUNTIFS([Column A]:[Column A], "2021", DISTINCT([Column B]:[Column B]), CONTAINS("TH", @cell)) but it's not working. I know I can do this by creating a helper column to only include rows in given year but ideally I'd like a single function to do this.

Thanks,

RLum

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @RLum

    Try this

    =COUNT(DISTINCT(COLLECT([Column B]:[Column B], [Column A]:[Column A], 2021, [Column B]:[Column B], CONTAINS("TH", @cell))))

    Note I purposefully did not include quotes around 2021. 2021 is a number, not text.

    If these are not your actual column names, be sure to edit this formula to make the column names match those in your sheet.

    Will this work for you

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @RLum

    Try this

    =COUNT(DISTINCT(COLLECT([Column B]:[Column B], [Column A]:[Column A], 2021, [Column B]:[Column B], CONTAINS("TH", @cell))))

    Note I purposefully did not include quotes around 2021. 2021 is a number, not text.

    If these are not your actual column names, be sure to edit this formula to make the column names match those in your sheet.

    Will this work for you

    Kelly

  • RLum
    RLum ✭✭
    Options

    Thank you so much, this worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!