countuniqueifs one condition and not-another condition-CodePudding

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:

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!

CodePudding user response：

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

or:

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