Fresh pair of Eyes required

[paultedder]

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'!D24557)*((('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'!D24557)+(SEARCH( "non*",'Grid data'!E2:E4557))*((('Grid data'!
F2:F4557="NS")+('Grid data'!F2:F4557="C")+(TRIM('Grid data'!
F2:F4557)="")))))))


Paul

 

[T. Valko]

Try it like this:

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

The wildcards weren't really doing anything.

 

[Max]

Paul,
Guess this earlier reply (in.misc) didn't quite take care of it ?

 

[Ron Coderre]

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'!D24557))+
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]

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'!D24557))+
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)

Thanks Gents, all good now Paul