0

I have been looking every where on the net and everyone seems to simply suggest an alternative like SUMPRODUCT, however, I want to use wildcards. Wildcards work fine in COUNTIF but not in other alternatives I have seen.

How can I search every nth cell in a column and return a count of the number of times an exact substring is found (i.e. COUNTIF(range, string), where range is every nth cell and string could be "*somestring", "some string*", "*somestring*" or "some?string")

JacobD
  • 627
  • 6
  • 15

1 Answers1

2

Yes, SUMPRODUCT is an option - say you want every 5th cell starting at A2 (i.e. A2, A7, A12 etc.) you can use this formula to count instances of "text" in those cells (possibly within other text)

=SUMPRODUCT((MOD(ROW(A2:A1000)-ROW(A2),5)=0)+0,ISNUMBER(SEARCH("text",A2:A1000))+0)

change the 5 to whatever n you want - it always starts with the first cell in the range if you use this syntax.

The ISNUMBER(SEARCH part gives you the equivalent of a "wildcard" search (you can't use actual wildcards here)

update

If you want the equivalent of "text*" then use LEFT function, e.g.

=SUMPRODUCT((MOD(ROW(A2:A1000)-ROW(A2),5)=0)+0,(LEFT(A2:A1000,LEN("text"))="text")+0)

and RIGHT can be used for the equivalent of "*text".

IF you want the equivalent of "t?xt" that would be trickier. The SEARCH function in the first formula will allow you to search for "t?xt" but that would be amongst other text so if you want the entire contents to be "t?xt" you could add another check, e.g.

=SUMPRODUCT((MOD(ROW(A2:A1000)-ROW(A2),5)=0)+0,ISNUMBER(SEARCH("t?xt",A2:A1000))+0,(LEN(A2:A1000)=4)+0)

That will only count cells which contain "text", "toxt", "t5xt" etc. but not "text2"

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • I stated that I wanted to use wildcards in my question. I have not seen a solution which does this. What if I want to search "*text" or "text 2*" or "t?xt" ...? – JacobD Apr 15 '14 at 23:50
  • @JacobD Those are three different strings. To OR, you would search for each one separately and add them together. SEARCH recognizes ? and * as wildcards, so the results for your third string should be as expected. – Ron Rosenfeld Apr 16 '14 at 01:19
  • @RonRosenfeld I meant I wanted to search for them on separate occasions. The answer says: "The ISNUMBER(SEARCH part gives you the equivalent of a "wildcard" search (you can't use actual wildcards here)" And then you say: "SEARCH recognizes ? and * as wildcards" So can I use wildcards? When I implement this solution it doesn't seem that wildcards work correctly as the answer states. – JacobD Apr 16 '14 at 05:16
  • @JacobD It works as described. Read Excel HELP for the SEARCH function. Then show us exactly what you used for a formula; exactly what your data is; your actual results; and your expected results. – Ron Rosenfeld Apr 16 '14 at 10:30
  • As written the formula will count any cell in A2,A7 etc. that **contains** "text", so it's the equivalent of using "*text*" in COUNTIF. For "text*" or "*text" you can use `LEFT` or `RIGHT` functions in place of `ISNUMBER(SEARCH`, I edited my answer. – barry houdini Apr 16 '14 at 11:00
  • @barryhoudini Thankyou for the update, `LEFT`, `RIGHT` and `LEN` help fix my problem. It still feels like a complicated alternative to wildcards. – JacobD Apr 16 '14 at 14:53
  • Yes, I don't deny it's complicated -if you wanted to retain the simpler COUNTIFS wildcard syntax then I think you can only do that if you have another column which indicates which rows are included, e.g. if B2, B7, B12 all contain some common value - do you have that? – barry houdini Apr 16 '14 at 14:57