sumproduct with wildcard for text?
 

I have the following formula that it won't work when I try to use a wildcard.
=SumProduct(($B$1:$BJ$5="high")*($F$1:$BN$5="requi rement*"))

The formula works if I use one of the actual categories (ie: requirement)
But I have several "requirement" type such as "requirement is waiting",
"requirment done".

Any help would be greatly appreciated,
Thank you so very muchhh : )

sumproduct with wildcard for text?
 

Hi,

Sumproduct doesn't accept wildcards, try this

=SUMPRODUCT(($B$1:$BJ$5="high")*(LEFT($F$1:$BN$5,1 1)="requirement"))

Mike

"nattie" wrote:

I have the following formula that it won't work when I try to use a wildcard.
=SumProduct(($B$1:$BJ$5="high")*($F$1:$BN$5="requi rement*"))

The formula works if I use one of the actual categories (ie: requirement)
But I have several "requirement" type such as "requirement is waiting",
"requirment done".

Any help would be greatly appreciated,
Thank you so very muchhh : )

sumproduct with wildcard for text?
 

Hi

Assuming "requirement" will always be the first word in the cell, this
should do it:

=SUMPRODUCT(($B$1:$BJ$5="high")*(LEFT($F$1:$BN$5,1 1)="requirement")

Regards,
Per

"nattie" skrev i meddelelsen
...
I have the following formula that it won't work when I try to use a
wildcard.
=SumProduct(($B$1:$BJ$5="high")*($F$1:$BN$5="requi rement*"))

The formula works if I use one of the actual categories (ie: requirement)
But I have several "requirement" type such as "requirement is waiting",
"requirment done".

Any help would be greatly appreciated,
Thank you so very muchhh : )

sumproduct with wildcard for text?
 

try:
=SumProduct(($B$1:$BJ$5="high")*(LEFT($F$1:$BN$5,1 1)="requirement"))

sumproduct with wildcard for text?
 

Allowing requirement anywhere in the string

=SUMPRODUCT(--($B$1:$BJ$5="high"),--(ISNUMBER(SEARCH("requirement",$F$1:$BN$5))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"nattie" wrote in message
...
I have the following formula that it won't work when I try to use a
wildcard.
=SumProduct(($B$1:$BJ$5="high")*($F$1:$BN$5="requi rement*"))

The formula works if I use one of the actual categories (ie: requirement)
But I have several "requirement" type such as "requirement is waiting",
"requirment done".

Any help would be greatly appreciated,
Thank you so very muchhh : )