sumproduct wildcats

M

Mosqui

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:H9101))

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
 
B

Biff

Hi!

Try this:

=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C9101,4)="pipe")*(Original!H2:H9101))

Biff
 
D

duane

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:C9101,5)="pipe ")*(Original!H2:H9101))
 
B

Biff

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
 
D

duane

yup...maybe this?

=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C9101,4)="pipe")*(LEN(Original!C2:C9101)=4)*(Original!H2:H9101))
 
D

duane

scratch that...brain cramp

duane said:
yup...maybe this?

=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C9101,4)="pipe")*(LEN(Original!C2:C9101)=4)*(Original!H2:H9101))
Click to expand...
 
D

duane

and the answer is both

=SUMPRODUCT(((LEFT(Original!C2:C9101,4)="pipe")*(LEN(Original!C2:C9101)=4)+(LEFT(Original!C2:C9101,5)="pipe
"))*(Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(Original!H2:H9101))
 
B

Biff

That will only count cells with a len of 4 chars!

Now, it will count

pipe

and nothing else!

Try this:

=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101=400)*(LEFT(Original!C2:C9101&"
",5)="pipe ")*Original!H2:H9101)

Also, if you ever need to calculate on just the specific word "pipe" you can
pad the beginning of each cell with a space:

......LEFT(" "&Original!C2:C9101&" ",5)=" pipe ")

OR

......LEFT(" "&Original!C2:C9101&" ",5)=" "&A1&" ")

Variations of this method come in handy if you're doing "database like"
searches. It's much more reliable then using:

ISNUMBER(SEARCH(.....)

but I still haven't found a method that is 100% bullet proof!

Biff
 
M

Mosqui

Thanks for your help, that was great !!!!



Biff said:
Hi!

Try this:

=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C9101,4)="pipe")*(Original!H2:H9101))

Biff
Click to expand...
 
A

Aladin Akyurek

....(LEFT(Original!C2:C9101&" ",5)="pipe ")
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
Click to expand...

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
 
Top