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