Search code examples
google-sheetsgoogle-sheets-formulacount-unique

How to use Excel or Google Sheets COUNTUNIQUEIFS where the unique cell meets two conditions in same criteria column

I have a call log that I want to determine the number of answered and unanswered unique callers. However, if a caller has both statuses answered and answered, I want it to count as answered, since someone talked to that caller at some point. I prefer Google Sheets countuniqueifs, but Excel will work fine too.

enter image description here

I want the following results:

status # of unique callers
answered 3
unanswered 1 (excludes those with both answered and unanswered)

For a unique caller with both unanswered and answered status, we want to consider them as answered.

Solution

  • Those that are answered:

    =COUNTUNIQUE(FILTER(B2:B,COUNTIFS(C2:C,"answered",B2:B,B2:B)))

    Those that are unanswered:

    =COUNTUNIQUE(FILTER(B2:B,COUNTIFS(C2:C,"answered",B2:B,B2:B)=0))

    Or rather, just minus the two from eachother:

    =COUNTUNIQUE(B2:B)-<OutcomeOfFirstFormula>)