# Formula to count cells with specific characters using sumproduct & Subtotal

• Hello,

I have a document that has several filters attached to its headers. I want to create a formula that will basically count the VIEWABLE cells that meet the criteria.

here is the formula that i am having trouble with:
=SUMPRODUCT(SUBTOTAL(103,OFFSET(J29,ROW(J28:J368)-ROW(J29),0)),(J28:J368="*"&"WL"&"*")+0)

Basically what i want to do with this is count any cells that have the characters WL in it and display a total based on the viewing filter. Some of these cells have a WL2 or WL3 etc, i want to count as many WL's that are viewable.

I have "*"&"WL"&"*" in place for a wildcard for any WL's. (this works in a regular count if formula).

Any idea why i am not able to calculate them?

• Re: Formula to count cells with specific characters using sumproduct &amp; Subtotal

Wildcards don't work in this type of formula. Instead use FIND or SEARCH (depending on whether you want case sensitive match or not)....

E.g.

=SUMPRODUCT(SUBTOTAL(103,OFFSET(J29,ROW(J28:J368)-ROW(J29),0)),(ISNUMBER(FIND("WL",J28:J368)))+0)

Note: ISNUMBER checks if a numeric result is returned by FIND() which would mean a match was FOUND.... also FIND is a case-sensitive search (use SEARCH if you don't care about the case)

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Formula to count cells with specific characters using sumproduct &amp; Subtotal

Thank you! I was not aware the wild card wouldnt work in that type of formula. I plugged in your formula above but it is giving me a boolean return (true/ false). the returned results are all false, when i do in fact have WL1 WL2 etc in the range.

Any idea why?

• Re: Formula to count cells with specific characters using sumproduct &amp; Subtotal

Strange. Are you sure you copied it straight as is?

I just did a quick test, and got expected results.

Or do you mean you tried: =ISNUMBER(FIND("WL",J28:J368)) only? Alone, that formula won't tell you if you have a match or not...

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Formula to count cells with specific characters using sumproduct &amp; Subtotal

ok, i repasted the formula into my doc and it is working all of a sudden. Not sure what i did wrong before but it is working perfectly!!!
Thank you very much!

so the "FIND" or "SEARCH" function will search for specific characters within the range specified?

that seems like a more handy and celaner way to run these type of searches...

• Re: Formula to count cells with specific characters using sumproduct &amp; Subtotal

The wildcards would be more efficient though when they can be used (as in COUNTIF, SUMIF, AVERAGEIF, MATCH, VLOOKUP, etc)...

Where there is a will there are many ways. Finding one that works for you is the challenge!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!