scratch that...brain cramp
"duane" wrote:
yup...maybe this?
=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(LEN(Original!C2:C9101)=4)*(Origin al!H2:H9101))
"Biff" wrote:
Yeah, you're right!
But what if some cells only contain the the word pipe and don't have a
trailing space as in:
.........LEFT(Original!C2:C9101,5)="pipe ")
Will work if:
pipe fitter
Won't work if:
pipe
Biff
"duane" wrote in message
...
does he not need this to eliminate the cell starting with with pipet?
=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,5)="pipe
")*(Original!H2:H9101))
"Biff" wrote:
Hi!
Try this:
=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(Original!H2:H9101))
Biff
"Mosqui" wrote in message
...
Thanks very much for all the answers, I went through the posts and
learnt
a
lot, but I could find exactly what I need.
The problem I've got is that one of my criterias is part of the cell.
So, to be clear; I need all the cells which start with the word "pipe".
pipe 100 w
sp-pipe
pipet 333
From these three I just need to get the first one.
=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*NOT(ISERROR((SEARCH ("pipe",Original!C2:C9101))))*(Original!H2:H910 1))
This is what I'm using at the moment.
Thanks in advance, and again congratulations to all the people who
colaborate with these answers.
Martin
|