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" <cjjoo.1yomtb_1132300206.9691@excelforum-nospam.com> wrote in
message news:cjjoo.1yomtb_1132300206.9691@excelforum-nospam.com...
>
> 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
>

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


"cjjoo" <cjjoo.1yoq1y_1132304402.3498@excelforum-nospam.com> wrote in
message news:cjjoo.1yoq1y_1132304402.3498@excelforum-nospam.com...
>
> 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]

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

[Bob Phillips]

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

:-))


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:437E0276.9DED555@verizonXSPAM.net...
> 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

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


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uWq5XSG7FHA.472@TK2MSFTNGP15.phx.gbl...
> Most of it was already his, all he had to was to replace
> {"patch","tube"}
> with
> {"patch","tube"}
>
> :-))
>
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:437E0276.9DED555@verizonXSPAM.net...
> > 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]

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" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:uWq5XSG7FHA.472@TK2MSFTNGP15.phx.gbl...
> > Most of it was already his, all he had to was to replace
> > {"patch","tube"}
> > with
> > {"patch","tube"}
> >
> > :-))
> >
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:437E0276.9DED555@verizonXSPAM.net...
> > > 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

[Bob Phillips]

That Lagavulin is good stuff :-))


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:437E1E0B.61FF7DA2@verizonXSPAM.net...
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:uWq5XSG7FHA.472@TK2MSFTNGP15.phx.gbl...
> > > Most of it was already his, all he had to was to replace
> > > {"patch","tube"}
> > > with
> > > {"patch","tube"}
> > >
> > > :-))
> > >
> > >
> > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > news:437E0276.9DED555@verizonXSPAM.net...
> > > > 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]

Off to google!

Ahhhh.



Bob Phillips wrote:
>
> That Lagavulin is good stuff :-))
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:437E1E0B.61FF7DA2@verizonXSPAM.net...
> > 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > news:uWq5XSG7FHA.472@TK2MSFTNGP15.phx.gbl...
> > > > Most of it was already his, all he had to was to replace
> > > > {"patch","tube"}
> > > > with
> > > > {"patch","tube"}
> > > >
> > > > :-))
> > > >
> > > >
> > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > news:437E0276.9DED555@verizonXSPAM.net...
> > > > > 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