英文:
How to use count(distinct Col#) in google sheet query
问题
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).
我有一个在Google表格中的查询，它工作得很好，但我需要count函数仅在单元格是唯一值时才计数。
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.
我有一个表格，在其中Col2可能会有重复的ID，例如ID01，ID02，ID01。我需要只计数ID01一次，即使表格中有多行它的情况。
Here is my google query:
这是我的Google查询：
QUERY(IMPORTRANGE("sheetURL/","sheetname!A1:Z50"), "SELECT count(Col2) where Col1='"&$D$2&"' and Col3 contains 'xyz'")
在SQL中，有distinct，我可以使用count(distinct Col2)，但在Google表格中不起作用。是否有任何方法可以在我的查询中仅计算Col2的唯一值？
请注意，我需要唯一的单元格（Col2），而不是唯一的行或记录。UNIQUE(QUERY(...))似乎对我来说不合适，因为唯一将返回唯一的行（而不是我想要的单元格）。
英文:
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).
答案1
得分: 1
使用 COUNTUNIQUEIFS() 函数。
=COUNTUNIQUEIFS(B3:B10,A3:A10,D3,C3:C10,"*xyz*")
如果要使用 QUERY() 函数，然后使用 UNIQUE() 和 COUNTA() 函数。
=COUNTA(UNIQUE(QUERY(A3:C10,"select B where A='" & D3 & "' and C contains 'xyz'",0)))
英文:
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)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论