sumproduct formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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)
Click to expand...

Should be
=SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A1),--(Sheet2!$F$1:$F$1000=B1),Sheet2!$E$1:$E$1000)
 
Roger thanks for help,
When I type the formula and push the "enter" button I got an error message?

Thanks,
 
=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.
 
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.
 
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.
 
Hi,
I also think it is becouse of regional settings. Now I face problems with
date formulas, probably becouse of regional settings.

Thanks again
 
Back
Top