Count Distinct Rows Using AND and OR

Lori Drum

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.

Genevieve P.

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

Genevieve P.

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

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