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 & 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 & 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 & 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 & 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 & 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!

Register Yourself Login