2

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!

1 Answer 1

0

Use query() with an { array expression } to get the relevant data, another query() to get unique names in those interim results, and finally rows() to calculate the number of unique names, like this:

=arrayformula( 
  rows( 
    query( 
      transpose( 
        query( 
          { G2:G, D2:D & E2:E & F2:F }, 
          "select Col1, count(Col1) 
           where Col1 = 'Incident Referral' or Col1 = 'LIKE Submission' 
           group by Col1 pivot Col2", 0 
        ) 
      ), 
      "where Col2 >= 1 and Col3 is null", 0 
    ) 
  ) 
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.