# Count Distinct Rows Using AND and OR

Options
✭✭✭

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.

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• Employee
Options

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