Your term ...*((('Grid data'!F2:F4557="NS")*('Grid
data'!F2:F4557="C")*(TRIM('Grid data'!F2:F4557)="")))
is multiplying 1 or 0 for true or false for column F being "NS" by another
1 or 0 for true or false for column F being "C", and then by another 1 or 0
for true or false for column F being "".
The mutliplication gives a boolean AND function, and it returns a 1 only if
all 3 terms are 1. If any of the 3 terms are zero the result of the
multiplication is zero (or FALSE). If column F is "NS" it can't also be "C"
or "", so you answer will always be zero, as you've found. If you want OR,
rather than AND, then you need *addition*, rather than *multiplication*, as
in your earlier formula.
--
David Biddulph

wrote in message
...
On Mar 9, 3:40 pm, "Max" wrote:

Max
Firstly sorry to keep bother you but you've started something in my
brain. After getting those results I now want to also look up a 3rd
value in the same column, the following is my formula but it is not
giving me the correct results, eg I am getting zero and I can
calculate that the answer is 7

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)="")))))

I am not getting an error so assume my parenthesis etc is ok ?

Paul Glad you got it working fine, Paul. You're welcome.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---
<paultedder wrote

Max
you are a champion. I used the following and it works a treat
=SUMPRODUCT(('Grid data'!B9:B4563=A9)*(ISERROR(SEARCH("bob*",'Grid
data'!D9:D4563))*(('Grid data'!F9:F4563="aa")+(TRIM('Grid data'!
F9:F4563)=""))))

Thanks very much Max
Paul