Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
On sheet one I have: Columns;C, D, E, J & Rows; 9, 10, 11, 12, 13, 14 as an
example. Column: C is for quantities, D is for sizes, E is for grades, J is for length : C D E J ------------------------------------------ 9: 4 .625 SAE 1.50 10: 6 .625 SAE 1.50 11: 4 .500 A325 1.75 12: 6 .500 A325 1.75 13: 8 .500 SAE 1.50 14: 6 .75 A325 2.25 On sheet two (summary sheet) I have the following as example: Columns: A (size), B (lenght), C (quantities) & rows 20, 21, 22 for A325 and rows 32, 33, 34 for SAE. I would like to see a total quantity shown in column C if I type in size in column A and length in column B for each sizes of same grade & lenght. I get confused with SUMIF or SUMPRODUCT. Can someone un-confuse me with this problem please. Thank you in advance. Serge |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
"Serge" wrote:
On sheet one I have: Columns;C, D, E, J & Rows; 9, 10, 11, 12, 13, 14 Column: C is for quantities, D is for sizes, E is for grades, J is for length : C D E J ------------------------------------------ 9: 4 .625 SAE 1.50 10: 6 .625 SAE 1.50 11: 4 .500 A325 1.75 12: 6 .500 A325 1.75 13: 8 .500 SAE 1.50 14: 6 .75 A325 2.25 On sheet two (summary sheet) Columns: A (size), B (length), C (quantities) & rows 20, 21, 22 for A325 and rows 32, 33, 34 for SAE. .. a total quantity shown in column C if I type in size in column A and length in column B for each sizes of same grade & length. In Sheet2, In C20: =SUMPRODUCT((Sheet1!$D$9:$D$14=A20)*(Sheet1!$J$9:$ J$14=B20)*(Sheet1!$E$9:$E$ 14="A325"),Sheet1!$C$9:$C$14) C20 copied to C22 In C32: =SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="SAE"),Sheet1!$C$9:$C$14) C32 copied to C34 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
On Sheet2, enter this formula in C20:
=SUMPRODUCT((Sheet1!$D$9:$D$14=A20)*(Sheet1!$J$9:$ J$14=B20)*(Sheet1!$E$9:$E$ 14="A325")*Sheet1!$C$9:$C$14) And copy down to C22. In C32 enter: =SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="A325")*Sheet1!$C$9:$C$14) And copy down to C34. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Serge" wrote in message ... On sheet one I have: Columns;C, D, E, J & Rows; 9, 10, 11, 12, 13, 14 as an example. Column: C is for quantities, D is for sizes, E is for grades, J is for length : C D E J ------------------------------------------ 9: 4 .625 SAE 1.50 10: 6 .625 SAE 1.50 11: 4 .500 A325 1.75 12: 6 .500 A325 1.75 13: 8 .500 SAE 1.50 14: 6 .75 A325 2.25 On sheet two (summary sheet) I have the following as example: Columns: A (size), B (lenght), C (quantities) & rows 20, 21, 22 for A325 and rows 32, 33, 34 for SAE. I would like to see a total quantity shown in column C if I type in size in column A and length in column B for each sizes of same grade & lenght. I get confused with SUMIF or SUMPRODUCT. Can someone un-confuse me with this problem please. Thank you in advance. Serge |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
Of course the second formula should refer to "SAE".
=SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="SAE")*Sheet1!$C$9:$C$14) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... On Sheet2, enter this formula in C20: =SUMPRODUCT((Sheet1!$D$9:$D$14=A20)*(Sheet1!$J$9:$ J$14=B20)*(Sheet1!$E$9:$E$ 14="A325")*Sheet1!$C$9:$C$14) And copy down to C22. In C32 enter: =SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="A325")*Sheet1!$C$9:$C$14) And copy down to C34. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Serge" wrote in message ... On sheet one I have: Columns;C, D, E, J & Rows; 9, 10, 11, 12, 13, 14 as an example. Column: C is for quantities, D is for sizes, E is for grades, J is for length : C D E J ------------------------------------------ 9: 4 .625 SAE 1.50 10: 6 .625 SAE 1.50 11: 4 .500 A325 1.75 12: 6 .500 A325 1.75 13: 8 .500 SAE 1.50 14: 6 .75 A325 2.25 On sheet two (summary sheet) I have the following as example: Columns: A (size), B (lenght), C (quantities) & rows 20, 21, 22 for A325 and rows 32, 33, 34 for SAE. I would like to see a total quantity shown in column C if I type in size in column A and length in column B for each sizes of same grade & lenght. I get confused with SUMIF or SUMPRODUCT. Can someone un-confuse me with this problem please. Thank you in advance. Serge |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
"Ragdyer" wrote:
... In C32 enter: =SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="A325")*Sheet1!$C$9:$C$14) Think Ragdyer meant "SAE" instead of "A325" for the formula in C32 <g, viz: In C32: =SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="SAE")*Sheet1!$C$9:$C$14) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
I am working on it, will let you know if it works asap.
Thanks "Max" wrote: "Ragdyer" wrote: ... In C32 enter: =SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="A325")*Sheet1!$C$9:$C$14) Think Ragdyer meant "SAE" instead of "A325" for the formula in C32 <g, viz: In C32: =SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="SAE")*Sheet1!$C$9:$C$14) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
Thank you all VERY MUCH for your imput. It works GREAT
"Max" wrote: "Ragdyer" wrote: ... In C32 enter: =SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="A325")*Sheet1!$C$9:$C$14) Think Ragdyer meant "SAE" instead of "A325" for the formula in C32 <g, viz: In C32: =SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="SAE")*Sheet1!$C$9:$C$14) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
Thank you all VERY MUCH for your imput. It works GREAT
"Ragdyer" wrote: Of course the second formula should refer to "SAE". =SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="SAE")*Sheet1!$C$9:$C$14) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... On Sheet2, enter this formula in C20: =SUMPRODUCT((Sheet1!$D$9:$D$14=A20)*(Sheet1!$J$9:$ J$14=B20)*(Sheet1!$E$9:$E$ 14="A325")*Sheet1!$C$9:$C$14) And copy down to C22. In C32 enter: =SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="A325")*Sheet1!$C$9:$C$14) And copy down to C34. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Serge" wrote in message ... On sheet one I have: Columns;C, D, E, J & Rows; 9, 10, 11, 12, 13, 14 as an example. Column: C is for quantities, D is for sizes, E is for grades, J is for length : C D E J ------------------------------------------ 9: 4 .625 SAE 1.50 10: 6 .625 SAE 1.50 11: 4 .500 A325 1.75 12: 6 .500 A325 1.75 13: 8 .500 SAE 1.50 14: 6 .75 A325 2.25 On sheet two (summary sheet) I have the following as example: Columns: A (size), B (lenght), C (quantities) & rows 20, 21, 22 for A325 and rows 32, 33, 34 for SAE. I would like to see a total quantity shown in column C if I type in size in column A and length in column B for each sizes of same grade & lenght. I get confused with SUMIF or SUMPRODUCT. Can someone un-confuse me with this problem please. Thank you in advance. Serge |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
Hello Max,
It work well until I copied the formula to empty cell to give me a "#NA" for answer. can the formula be modified? "Max" wrote: "Ragdyer" wrote: ... In C32 enter: =SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="A325")*Sheet1!$C$9:$C$14) Think Ragdyer meant "SAE" instead of "A325" for the formula in C32 <g, viz: In C32: =SUMPRODUCT((Sheet1!$D$9:$D$14=A32)*(Sheet1!$J$9:$ J$14=B32)*(Sheet1!$E$9:$E$ 14="SAE")*Sheet1!$C$9:$C$14) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
"Serge" wrote:
.. It work well until I copied the formula to empty cell to give me a "#NA" for answer. can the formula be modified? I'm not sure how you got that #N/A error, but you could try these 2 slight variations* to what I suggested earlier (note that my suggestion is a little different from Ragdyer's) *with a simple error trap to check on col A & B having inputs In C20, copied to C22: =IF(OR(A20="",B20=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$14=A20)*(Sheet1!$J$9:$J$ 14=B20)*(Sheet1!$E$9:$E$14="A325"),Sheet1!$C$9:$C$ 14)) In C32, copied to C34: =IF(OR(A32="",B32=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$14=A32)*(Sheet1!$J$9:$J$ 14=B32)*(Sheet1!$E$9:$E$14="SAE"),Sheet1!$C$9:$C$1 4)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
Hello Max,
Thanks for your reply. My question refered to empty cells on the first sheet, because in the given formula I changed the row number 14 to 188 which do not have any input yet. "Serge" wrote: On sheet one I have: Columns;C, D, E, J & Rows; 9, 10, 11, 12, 13, 14 as an example. Column: C is for quantities, D is for sizes, E is for grades, J is for length : C D E J ------------------------------------------ 9: 4 .625 SAE 1.50 10: 6 .625 SAE 1.50 11: 4 .500 A325 1.75 12: 6 .500 A325 1.75 13: 8 .500 SAE 1.50 14: 6 .75 A325 2.25 On sheet two (summary sheet) I have the following as example: Columns: A (size), B (lenght), C (quantities) & rows 20, 21, 22 for A325 and rows 32, 33, 34 for SAE. I would like to see a total quantity shown in column C if I type in size in column A and length in column B for each sizes of same grade & lenght. I get confused with SUMIF or SUMPRODUCT. Can someone un-confuse me with this problem please. Thank you in advance. Serge |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
"Serge" wrote:
.. My question refered to empty cells on the first sheet, because in the given formula I changed the row number 14 to 188 which do not have any input yet. All the ranges in the sumproduct formula have to be identical in structure. Ensure that this is the case (that's probably why you got the #N/A earlier) Anyway, here's the revised formulas extended to row 188 for reference In C20, copied to C22: =IF(OR(A20="",B20=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$14=A20)*(Sheet1!$J$9:$J$188=B20)*(Sheet1!$E$9:$E $188="A325"),Sheet1!$C$9:$C$188)) In C32, copied to C34: =IF(OR(A32="",B32=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$188=A32)*(Sheet1!$J$9:$J$188=B32)*(Sheet1!$E$9:$ E$188="SAE"),Sheet1!$C$9:$C$188)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Serge" wrote: On sheet one I have: Columns;C, D, E, J & Rows; 9, 10, 11, 12, 13, 14 as an example. Column: C is for quantities, D is for sizes, E is for grades, J is for length : C D E J ------------------------------------------ 9: 4 .625 SAE 1.50 10: 6 .625 SAE 1.50 11: 4 .500 A325 1.75 12: 6 .500 A325 1.75 13: 8 .500 SAE 1.50 14: 6 .75 A325 2.25 On sheet two (summary sheet) I have the following as example: Columns: A (size), B (lenght), C (quantities) & rows 20, 21, 22 for A325 and rows 32, 33, 34 for SAE. I would like to see a total quantity shown in column C if I type in size in column A and length in column B for each sizes of same grade & lenght. I get confused with SUMIF or SUMPRODUCT. Can someone un-confuse me with this problem please. Thank you in advance. Serge |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
Hello again Max,
Still have #N/A for answer as long as there are input in those rows in the first sheet. I changed the row back to it's original number and it works fine. I do need to include all the rows even though there is no input in them. Thanks for your effort. Serge in BC Canada "Max" wrote: "Serge" wrote: .. My question refered to empty cells on the first sheet, because in the given formula I changed the row number 14 to 188 which do not have any input yet. All the ranges in the sumproduct formula have to be identical in structure. Ensure that this is the case (that's probably why you got the #N/A earlier) Anyway, here's the revised formulas extended to row 188 for reference In C20, copied to C22: =IF(OR(A20="",B20=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$14=A20)*(Sheet1!$J$9:$J$188=B20)*(Sheet1!$E$9:$E $188="A325"),Sheet1!$C$9:$C$188)) In C32, copied to C34: =IF(OR(A32="",B32=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$188=A32)*(Sheet1!$J$9:$J$188=B32)*(Sheet1!$E$9:$ E$188="SAE"),Sheet1!$C$9:$C$188)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Serge" wrote: On sheet one I have: Columns;C, D, E, J & Rows; 9, 10, 11, 12, 13, 14 as an example. Column: C is for quantities, D is for sizes, E is for grades, J is for length : C D E J ------------------------------------------ 9: 4 .625 SAE 1.50 10: 6 .625 SAE 1.50 11: 4 .500 A325 1.75 12: 6 .500 A325 1.75 13: 8 .500 SAE 1.50 14: 6 .75 A325 2.25 On sheet two (summary sheet) I have the following as example: Columns: A (size), B (lenght), C (quantities) & rows 20, 21, 22 for A325 and rows 32, 33, 34 for SAE. I would like to see a total quantity shown in column C if I type in size in column A and length in column B for each sizes of same grade & lenght. I get confused with SUMIF or SUMPRODUCT. Can someone un-confuse me with this problem please. Thank you in advance. Serge |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
There is a . after the word "answer"
"Serge" wrote: Hello again Max, Still have #N/A for answer as long as there are input in those rows in the first sheet. I changed the row back to it's original number and it works fine. I do need to include all the rows even though there is no input in them. Thanks for your effort. Serge in BC Canada "Max" wrote: "Serge" wrote: .. My question refered to empty cells on the first sheet, because in the given formula I changed the row number 14 to 188 which do not have any input yet. All the ranges in the sumproduct formula have to be identical in structure. Ensure that this is the case (that's probably why you got the #N/A earlier) Anyway, here's the revised formulas extended to row 188 for reference In C20, copied to C22: =IF(OR(A20="",B20=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$14=A20)*(Sheet1!$J$9:$J$188=B20)*(Sheet1!$E$9:$E $188="A325"),Sheet1!$C$9:$C$188)) In C32, copied to C34: =IF(OR(A32="",B32=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$188=A32)*(Sheet1!$J$9:$J$188=B32)*(Sheet1!$E$9:$ E$188="SAE"),Sheet1!$C$9:$C$188)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Serge" wrote: On sheet one I have: Columns;C, D, E, J & Rows; 9, 10, 11, 12, 13, 14 as an example. Column: C is for quantities, D is for sizes, E is for grades, J is for length : C D E J ------------------------------------------ 9: 4 .625 SAE 1.50 10: 6 .625 SAE 1.50 11: 4 .500 A325 1.75 12: 6 .500 A325 1.75 13: 8 .500 SAE 1.50 14: 6 .75 A325 2.25 On sheet two (summary sheet) I have the following as example: Columns: A (size), B (lenght), C (quantities) & rows 20, 21, 22 for A325 and rows 32, 33, 34 for SAE. I would like to see a total quantity shown in column C if I type in size in column A and length in column B for each sizes of same grade & lenght. I get confused with SUMIF or SUMPRODUCT. Can someone un-confuse me with this problem please. Thank you in advance. Serge |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
Change the $D$14 to $D$188
from: =IF(OR(A20="",B20=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$14=A20)*(Sheet1!$J$9:$J$188=B20)*(Sheet1!$E$9:$E $188="A325"),Sheet1!$C$9:$C$188)) to: =IF(OR(A20="",B20=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$188=A20)*(Sheet1!$J$9:$J$188=B20)*(Sheet1!$E$9:$ E$188="A325"),Sheet1!$C$9:$C$188)) "Serge" wrote: There is a . after the word "answer" "Serge" wrote: Hello again Max, Still have #N/A for answer as long as there are input in those rows in the first sheet. I changed the row back to it's original number and it works fine. I do need to include all the rows even though there is no input in them. Thanks for your effort. Serge in BC Canada "Max" wrote: "Serge" wrote: .. My question refered to empty cells on the first sheet, because in the given formula I changed the row number 14 to 188 which do not have any input yet. All the ranges in the sumproduct formula have to be identical in structure. Ensure that this is the case (that's probably why you got the #N/A earlier) Anyway, here's the revised formulas extended to row 188 for reference In C20, copied to C22: =IF(OR(A20="",B20=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$14=A20)*(Sheet1!$J$9:$J$188=B20)*(Sheet1!$E$9:$E $188="A325"),Sheet1!$C$9:$C$188)) In C32, copied to C34: =IF(OR(A32="",B32=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$188=A32)*(Sheet1!$J$9:$J$188=B32)*(Sheet1!$E$9:$ E$188="SAE"),Sheet1!$C$9:$C$188)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Serge" wrote: On sheet one I have: Columns;C, D, E, J & Rows; 9, 10, 11, 12, 13, 14 as an example. Column: C is for quantities, D is for sizes, E is for grades, J is for length : C D E J ------------------------------------------ 9: 4 .625 SAE 1.50 10: 6 .625 SAE 1.50 11: 4 .500 A325 1.75 12: 6 .500 A325 1.75 13: 8 .500 SAE 1.50 14: 6 .75 A325 2.25 On sheet two (summary sheet) I have the following as example: Columns: A (size), B (lenght), C (quantities) & rows 20, 21, 22 for A325 and rows 32, 33, 34 for SAE. I would like to see a total quantity shown in column C if I type in size in column A and length in column B for each sizes of same grade & lenght. I get confused with SUMIF or SUMPRODUCT. Can someone un-confuse me with this problem please. Thank you in advance. Serge |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
Hello Toppers,
I did that, and when I did I gor "#N/A" for answer, between row 15 & 188 I have empty cells in the first sheet. Cells that do not have input yet. "Toppers" wrote: Change the $D$14 to $D$188 from: =IF(OR(A20="",B20=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$14=A20)*(Sheet1!$J$9:$J$188=B20)*(Sheet1!$E$9:$E $188="A325"),Sheet1!$C$9:$C$188)) to: =IF(OR(A20="",B20=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$188=A20)*(Sheet1!$J$9:$J$188=B20)*(Sheet1!$E$9:$ E$188="A325"),Sheet1!$C$9:$C$188)) "Serge" wrote: There is a . after the word "answer" "Serge" wrote: Hello again Max, Still have #N/A for answer as long as there are input in those rows in the first sheet. I changed the row back to it's original number and it works fine. I do need to include all the rows even though there is no input in them. Thanks for your effort. Serge in BC Canada "Max" wrote: "Serge" wrote: .. My question refered to empty cells on the first sheet, because in the given formula I changed the row number 14 to 188 which do not have any input yet. All the ranges in the sumproduct formula have to be identical in structure. Ensure that this is the case (that's probably why you got the #N/A earlier) Anyway, here's the revised formulas extended to row 188 for reference In C20, copied to C22: =IF(OR(A20="",B20=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$14=A20)*(Sheet1!$J$9:$J$188=B20)*(Sheet1!$E$9:$E $188="A325"),Sheet1!$C$9:$C$188)) In C32, copied to C34: =IF(OR(A32="",B32=""),"",SUMPRODUCT((Sheet1!$D$9:$ D$188=A32)*(Sheet1!$J$9:$J$188=B32)*(Sheet1!$E$9:$ E$188="SAE"),Sheet1!$C$9:$C$188)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Serge" wrote: On sheet one I have: Columns;C, D, E, J & Rows; 9, 10, 11, 12, 13, 14 as an example. Column: C is for quantities, D is for sizes, E is for grades, J is for length : C D E J ------------------------------------------ 9: 4 .625 SAE 1.50 10: 6 .625 SAE 1.50 11: 4 .500 A325 1.75 12: 6 .500 A325 1.75 13: 8 .500 SAE 1.50 14: 6 .75 A325 2.25 On sheet two (summary sheet) I have the following as example: Columns: A (size), B (lenght), C (quantities) & rows 20, 21, 22 for A325 and rows 32, 33, 34 for SAE. I would like to see a total quantity shown in column C if I type in size in column A and length in column B for each sizes of same grade & lenght. I get confused with SUMIF or SUMPRODUCT. Can someone un-confuse me with this problem please. Thank you in advance. Serge |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
Toppers: Thanks for the correction ! My error.
Serge: Glad to see you resolved it in your new post -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or SUMIF
Thanks for your reply Max.
Until next time. Serge "Max" wrote: Toppers: Thanks for the correction ! My error. Serge: Glad to see you resolved it in your new post -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif and sumproduct together | Excel Discussion (Misc queries) | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
SUMPRODUCT or SUMIF | Excel Worksheet Functions | |||
SUMPRODUCT or SUMIF or ... | Excel Worksheet Functions | |||
Sumif not Sumproduct | Excel Worksheet Functions |