another sumproduct question

[cjjoo]

hi guys,

this is my problem:

in a sumproduct function, i want to sum the columns that fulfil four
criterias.

The nagging problem is tat in one of the criteria , i want it to picked
out (from the

assigned column) either " tube " or " patch" but i do not know how to
do it . Can


anyone advise me on this? I came across some sumproduct where people
use

the + sign in a sumpdt function. Can i use it for my case ?


my function: =
(SUMPRODUCT(--(tyre_procure!$E$2:$E$10001=4086),--(tyre_procure!$B$2:$B$10001="Oct"),--(tyre_procure!$J$2:$J$10001="Solid"),--(tyre_procure!$K$2:$K$10001=REPLACE(C7,7,1,"")),--(ISNUMBER(FIND("patch",tyre_procure!$F$2:$F$10001))),tyre_procure!$H$2:$H$10001))


the problem lies in the criteria colored in red. i wan to make
adustments so that the function will picked out either the word
"patch " or "tube" in column F

 

[Bob Phillips]

You could, but this is simpler

=(SUMPRODUCT(--(tyre_procure!$E$2:$E$10001=4086),-
-(tyre_procure!$B$2:$B$10001="Oct"),
--(tyre_procure!$J$2:$J$10001="Solid"),
--(tyre_procure!$K$2:$K$10001=REPLACE(C7,7,1,"")),
--(ISNUMBER(FIND({"patch","tube"},tyre_procure!$F$2:$F$10001))),
tyre_procure!$H$2:$H$10001))


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[cjjoo]

how do i insert the {} brackets? Do i have to insert them as ctr+ shift
+ enter ?

 

[Bob Phillips]

No, just type them in this case.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

In this case, copy the whole formula from Bob's message and paste it into the
formula bar for that cell.

(I wouldn't want to type it in from scratch!)

 

[Bob Phillips]

Most of it was already his, all he had to was to replace
{"patch","tube"}
with
{"patch","tube"}

)

 

[Bob Phillips]

Try and say it properly

Most of it was already his, all he had to was to replace
"patch"
with
{"patch","tube"}

)

--

HTH

RP
(remove nothere from the email address if mailing direct)

Most of it was already his, all he had to was to replace
{"patch","tube"}
with
{"patch","tube"}

)

In this case, copy the whole formula from Bob's message and paste it

into

 

[Dave Peterson]

I thought I was seeing things!

Try and say it properly

Most of it was already his, all he had to was to replace
"patch"
with
{"patch","tube"}

)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

That Lagavulin is good stuff )

I thought I was seeing things!

 

[Dave Peterson]

Off to google!

Ahhhh.

That Lagavulin is good stuff )