Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Google Spreadsheet, filter doesn't allow wildcards? How to countif multiple columns with wildcards?

When I do:

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

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

I correctly get 4.

When I do

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

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?

like image 203
J.R. Avatar asked Jan 24 '14 21:01

J.R.


Video Answer


1 Answers

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

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

or

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

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

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

like image 59
AdamL Avatar answered Sep 27 '22 17:09

AdamL