英文:
Google Sheets - Query Count over multiple columns
问题
我正在尝试在Google Sheets中编写一个查询公式,以统计字符串的任何出现,如果该行的另一个单元格以特定字符串开头。
表格有数百行,但看起来像这样:
现在我想要统计列D
和J
中出现名称Alex的任何情况,如果相应的类别列(A
和G
)以WEB
开头。
所以我写了以下公式:
=QUERY({A2:L481},"SELECT COUNT(Col1) WHERE ((LOWER(Col4) LIKE '%alex%' AND LOWER(Col1) LIKE '%web%') OR (LOWER(Col10) LIKE '%alex%' AND LOWER(Col7) LIKE '%web%') OR (LOWER(Col16) LIKE '%alex%' AND LOWER(Col13) LIKE '%web%'))",0)
不幸的是,总数不正确。
也许是因为我有点过度疲劳,所以我看不清楚了。但我就是找不到错误。
提前感谢任何帮助。
问候
Lars
英文:
I am trying to write a query formula in Google Sheets to count any appearances of a string, if another cell in that row begins with a specific string.
The table has hundreds of rows, but looks like this:
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
WEB – General | 1080 | 1080 | Michael | JPEG | 2 Mb | Display - Business | 1920 | 1080 | Alex | PNG | 2 | ||||||
WEB – General | 560 | 660 | Martin | JPEG | 50 | Web - Mixed | 1920 | 1080 | Alex | PNG | 2 | ||||||
Display – Mixed | 1700 | 300 | Matteo | JPEG | Display - General | 1920 | 1080 | Bill | PNG | 2 | |||||||
WEB – Business | 800 | 500 | Michael | JPEG | WEB - General | 800 | 500 | Alex | JPEG | 250kb | |||||||
WEB - General | 1440 | 480 | Matteo | PNG | Display - Mixed | 700 | 600 | MATTEO | PNG | ||||||||
WEB – Mixed | 297 | 210 | Alex | JPEG | 4000 | WEB - General | 297 | 210 | Maluma | JPEG | 4000 |
Here Columns A - F and G - L belong together in each case.
Now I want to count any appearance of the name Alex in column D
& J
, if the corresponding category column (A
& G
) begins with WEB
.
So I wrote the following formula:
=QUERY({A2:L481};"
SELECT COUNT(Col1) WHERE ((LOWER(Col4) LIKE '%alex%' AND LOWER(Col1) LIKE '%web%')
OR (LOWER(Col10) LIKE '%alex%' AND LOWER(Col7) LIKE '%web%')
OR (LOWER(Col16) LIKE '%alex%' AND LOWER(Col13) LIKE '%web%'))";0)
Unfortunately the sum is not correct.
Maybe I just can't see through it any more because I'm a bit overtired. But I just can't find the mistake.
Thanks in advance for any help.
Regards
Lars
答案1
得分: 2
=SUM(INDEX(IF((LEFT(A2:O, 3)="web")*(D2:R="alex"), 1, )))
如果需要部分匹配 Alex
以匹配 Alex Dunphy
和 Alexander
:
=SUM(INDEX(IF((LEFT(A2:O, 3)="web")*(IFERROR(SEARCH("alex", D2:R))), 1, )))
如果需要部分匹配 Alex
以匹配 Alex Dunphy
但不匹配 Alexander
:
=SUM(INDEX(IF((LEFT(A2:O, 3)="web")*(IFERROR(REGEXMATCH(D2:R, "(?i)\balex\b"))), 1, )))
英文:
You may try:
=sum(index(if((left(A2:O;3)="web")*(D2:R="alex");1;)))
IF Alex
needs to be also partial-matched to say Alex Dunphy
and Alexander
as well:
=sum(index(if((left(A2:O,3)="web")*(iferror(search("alex",D2:R))),1,)))
IF Alex
needs to be also partial-matched to say Alex Dunphy
and NOT Alexander
:
=sum(index(if((left(A2:O,3)="web")*(iferror(regexmatch(D2:R,"(?i)\balex\b"))),1,)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论