Thread: sumproduct formula

Hi,
I hava a workbook with 2 worksheets.

Sheet1 consists of 3 columns:
column A: invoice number
column B: "H" or "B" letters
column C: numerical value

Sheet2 consists of 10 columns:
column A: invoice number
column B: product item ID
column C: product weight
column D: price
column E: number of Items
column F: "H" or "B"

The task is:
1.For C column in Sheet1 to sum all the column E in Sheet2 having the same
Invoıce number as in Sheet1 column A and having the same letter in column F
as in sheet1 column B.

Thanks for help

Ermek

Hi

Try
=SUMPRODUCT(--(Sheet2!$A$$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)

Change ranges to suit, but keep them of equal length.

--
Regards

Roger Govier


"ermeko" <ermeko@discussions.microsoft.com> wrote in message
news:54E83BBD-8CC4-46E0-B7D3-1AEBBA3C1CCB@microsoft.com...
> Hi,
> I hava a workbook with 2 worksheets.
>
> Sheet1 consists of 3 columns:
> column A: invoice number
> column B: "H" or "B" letters
> column C: numerical value
>
> Sheet2 consists of 10 columns:
> column A: invoice number
> column B: product item ID
> column C: product weight
> column D: price
> column E: number of Items
> column F: "H" or "B"
>
> The task is:
> 1.For C column in Sheet1 to sum all the column E in Sheet2 having the
> same
> Invoice number as in Sheet1 column A and having the same letter in
> column F
> as in sheet1 column B.
>
> Thanks for help
>
> Ermek
>
>

Apologies, got carried away with the dollars!!!
>=SUMPRODUCT(--(Sheet2!$A$$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)

Should be
=SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)

--
Regards

Roger Govier


"ermeko" <ermeko@discussions.microsoft.com> wrote in message
news:54E83BBD-8CC4-46E0-B7D3-1AEBBA3C1CCB@microsoft.com...
> Hi,
> I hava a workbook with 2 worksheets.
>
> Sheet1 consists of 3 columns:
> column A: invoice number
> column B: "H" or "B" letters
> column C: numerical value
>
> Sheet2 consists of 10 columns:
> column A: invoice number
> column B: product item ID
> column C: product weight
> column D: price
> column E: number of Items
> column F: "H" or "B"
>
> The task is:
> 1.For C column in Sheet1 to sum all the column E in Sheet2 having the
> same
> Invoice number as in Sheet1 column A and having the same letter in
> column F
> as in sheet1 column B.
>
> Thanks for help
>
> Ermek
>
>

Roger thanks for help,
When I type the formula and push the "enter" button I got an error message?

Thanks,

"Roger Govier" wrote:

> Apologies, got carried away with the dollars!!!
> >=SUMPRODUCT(--(Sheet2!$A$$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)
>
> Should be
> =SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)
>
> --
> Regards
>
> Roger Govier
>
>
> "ermeko" <ermeko@discussions.microsoft.com> wrote in message
> news:54E83BBD-8CC4-46E0-B7D3-1AEBBA3C1CCB@microsoft.com...
> > Hi,
> > I hava a workbook with 2 worksheets.
> >
> > Sheet1 consists of 3 columns:
> > column A: invoice number
> > column B: "H" or "B" letters
> > column C: numerical value
> >
> > Sheet2 consists of 10 columns:
> > column A: invoice number
> > column B: product item ID
> > column C: product weight
> > column D: price
> > column E: number of Items
> > column F: "H" or "B"
> >
> > The task is:
> > 1.For C column in Sheet1 to sum all the column E in Sheet2 having the
> > same
> > Invoice number as in Sheet1 column A and having the same letter in
> > column F
> > as in sheet1 column B.
> >
> > Thanks for help
> >
> > Ermek
> >
> >
>
>
>

=SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)

when I chenge "," with ";" the formula works.

Roger thanks for your help.

"Roger Govier" wrote:

> Apologies, got carried away with the dollars!!!
> >=SUMPRODUCT(--(Sheet2!$A$$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)
>
> Should be
> =SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)
>
> --
> Regards
>
> Roger Govier
>
>
> "ermeko" <ermeko@discussions.microsoft.com> wrote in message
> news:54E83BBD-8CC4-46E0-B7D3-1AEBBA3C1CCB@microsoft.com...
> > Hi,
> > I hava a workbook with 2 worksheets.
> >
> > Sheet1 consists of 3 columns:
> > column A: invoice number
> > column B: "H" or "B" letters
> > column C: numerical value
> >
> > Sheet2 consists of 10 columns:
> > column A: invoice number
> > column B: product item ID
> > column C: product weight
> > column D: price
> > column E: number of Items
> > column F: "H" or "B"
> >
> > The task is:
> > 1.For C column in Sheet1 to sum all the column E in Sheet2 having the
> > same
> > Invoice number as in Sheet1 column A and having the same letter in
> > column F
> > as in sheet1 column B.
> >
> > Thanks for help
> >
> > Ermek
> >
> >
>
>
>

Hi

Thanks for the feedback.
Difference in Regional settings for you compared with me in the UK was
the reason you needed to change the commas to semi-colons.
Glad you got it to work.

--
Regards

Roger Govier


"ermeko" <ermeko@discussions.microsoft.com> wrote in message
news:4399F06A-1942-4D70-B98F-1D45B7B2B7FF@microsoft.com...
> =SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)
>
> when I chenge "," with ";" the formula works.
>
> Roger thanks for your help.
>
> "Roger Govier" wrote:
>
>> Apologies, got carried away with the dollars!!!
>> >=SUMPRODUCT(--(Sheet2!$A$$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)
>>
>> Should be
>> =SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "ermeko" <ermeko@discussions.microsoft.com> wrote in message
>> news:54E83BBD-8CC4-46E0-B7D3-1AEBBA3C1CCB@microsoft.com...
>> > Hi,
>> > I hava a workbook with 2 worksheets.
>> >
>> > Sheet1 consists of 3 columns:
>> > column A: invoice number
>> > column B: "H" or "B" letters
>> > column C: numerical value
>> >
>> > Sheet2 consists of 10 columns:
>> > column A: invoice number
>> > column B: product item ID
>> > column C: product weight
>> > column D: price
>> > column E: number of Items
>> > column F: "H" or "B"
>> >
>> > The task is:
>> > 1.For C column in Sheet1 to sum all the column E in Sheet2 having
>> > the
>> > same
>> > Invoice number as in Sheet1 column A and having the same letter in
>> > column F
>> > as in sheet1 column B.
>> >
>> > Thanks for help
>> >
>> > Ermek
>> >
>> >
>>
>>
>>

Another reason to use:

=SUMPRODUCT((Sheet2!$A$1:$A$1000=A1)*(Sheet2!$F$1:$F$1000=B1)*Sheet2!$E$1:$E
$1000)
<vbg>

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:urdAbqAqGHA.516@TK2MSFTNGP05.phx.gbl...
Hi

Thanks for the feedback.
Difference in Regional settings for you compared with me in the UK was
the reason you needed to change the commas to semi-colons.
Glad you got it to work.

--
Regards

Roger Govier


"ermeko" <ermeko@discussions.microsoft.com> wrote in message
news:4399F06A-1942-4D70-B98F-1D45B7B2B7FF@microsoft.com...
>
=SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$
1:$E$1000)
>
> when I chenge "," with ";" the formula works.
>
> Roger thanks for your help.
>
> "Roger Govier" wrote:
>
>> Apologies, got carried away with the dollars!!!
>>
>=SUMPRODUCT(--(Sheet2!$A$$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$
E$1:$E$1000)
>>
>> Should be
>>
=SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$
1:$E$1000)
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "ermeko" <ermeko@discussions.microsoft.com> wrote in message
>> news:54E83BBD-8CC4-46E0-B7D3-1AEBBA3C1CCB@microsoft.com...
>> > Hi,
>> > I hava a workbook with 2 worksheets.
>> >
>> > Sheet1 consists of 3 columns:
>> > column A: invoice number
>> > column B: "H" or "B" letters
>> > column C: numerical value
>> >
>> > Sheet2 consists of 10 columns:
>> > column A: invoice number
>> > column B: product item ID
>> > column C: product weight
>> > column D: price
>> > column E: number of Items
>> > column F: "H" or "B"
>> >
>> > The task is:
>> > 1.For C column in Sheet1 to sum all the column E in Sheet2 having
>> > the
>> > same
>> > Invoice number as in Sheet1 column A and having the same letter in
>> > column F
>> > as in sheet1 column B.
>> >
>> > Thanks for help
>> >
>> > Ermek
>> >
>> >
>>
>>
>>

I always used to, and I'm beginning to think your right RD<bg>

--
Regards

Roger Govier


"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:O9jljCDqGHA.524@TK2MSFTNGP05.phx.gbl...
> Another reason to use:
>
> =SUMPRODUCT((Sheet2!$A$1:$A$1000=A1)*(Sheet2!$F$1:$F$1000=B1)*Sheet2!$E$1:$E
> $1000)
> <vbg>
>
> --
>
> Regards,
>
> RD
> ----------------------------------------------------------------------------
> -------------------
> Please keep all correspondence within the Group, so all may benefit !
> ----------------------------------------------------------------------------
> -------------------
>
> "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:urdAbqAqGHA.516@TK2MSFTNGP05.phx.gbl...
> Hi
>
> Thanks for the feedback.
> Difference in Regional settings for you compared with me in the UK was
> the reason you needed to change the commas to semi-colons.
> Glad you got it to work.
>
> --
> Regards
>
> Roger Govier
>
>
> "ermeko" <ermeko@discussions.microsoft.com> wrote in message
> news:4399F06A-1942-4D70-B98F-1D45B7B2B7FF@microsoft.com...
>>
> =SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$
> 1:$E$1000)
>>
>> when I chenge "," with ";" the formula works.
>>
>> Roger thanks for your help.
>>
>> "Roger Govier" wrote:
>>
>>> Apologies, got carried away with the dollars!!!
>>>
>>=SUMPRODUCT(--(Sheet2!$A$$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$
> E$1:$E$1000)
>>>
>>> Should be
>>>
> =SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$
> 1:$E$1000)
>>>
>>> --
>>> Regards
>>>
>>> Roger Govier
>>>
>>>
>>> "ermeko" <ermeko@discussions.microsoft.com> wrote in message
>>> news:54E83BBD-8CC4-46E0-B7D3-1AEBBA3C1CCB@microsoft.com...
>>> > Hi,
>>> > I hava a workbook with 2 worksheets.
>>> >
>>> > Sheet1 consists of 3 columns:
>>> > column A: invoice number
>>> > column B: "H" or "B" letters
>>> > column C: numerical value
>>> >
>>> > Sheet2 consists of 10 columns:
>>> > column A: invoice number
>>> > column B: product item ID
>>> > column C: product weight
>>> > column D: price
>>> > column E: number of Items
>>> > column F: "H" or "B"
>>> >
>>> > The task is:
>>> > 1.For C column in Sheet1 to sum all the column E in Sheet2 having
>>> > the
>>> > same
>>> > Invoice number as in Sheet1 column A and having the same letter in
>>> > column F
>>> > as in sheet1 column B.
>>> >
>>> > Thanks for help
>>> >
>>> > Ermek
>>> >
>>> >
>>>
>>>
>>>
>
>
>

It does have it's place, but they're few and far between.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:OhHEg6EqGHA.3244@TK2MSFTNGP03.phx.gbl...
I always used to, and I'm beginning to think your right RD<bg>

--
Regards

Roger Govier


"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:O9jljCDqGHA.524@TK2MSFTNGP05.phx.gbl...
> Another reason to use:
>
>
=SUMPRODUCT((Sheet2!$A$1:$A$1000=A1)*(Sheet2!$F$1:$F$1000=B1)*Sheet2!$E$1:$E
> $1000)
> <vbg>
>
> --
>
> Regards,
>
> RD
> --------------------------------------------------------------------------
--
> -------------------
> Please keep all correspondence within the Group, so all may benefit !
> --------------------------------------------------------------------------
--
> -------------------
>
> "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:urdAbqAqGHA.516@TK2MSFTNGP05.phx.gbl...
> Hi
>
> Thanks for the feedback.
> Difference in Regional settings for you compared with me in the UK was
> the reason you needed to change the commas to semi-colons.
> Glad you got it to work.
>
> --
> Regards
>
> Roger Govier
>
>
> "ermeko" <ermeko@discussions.microsoft.com> wrote in message
> news:4399F06A-1942-4D70-B98F-1D45B7B2B7FF@microsoft.com...
>>
>
=SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$
> 1:$E$1000)
>>
>> when I chenge "," with ";" the formula works.
>>
>> Roger thanks for your help.
>>
>> "Roger Govier" wrote:
>>
>>> Apologies, got carried away with the dollars!!!
>>>
>>=SUMPRODUCT(--(Sheet2!$A$$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!
$
> E$1:$E$1000)
>>>
>>> Should be
>>>
>
=SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$
> 1:$E$1000)
>>>
>>> --
>>> Regards
>>>
>>> Roger Govier
>>>
>>>
>>> "ermeko" <ermeko@discussions.microsoft.com> wrote in message
>>> news:54E83BBD-8CC4-46E0-B7D3-1AEBBA3C1CCB@microsoft.com...
>>> > Hi,
>>> > I hava a workbook with 2 worksheets.
>>> >
>>> > Sheet1 consists of 3 columns:
>>> > column A: invoice number
>>> > column B: "H" or "B" letters
>>> > column C: numerical value
>>> >
>>> > Sheet2 consists of 10 columns:
>>> > column A: invoice number
>>> > column B: product item ID
>>> > column C: product weight
>>> > column D: price
>>> > column E: number of Items
>>> > column F: "H" or "B"
>>> >
>>> > The task is:
>>> > 1.For C column in Sheet1 to sum all the column E in Sheet2 having
>>> > the
>>> > same
>>> > Invoice number as in Sheet1 column A and having the same letter in
>>> > column F
>>> > as in sheet1 column B.
>>> >
>>> > Thanks for help
>>> >
>>> > Ermek
>>> >
>>> >
>>>
>>>
>>>
>
>
>

Hi,
I also think it is becouse of regional settings. Now I face problems with
date formulas, probably becouse of regional settings.

Thanks again

"Roger Govier" wrote:

> Hi
>
> Thanks for the feedback.
> Difference in Regional settings for you compared with me in the UK was
> the reason you needed to change the commas to semi-colons.
> Glad you got it to work.
>
> --
> Regards
>
> Roger Govier
>
>
> "ermeko" <ermeko@discussions.microsoft.com> wrote in message
> news:4399F06A-1942-4D70-B98F-1D45B7B2B7FF@microsoft.com...
> > =SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)
> >
> > when I chenge "," with ";" the formula works.
> >
> > Roger thanks for your help.
> >
> > "Roger Govier" wrote:
> >
> >> Apologies, got carried away with the dollars!!!
> >> >=SUMPRODUCT(--(Sheet2!$A$$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)
> >>
> >> Should be
> >> =SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "ermeko" <ermeko@discussions.microsoft.com> wrote in message
> >> news:54E83BBD-8CC4-46E0-B7D3-1AEBBA3C1CCB@microsoft.com...
> >> > Hi,
> >> > I hava a workbook with 2 worksheets.
> >> >
> >> > Sheet1 consists of 3 columns:
> >> > column A: invoice number
> >> > column B: "H" or "B" letters
> >> > column C: numerical value
> >> >
> >> > Sheet2 consists of 10 columns:
> >> > column A: invoice number
> >> > column B: product item ID
> >> > column C: product weight
> >> > column D: price
> >> > column E: number of Items
> >> > column F: "H" or "B"
> >> >
> >> > The task is:
> >> > 1.For C column in Sheet1 to sum all the column E in Sheet2 having
> >> > the
> >> > same
> >> > Invoice number as in Sheet1 column A and having the same letter in
> >> > column F
> >> > as in sheet1 column B.
> >> >
> >> > Thanks for help
> >> >
> >> > Ermek
> >> >
> >> >
> >>
> >>
> >>
>
>
>