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


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=486158

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

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$1000
1="Oct"),--(tyre_procure!$J$2:$J$10001="Solid"),--(tyre_procure!$K$2:$K$1000
1=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


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=486158

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


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=486158

No, just type them in this case.

--

HTH

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


"cjjoo" wrote in
message ...

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


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=486158

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

cjjoo wrote:

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

--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=486158

--

Dave Peterson

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

:-))


"Dave Peterson" wrote in message
...
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!)

cjjoo wrote:

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

--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread:
http://www.excelforum.com/showthread...hreadid=486158

--

Dave Peterson

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" wrote in message
...
Most of it was already his, all he had to was to replace
{"patch","tube"}
with
{"patch","tube"}

:-))


"Dave Peterson" wrote in message
...
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!)

cjjoo wrote:

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

--
cjjoo

------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread:
http://www.excelforum.com/showthread...hreadid=486158

--

Dave Peterson

I thought I was seeing things!

Bob Phillips wrote:

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" wrote in message
...
Most of it was already his, all he had to was to replace
{"patch","tube"}
with
{"patch","tube"}

:-))


"Dave Peterson" wrote in message
...
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!)

cjjoo wrote:

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

--
cjjoo

------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread:
http://www.excelforum.com/showthread...hreadid=486158

--

Dave Peterson



--

Dave Peterson

That Lagavulin is good stuff :-))


"Dave Peterson" wrote in message
...
I thought I was seeing things!

Bob Phillips wrote:

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" wrote in message
...
Most of it was already his, all he had to was to replace
{"patch","tube"}
with
{"patch","tube"}

:-))


"Dave Peterson" wrote in message
...
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!)

cjjoo wrote:

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

--
cjjoo


------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread:
http://www.excelforum.com/showthread...hreadid=486158

--

Dave Peterson



--

Dave Peterson

Off to google!

Ahhhh.



Bob Phillips wrote:

That Lagavulin is good stuff :-))

"Dave Peterson" wrote in message
...
I thought I was seeing things!

Bob Phillips wrote:

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" wrote in message
...
Most of it was already his, all he had to was to replace
{"patch","tube"}
with
{"patch","tube"}

:-))


"Dave Peterson" wrote in message
...
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!)

cjjoo wrote:

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

--
cjjoo


------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread:
http://www.excelforum.com/showthread...hreadid=486158

--

Dave Peterson



--

Dave Peterson

--

Dave Peterson