CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulacount-unique
Les
Les

Reputation: 330

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.

Upvotes: 0

Views: 5955

Answers (2)

player0
player0

Reputation: 1

use for answered:

=COUNTA(IFNA(QUERY(SORTN(SORT(B2:C, 2, 1), 9^9, 2, 1, 0), 
 "select Col1 where Col2 = 'answered'")))

and for unanswered:

=COUNTA(IFNA(QUERY(SORTN(SORT(B2:C, 2, 1), 9^9, 2, 1, 0), 
 "select Col1 where Col2 = 'unanswered'")))

enter image description here

Upvotes: -1

JvdV
JvdV

Reputation: 75900

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>)

Upvotes: 2

Related Questions