0

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.

player0
  • 124,011
  • 12
  • 67
  • 124
Les
  • 330
  • 5
  • 15

2 Answers2

2

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>)
JvdV
  • 70,606
  • 8
  • 39
  • 70
-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

player0
  • 124,011
  • 12
  • 67
  • 124
  • This didn't quite work for me because not all the phone numbers are in numeric, so maybe the sorting didn't work. – Les Feb 15 '21 at 17:22