英文:
Google Sheet Query Count working, but other functions are not (e.g., sum, avg)
问题
Here's the translated part of the content you provided:
我想根据许多标准来计算/汇总/平均各种数据点。
至少有一个接触点落在两个日期之间是其中之一的标准。 有五个潜在的接触点。 每个潜在的接触点都有一个列。
我创建了这个可以用于计数的查询
'=QUERY(B10:G,"Select count (B)
where B<5 AND
(C >= date '"&TEXT(C9, "e-m-d")&"'AND C <= date '"&TEXT(D9, "e-m-d")&"')
Or (D >= date '"&TEXT(C9, "e-m-d")&"'AND D <= date '"&TEXT(D9, "e-m-d")&"')
Or (E >= date '"&TEXT(C9, "e-m-d")&"'AND E <= date '"&TEXT(D9, "e-m-d")&"')
Or (F >= date '"&TEXT(C9, "e-m-d")&"'AND F <= date '"&TEXT(D9, "e-m-d")&"')
Or (G >= date '"&TEXT(C9, "e-m-d")&"'AND G <= date '"&TEXT(D9, "e-m-d")&"')
label count(B) ''", 0)'
但是，当我使用其他函数时它不起作用
'=QUERY(B10:G,"Select Sum(B)
where B<5 AND
(C >= date '"&TEXT(C9, "e-m-d")&"'AND C <= date '"&TEXT(D9, "e-m-d")&"')
Or (D >= date '"&TEXT(C9, "e-m-d")&"'AND D <= date '"&TEXT(D9, "e-m-d")&"')
Or (E >= date '"&TEXT(C9, "e-m-d")&"'AND E <= date '"&TEXT(D9, "e-m-d")&"')
Or (F >= date '"&TEXT(C9, "e-m-d")&"'AND F <= date '"&TEXT(D9, "e-m-d")&"')
Or (G >= date '"&TEXT(C9, "e-m-d")&"'AND G <= date '"&TEXT(D9, "e-m-d")&"')
label count(B) ''", 0)'
这是数据的样子：链接到Google表格
Hope this helps!
英文:
I want to count / sum / average various data points based on many criteria.
One of the criteria at least one of the touchpoints falls between two set dates. There are five potential touchpoints. Each potential touch point has a column.
I created this query that works with count
`=QUERY(B10:G,"Select count (B)
where B<5 AND
(C >= date '"&TEXT(C9, "e-m-d")&"'AND C <= date '"&TEXT(D9, "e-m-d")&"')
Or (D >= date '"&TEXT(C9, "e-m-d")&"'AND D <= date '"&TEXT(D9, "e-m-d")&"')
Or (E >= date '"&TEXT(C9, "e-m-d")&"'AND E <= date '"&TEXT(D9, "e-m-d")&"')
Or (F >= date '"&TEXT(C9, "e-m-d")&"'AND F <= date '"&TEXT(D9, "e-m-d")&"')
Or (G >= date '"&TEXT(C9, "e-m-d")&"'AND G <= date '"&TEXT(D9, "e-m-d")&"')
label count(B) ''", 0)`
However, when I use other funchtions it does not work
`=QUERY(B10:G,"Select Sum(B)
where B<5 AND
(C >= date '"&TEXT(C9, "e-m-d")&"'AND C <= date '"&TEXT(D9, "e-m-d")&"')
Or (D >= date '"&TEXT(C9, "e-m-d")&"'AND D <= date '"&TEXT(D9, "e-m-d")&"')
Or (E >= date '"&TEXT(C9, "e-m-d")&"'AND E <= date '"&TEXT(D9, "e-m-d")&"')
Or (F >= date '"&TEXT(C9, "e-m-d")&"'AND F <= date '"&TEXT(D9, "e-m-d")&"')
Or (G >= date '"&TEXT(C9, "e-m-d")&"'AND G <= date '"&TEXT(D9, "e-m-d")&"')
label count(B) ''", 0)`
This is what the data looks like: https://docs.google.com/spreadsheets/d/1BiWsm6uPncPsdM7WiVDdRSbkUcbulNLhITqbt4rqXB8/edit?usp=sharing
Start End
1/1/23 1/31/23
| Number | Screening | F1 | F2 | F3 | F4 |
|---|---|---|---|---|---|
| 1 | 1/1/23 | 1/4/23 | 1/6/23 | 1/8/23 | 1/10/23 |
| 2 | 12/03/22 | 1/4/23 | 02/05/23 | 3/9/23 | 04/10/23 |
| 3 | 1/7/23 | 1/8/23 | 1/9/23 | 1/10/23 | 1/11/23 |
| 4 | 09/22/22 | 10/28/22 | 12/03/22 | 1/10/23 | 02/17/23 |
I searched this site and tried other formulas
答案1
得分: 1
以下是翻译好的内容：
检查标签语句
在您的第二个公式中，您想要获取列B中值的总和，请将label count(B)更改为label sum(B)。因此，该公式应如下所示：
对于总和：
=QUERY(B10:G,"选择 Sum(B)
其中 B<5 并且
((C >= 日期 '&"TEXT(C9, "e-m-d")&"' 并且 C <= 日期 '&"TEXT(D9, "e-m-d")&"')
或 (D >= 日期 '&"TEXT(C9, "e-m-d")&"' 并且 D <= 日期 '&"TEXT(D9, "e-m-d")&"')
或 (E >= 日期 '&"TEXT(C9, "e-m-d")&"' 并且 E <= 日期 '&"TEXT(D9, "e-m-d")&"')
或 (F >= 日期 '&"TEXT(C9, "e-m-d")&"' 并且 F <= 日期 '&"TEXT(D9, "e-m-d")&"')
或 (G >= 日期 '&"TEXT(C9, "e-m-d")&"' 并且 G <= 日期 '&"TEXT(D9, "e-m-d")&"'))
label Sum(B) ''", 0)
对于平均值：
=QUERY(B10:G,"选择 Avg(B)
其中 B<5 并且
((C >= 日期 '&"TEXT(C9, "e-m-d")&"' 并且 C <= 日期 '&"TEXT(D9, "e-m-d")&"')
或 (D >= 日期 '&"TEXT(C9, "e-m-d")&"' 并且 D <= 日期 '&"TEXT(D9, "e-m-d")&"')
或 (E >= 日期 '&"TEXT(C9, "e-m-d")&"' 并且 E <= 日期 '&"TEXT(D9, "e-m-d")&"')
或 (F >= 日期 '&"TEXT(C9, "e-m-d")&"' 并且 F <= 日期 '&"TEXT(D9, "e-m-d")&"')
或 (G >= 日期 '&"TEXT(C9, "e-m-d")&"' 并且 G <= 日期 '&"TEXT(D9, "e-m-d")&"'))
label Avg(B) ''", 0)
相同的概念适用于其他函数（如最小值、最大值等）。在使用这些公式时，请始终注意查看标签。
英文:
Check the Label Statement
In your second formula where you wanted to get the sum of values in column B, change the label count(B) to label sum(B). Thus, the formula should look like:
For Sum:
=QUERY(B10:G,"Select Sum(B)
where B<5 AND
((C >= date '"&TEXT(C9, "e-m-d")&"'AND C <= date '"&TEXT(D9, "e-m-d")&"')
Or (D >= date '"&TEXT(C9, "e-m-d")&"'AND D <= date '"&TEXT(D9, "e-m-d")&"')
Or (E >= date '"&TEXT(C9, "e-m-d")&"'AND E <= date '"&TEXT(D9, "e-m-d")&"')
Or (F >= date '"&TEXT(C9, "e-m-d")&"'AND F <= date '"&TEXT(D9, "e-m-d")&"')
Or (G >= date '"&TEXT(C9, "e-m-d")&"'AND G <= date '"&TEXT(D9, "e-m-d")&"'))
label Sum(B) ''", 0)
For Avg:
=QUERY(B10:G,"Select Avg(B)
where B<5 AND
((C >= date '"&TEXT(C9, "e-m-d")&"'AND C <= date '"&TEXT(D9, "e-m-d")&"')
Or (D >= date '"&TEXT(C9, "e-m-d")&"'AND D <= date '"&TEXT(D9, "e-m-d")&"')
Or (E >= date '"&TEXT(C9, "e-m-d")&"'AND E <= date '"&TEXT(D9, "e-m-d")&"')
Or (F >= date '"&TEXT(C9, "e-m-d")&"'AND F <= date '"&TEXT(D9, "e-m-d")&"')
Or (G >= date '"&TEXT(C9, "e-m-d")&"'AND G <= date '"&TEXT(D9, "e-m-d")&"'))
label Avg(B) ''", 0)
The same concept is applicable to other functions (i.e. min, max, etc.) Always take note that when using these formulas, you should also take a look at the label.
答案2
得分: 1
Alternate formula:（如果您不希望在查询函数中单独检查每一列的日期条件，请使用以下公式）
=SUM(IFNA(FILTER(B12:B, B12:B < 5, BYROW(C12:G, LAMBDA(Σ, IF(COUNTA(Σ) = 0, , SUM(INDEX(IF((Σ >= C9) * (Σ <= D9), 1,)))))) <> 0)))
- 您只需在公式的起始部分用
average或counta替换sum部分以进行不同的汇总计算。
英文:
Alternate formula: (incase if you do not wish to have a date-condition check for each column separately within query function)
=sum(ifna(filter(B12:B,
B12:B<5,
byrow(C12:G,lambda(Σ,if(counta(Σ)=0,,sum(index(if((Σ>=C9)*(Σ<=D9),1,))))))<>0)))
- you may only replace the
sumpart used at the starting point of formula withaverageorcountafor diff. aggregate calculations.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论