how to use OR logic in COUNTUNIQUEIFS function?

Question

How do I combine the following in google sheets?

= COUNTUNIQUEIFS(A2:$A, B2:$B, ">0", C2:$C, "apple") + COUNTUNIQUEIFS(A2:$A, B2:$B, ">0", C2:$C, "banana") + COUNTUNIQUEIFS(A2:$A, B2:$B, ">0", C2:$C, "orange") + COUNTUNIQUEIFS(A2:$A, B2:$B, ">0", C2:$C, "lemon") 

I've tried a lot but in vain.

Something like this

= COUNTUNIQUEIFS(A2:$A, B2:$B, ">0", C2:$C, OR{"apple","banana","orange","lemon"})

Answer 1

The conditional aggregate functions operate with AND logic, if you want to do a count based on OR logic you have to use a function like FILTER to do the filtering and then a regular COUNTUNIQUE:

=COUNTUNIQUE(FILTER(A2:A,B2:B>0,(C2:C="apple")+(C2:C="banana")+(C2:C="orange")+(C2:C="lemon")))

Or with REGEXMATCH:

=COUNTUNIQUE(FILTER(A2:A,B2:B>0,REGEXMATCH(C2:C,"(?i)apple|banana|orange|lemon")))

You can also filter with QUERY:

=COUNTUNIQUE(QUERY(A2:C,"select A where B>0 and lower(C) matches '.*(apple|banana|orange|lemon).*'"))