SUMPRODUCT condition ="*text*" not working

[nfellen]

There are two conditions for this formula - one determines how many are in the parents in the members category (in column T), and the second one determines of the parents how many are female in the genders column (in column K).

The formula I'm using:

=SUMPRODUCT((T11:T40="parent")*(K11:K40="F"))

Now if in the members category, the person indicates they are a student and a parent, the text will say "parent,student" and so I edited the formula to be this to take into consideration additional text in that cell:

=SUMPRODUCT((T11:T40="*parent*")*(K11:K40="F"))

Which results in the count not working!

I don't know why and I can't understand it. Help? Thank you!!

[tigeravatar]

nfellen,

Welcome to the forum!
Text= can find exact matches (ignoring case sensivity), but not partial matches. To find partial matches you'll need to use Isnumber(search()) like so:

=SUMPRODUCT(ISNUMBER(SEARCH("parent",T11:T40))*(K11:K40="F"))

[nfellen]

Thanks - that explanation helps, but it's still not working for me. I tried playing with it - quotes, no quotes, asterisks, no asterisks - not working. I did change the range information to $T:$T and $K:$K but that shouldn't affect things negatively.

[MarvinP]

Hi nfellen and welcome to the forum.

Another way to make True a number is using a Unary (I think that is what it's called). The idea is you put a negative sign in front of stuff that is true so it turns to negative. You put two negative signes and it turns it positive. See if this formula works for you in the attached..

=SUMPRODUCT(--(K2:K25="F")*--(T2:T25="Parent"))