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

Try it like this:

((ISERROR(SEARCH("shine",rng)))+(ISERROR(SEARCH("n on",rng)))0)

The wildcards weren't really doing anything.


--
Biff
Microsoft Excel MVP


wrote in message
...
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

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
---

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)


wrote in message
...
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

On Mar 14, 10:17 pm, "Ron Coderre"
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)

wrote in message

...

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