 |
 |
|
|
|
07-19-2008, 11:02 AM
|
|||
|
|||
|
SUMPRODUCT
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 
|
|
07-19-2008, 11:47 AM
|
|||
|
|||
|
Re: SUMPRODUCT
=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" <psowerby@murton-view.freeserve.co.uk> wrote in message news:9d3e3cde-2717-47d3-b613-64934f3de9bc@a1g2000hsb.googlegroups.com... >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
|
|
07-19-2008, 12:31 PM
|
|||
|
|||
|
Re: SUMPRODUCT
On 19 Jul, 12:47, "Bob Phillips" <bob....@somewhere.com> wrote:
> =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" <psowe...@murton-view.freeserve.co.uk> wrote in message > > news:9d3e3cde-2717-47d3-b613-64934f3de9bc@a1g2000hsb.googlegroups.com... > > > > >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- Hide quoted text - > > - 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*"
|
|
07-19-2008, 01:37 PM
|
|||
|
|||
|
Re: SUMPRODUCT
=SUMPRODUCT(--((COUNTIF(PlannedDowntime,$J$21:$J$28)>0)+(ISNUMBE R(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) "Pete" <psowerby@murton-view.freeserve.co.uk> wrote in message news:1179f1a9-7217-4848-8039-21a2f3f2b0ac@b1g2000hsg.googlegroups.com... > On 19 Jul, 12:47, "Bob Phillips" <bob....@somewhere.com> wrote: >> =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" <psowe...@murton-view.freeserve.co.uk> wrote in message >> >> news:9d3e3cde-2717-47d3-b613-64934f3de9bc@a1g2000hsb.googlegroups.com... >> >> >> >> >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- Hide quoted text - >> >> - 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*"
|
 |
|
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
