Fresh pair of Eyes required

Hi,
with the help of some excellent feedback from the User Group I have
created the following formula but I am not getting the expected
results

The formula works finre until I put the 2nd search for a particular
word..
eg
This works fine

=SUMPRODUCT((('Grid data'!B2:B4557=A2)*(ISERROR(SEARCH("shine*",'Grid
data'!D2:D4557)*((('Grid data'!F2:F4557="NS")+('Grid data'!
F2:F4557="C")+(TRIM('Grid data'!
F2:F4557)="")))))))

And counts correctly, ignoring anything with SHINE in column D and
only including, blanks, C and NS values.

The following formula I am not getting the right results. It appears
to be ignoring the search for "NON" in column E.

=SUMPRODUCT((('Grid data'!B2:B4557=A2)*(ISERROR(SEARCH("shine*",'Grid
data'!D2:D4557)+(SEARCH( "non*",'Grid data'!E2:E4557))*((('Grid data'!
F2:F4557="NS")+('Grid data'!F2:F4557="C")+(TRIM('Grid data'!
F2:F4557)="")))))))


Paul

T
Fri Mar 14 01:59:55 CDT 2008

Try it like this:

((ISERROR(SEARCH("shine",rng)))+(ISERROR(SEARCH("non",rng)))>0)

The wildcards weren't really doing anything.


--
Biff
Microsoft Excel MVP


<paultedder@hotmail.com> wrote in message
news:cd39ca56-2236-43de-af22-112a0e84ace6@u10g2000prn.googlegroups.com...
> Hi,
> with the help of some excellent feedback from the User Group I have
> created the following formula but I am not getting the expected
> results
>
> The formula works finre until I put the 2nd search for a particular
> word..
> eg
> This works fine
>
> =SUMPRODUCT((('Grid data'!B2:B4557=A2)*(ISERROR(SEARCH("shine*",'Grid
> data'!D2:D4557)*((('Grid data'!F2:F4557="NS")+('Grid data'!
> F2:F4557="C")+(TRIM('Grid data'!
> F2:F4557)="")))))))
>
> And counts correctly, ignoring anything with SHINE in column D and
> only including, blanks, C and NS values.
>
> The following formula I am not getting the right results. It appears
> to be ignoring the search for "NON" in column E.
>
> =SUMPRODUCT((('Grid data'!B2:B4557=A2)*(ISERROR(SEARCH("shine*",'Grid
> data'!D2:D4557)+(SEARCH( "non*",'Grid data'!E2:E4557))*((('Grid data'!
> F2:F4557="NS")+('Grid data'!F2:F4557="C")+(TRIM('Grid data'!
> F2:F4557)="")))))))
>
>
> Paul

Max
Fri Mar 14 03:34:23 CDT 2008

Paul,
Guess this earlier reply (in.misc) didn't quite take care of it ?
--------------
Maybe try changing this part of it in your expression to:
.. *(ISERROR(SEARCH("shine*",'grid
data'!D2:D4557))*(ISERROR(SEARCH("non*",'grid data'!E2:E4557))* ...
-------------
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Ron
Fri Mar 14 07:17:27 CDT 2008

Not *exactly* sure of what you're trying for,
but...maybe this:

This formula tests if:
((Col_B=A2 and Col_D does NOT contain "shine") OR Col_E contains "non")
AND
Col_F is any of "NS","C", or ""

(in sections for readability):
=SUMPRODUCT(--(
((('Grid data'!B2:B4557=A2)*ISERROR(SEARCH("SHINE",'Grid data'!D2:D4557))+
ISNUMBER(SEARCH("NON",'Grid data'!E2:E4557)))>0)*
(TRIM('Grid data'!F2:F4557)={"NS","C",""})>0))


Does that help?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


<paultedder@hotmail.com> wrote in message
news:cd39ca56-2236-43de-af22-112a0e84ace6@u10g2000prn.googlegroups.com...
> Hi,
> with the help of some excellent feedback from the User Group I have
> created the following formula but I am not getting the expected
> results
>
> The formula works finre until I put the 2nd search for a particular
> word..
> eg
> This works fine
>
> =SUMPRODUCT((('Grid data'!B2:B4557=A2)*(ISERROR(SEARCH("shine*",'Grid
> data'!D2:D4557)*((('Grid data'!F2:F4557="NS")+('Grid data'!
> F2:F4557="C")+(TRIM('Grid data'!
> F2:F4557)="")))))))
>
> And counts correctly, ignoring anything with SHINE in column D and
> only including, blanks, C and NS values.
>
> The following formula I am not getting the right results. It appears
> to be ignoring the search for "NON" in column E.
>
> =SUMPRODUCT((('Grid data'!B2:B4557=A2)*(ISERROR(SEARCH("shine*",'Grid
> data'!D2:D4557)+(SEARCH( "non*",'Grid data'!E2:E4557))*((('Grid data'!
> F2:F4557="NS")+('Grid data'!F2:F4557="C")+(TRIM('Grid data'!
> F2:F4557)="")))))))
>
>
> Paul

paultedder
Mon Mar 17 18:54:24 CDT 2008

On Mar 14, 10:17 pm, "Ron Coderre" <ronREMOVETHIScode...@bigfoot.com>
wrote:
> Not *exactly* sure of what you're trying for,
> but...maybe this:
>
> This formula tests if:
> ((Col_B=A2 and Col_D does NOT contain "shine") OR Col_E contains "non")
> AND
> Col_F is any of "NS","C", or ""
>
> (in sections for readability):
> =SUMPRODUCT(--(
> ((('Grid data'!B2:B4557=A2)*ISERROR(SEARCH("SHINE",'Grid data'!D2:D4557))+
> ISNUMBER(SEARCH("NON",'Grid data'!E2:E4557)))>0)*
> (TRIM('Grid data'!F2:F4557)={"NS","C",""})>0))
>
> Does that help?
>
> --------------------------
>
> Regards,
>
> Ron
> Microsoft MVP (Excel)
> (XL2003, Win XP)
>
> <paulted...@hotmail.com> wrote in message
>
> news:cd39ca56-2236-43de-af22-112a0e84ace6@u10g2000prn.googlegroups.com...
>
> > Hi,
> > with the help of some excellent feedback from the User Group I have
> > created the following formula but I am not getting the expected
> > results
>
> > The formula works finre until I put the 2nd search for a particular
> > word..
> > eg
> > This works fine
>
> > =SUMPRODUCT((('Grid data'!B2:B4557=A2)*(ISERROR(SEARCH("shine*",'Grid
> > data'!D2:D4557)*((('Grid data'!F2:F4557="NS")+('Grid data'!
> > F2:F4557="C")+(TRIM('Grid data'!
> > F2:F4557)="")))))))
>
> > And counts correctly, ignoring anything with SHINE in column D and
> > only including, blanks, C and NS values.
>
> > The following formula I am not getting the right results. It appears
> > to be ignoring the search for "NON" in column E.
>
> > =SUMPRODUCT((('Grid data'!B2:B4557=A2)*(ISERROR(SEARCH("shine*",'Grid
> > data'!D2:D4557)+(SEARCH( "non*",'Grid data'!E2:E4557))*((('Grid data'!
> > F2:F4557="NS")+('Grid data'!F2:F4557="C")+(TRIM('Grid data'!
> > F2:F4557)="")))))))
>
> > Paul

Thanks Gents, all good now Paul