SUMPRODUCT

[Pete]

I use the following formula to calculate if any of the Cells in
J21:J28 include categories from the PlannedDowntime named range.

SUMPRODUCT(--(COUNTIF(PlannedDowntime,$J$21:$J$28)>0),--($A$21:$A$28))

Is there anyway I can do a search from within J21:J28 as above for
part of a string. I would like to look for the word "Trial" in J21:J28
but the Cell may contain "Trial Paper Break" or "Trial Dryer Jam"


Thanks

Peter

 

[Bob Phillips]

=SUMIF($J$21:$J$28,"Trial*",$A$21:$A$28)

--
HTH

Bob

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

 

[Pete]

=SUMIF($J$21:$J$28,"Trial*",$A$21:$A$28)

--
HTH

Bob

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






- Show quoted text -

Bob,

Can this be part of the SUMPRODUCT Formula? as I need to look for
values contained within "PlannedDowntime" or anything containing
"Trial*"

 

[Bob Phillips]

=SUMPRODUCT(--((COUNTIF(PlannedDowntime,$J$21:$J$28)>0)+(ISNUMBER(SEARCH("trial",$J$21:$J$28)))),--($A$21:$A$28))

--
HTH

Bob

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