CODEWITHSUNDEEP
google-sheetsfiltercountuniquevlookup
gonzalo2000
gonzalo2000

Reputation: 648

countuniqueifs one condition and not-another condition

In my sheet, Column G will either indicate "LIKE Submission" or "Incident Referral." Columns D-E-F list different names (one per row). I want to tally the number of unique names where the row contains "Incident Referral" for Column G and there are NO instances of "LIKE Submission."

This is a sample of my data: enter image description here

Thus far, I've attempted =COUNTUNIQUEIFS(Referrals!D:D, Referrals!E:E, Referrals!F:F, Referrals!G17:G, "Incident Referral", Referrals!G17:G, "<>*LIKE Submission*"), but the result doesn't check out what I expect to obtain from my test.

In the example on the image above, I would expect the result to evaluate to 1 (i.e. only "Stark, Tony" has an "Incident Referral" AND no "LIKE Submission"). All help will be well received!

Upvotes: 2

Views: 685

Answers (1)

player0
player0

Reputation: 1

try:

=INDEX(COUNTUNIQUE(IFNA(FILTER(D2:D&E2:E&F2:F, VLOOKUP(D2:D&E2:E&F2:F, 
 SORT({D2:D&E2:E&F2:F, G2:G}, 2, ), 2, )="incident referral"))))

enter image description here

or:

=INDEX(LAMBDA(x, COUNTUNIQUE(IFNA(FILTER(x, VLOOKUP(x, 
 SORT({x, G2:G}, 2, ), 2, )="incident referral"))))(D2:D&E2:E&F2:F))

enter image description here

Upvotes: 3

Related Questions