| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct formula
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" 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct formula
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct formula
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" 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct formula
=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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct formula
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct formula
I always used to, and I'm beginning to think your right RD<bg
-- Regards Roger Govier "RagDyeR" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct formula
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" wrote in message ... I always used to, and I'm beginning to think your right RD<bg -- Regards Roger Govier "RagDyeR" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct formula
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" 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SumProduct Formula Help | Excel Worksheet Functions | |||
| do I need array formula or sumproduct for counting? | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| sumproduct formula to slow | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
