1

I have a Google sheet query, which works fine, but I need the count function to count the cell only if it is distinct (unique value). I have a sheet, where Col2 can have repeated IDs, e.g. ID01, ID02, ID01. I need to count ID01 only once, even if the sheet has multiple rows for it. Here is my google query:

QUERY(IMPORTRANGE("sheetURL/","sheetname!A1:Z50"), "SELECT count(Col2) where Col1='"&$D$2&"' and Col3 contains 'xyz'")

In SQL, there is distinct where I can use count(distinct Col2) but it does not work in Google sheets.Is there any way where I can count only distinct values of Col2 in my query?

Note that I need distinct cell (Col2), not distinct row or record. UNIQUE(QUERY(...)) does not seem the right thing for me, as UNIQUE will return unique rows (not cells as I want).

Improve this question
1

1 Answer 1

Reset to default
1

Use COUNTUNIQUEIFS() function.

=COUNTUNIQUEIFS(B3:B10,A3:A10,D3,C3:C10,"*xyz*")

If you want to use QUERY() function then use UNIQUE() and COUNTA() function.

=COUNTA(UNIQUE(QUERY(A3:C10,"select B where A='" & D3 & "' and C contains 'xyz'",0)))

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

If the count result is 0, this equation returns 1? any idea why?
@None Because, COUNTA() counts everything, even error values. To avoid that false positive use ROWS() function instead. Try- =ROWS(UNIQUE(QUERY(A3:C10,"select B where A='" & D3 & "' and C contains 'xyz'",0)))

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.