sumproduct formula

[Guest]

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

 

[Roger Govier]

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.

 

[Roger Govier]

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)

 

[Guest]

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

Thanks,

 

[Guest]

=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]

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.

 

[RagDyeR]

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

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.

 

[Roger Govier]

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

 

[RagDyeR]

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

 

[Guest]

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

Thanks again