Count Distinct Rows Using AND and OR

Hi geniuses. I need help with a formula. I have a sheet that looks like this:

I need to get a count of distinct rows that are Type = Task AND have (Past Due = 1 OR Jeopardy Status = Red). I assume I'll need to use COUNTIFS with OR and DISTINCT, but I'm not sure how to structure it. Thoughts? Thank you very much.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Lori Drum

    Since you have some rows that could potentially have both a Flag and a Red ball but you only want to count that row once, the way I would do this is to create three separate formulas:

    First, count the Red status rows. Then, add this to the count of the flag rows. Finally, minus the count of the rows that have both from the total. This will give you the Distinct count!

    Try this:

    =COUNTIF([Past Due]:[Past Due], 1) + COUNTIF([Jeopardy Status]:[Jeopardy Status], "Red") - COUNTIFS([Past Due]:[Past Due], 1, [Jeopardy Status]:[Jeopardy Status], "Red")

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Lori Drum

    Since you have some rows that could potentially have both a Flag and a Red ball but you only want to count that row once, the way I would do this is to create three separate formulas:

    First, count the Red status rows. Then, add this to the count of the flag rows. Finally, minus the count of the rows that have both from the total. This will give you the Distinct count!

    Try this:

    =COUNTIF([Past Due]:[Past Due], 1) + COUNTIF([Jeopardy Status]:[Jeopardy Status], "Red") - COUNTIFS([Past Due]:[Past Due], 1, [Jeopardy Status]:[Jeopardy Status], "Red")

    Cheers,

    Genevieve

  • Lori Drum
    Lori Drum ✭✭✭

    Thank you, Genevieve! That will work just fine. I appreciate the help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!