Google Spreadsheet，过滤器不允许使用通配符?如何用通配符计算多列? [英] Google Spreadsheet, filter doesn't allow wildcards? How to countif multiple columns with wildcards?

问题描述

当我这样做时:

B         C
223 herp
223 herp
3   herp
223 derp
223 herp,derp

=countif(C:C, "*herp*")

我正确得到4.

当我这样做

=count(filter(B:B, B:B=223, C:C="*herp*"))

我错误地得到0.删除"*"通配符时，我得到2，这更好，但没有得到herp，derp.

I incorrectly get 0. When I remove the "*" wildcard characters, I get 2, which is better, but doesn't get herp,derp.

过滤器不支持通配符吗?如果是这样，那么仅当其中两个列满足两个具有通配符的不同条件时，才如何计数一行?

Does filter not support wildcard characters? If so, how can I count a row only if two of it's columns meet two different criteria which have wildcards?

推荐答案

FILTER不支持通配符，不可以.您需要执行以下操作:

FILTER doesn't support wildcards, no. You need to do something like:

=COUNT(FILTER(B:B,B:B=223,SEARCH("herp",C:C)))

或

=COUNT(FILTER(B:B,B:B=223,REGEXMATCH(C:C,"herp")))

或者，在新版电子表格中，支持COUNTIFS:

Alternatively, in the new version of Sheets, COUNTIFS is supported:

=COUNTIFS(B:B,223,C:C,"*herp*")

这篇关于Google Spreadsheet，过滤器不允许使用通配符?如何用通配符计算多列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！