If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SumProduct
I know that there are loads of similar questions posted but I just dont seem
to be able to figure this out. I am trying to use =SUMPRODUCT('order details'!$E$2:$E$1584=Summary!$E11,'order details'!$B$2:$B$1584=Summary!G$6,'order details'!F2:F1584) I highlighted range Summary!G11:Summary!G1573 Typed in the above Pressed Ctrl + Shift + enter What i want it to do in cell Summary!G11 On my order Details sheet I have an array where I want to pick out the value from column F if Column E matches the value in Summary!E11 and Column B matches the value in Summary!G$6. I want the value Summary!E11to move down the page with the array formula but Summary!G$6 will remain static Any suggestions please |
#2
|
|||
|
|||
SumProduct
Your formula looked pretty close, except for the parenthesis and operator.
Try this: =SUMPRODUCT(('order details'!E$2:E$1584=Summary!E11)*('order details'!B$2:B$1584=Summary!G$6),'order details'!F$2:F$1584) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "judith" wrote in message ... I know that there are loads of similar questions posted but I just dont seem to be able to figure this out. I am trying to use =SUMPRODUCT('order details'!$E$2:$E$1584=Summary!$E11,'order details'!$B$2:$B$1584=Summary!G$6,'order details'!F2:F1584) I highlighted range Summary!G11:Summary!G1573 Typed in the above Pressed Ctrl + Shift + enter What i want it to do in cell Summary!G11 On my order Details sheet I have an array where I want to pick out the value from column F if Column E matches the value in Summary!E11 and Column B matches the value in Summary!G$6. I want the value Summary!E11to move down the page with the array formula but Summary!G$6 will remain static Any suggestions please |
#3
|
|||
|
|||
SumProduct
First, highlight only cell G11, enter the corrected formula, then fill down.
(not necessary to ctrl/shift/enter) =SUMPRODUCT(N('order details'!$E$2:$E$1584=Summary!$E11),N('order details'!$B$2:$B$1584=Summary!G$6),'order details'!F2:F1584) Bob Umlas Excel MVP "judith" wrote in message ... I know that there are loads of similar questions posted but I just dont seem to be able to figure this out. I am trying to use =SUMPRODUCT('order details'!$E$2:$E$1584=Summary!$E11,'order details'!$B$2:$B$1584=Summary!G$6,'order details'!F2:F1584) I highlighted range Summary!G11:Summary!G1573 Typed in the above Pressed Ctrl + Shift + enter What i want it to do in cell Summary!G11 On my order Details sheet I have an array where I want to pick out the value from column F if Column E matches the value in Summary!E11 and Column B matches the value in Summary!G$6. I want the value Summary!E11to move down the page with the array formula but Summary!G$6 will remain static Any suggestions please |
#4
|
|||
|
|||
SumProduct
Hi Judith.
By the looks of it you're trying to find every instance of summary!$E11 from column E on the orderdetails worksheet, as well as finding every instance of summary!G$6 on the order details worksheet column B. Then, where both instances are true, you want to sum the total, from column F. However, you've not made your last array static. It'll flow with your drag down. Is this correct? Assuming correct, you've then repeated that identical equation all the way down column G. I for one have never gotten sumproduct to work with just the commas separating the arrays-- as you're using it. I've always had to use-- sumproduct((array1=criteria1)*(array2=criteria2)*( array3)) Also, for your criteria-- summary!G$6, and summary!$E11, try the following- =SUMPRODUCT(('order details'!$E$2:$E$1584&""=Summary!$E11&"")*('order details'!$B$2:$B$1584&""=Summary!G$6&"")*('order details'!F$2:F$1584)) Notice the &"" after each of your two criteria arrays. Use these only if you have numeric values that could be differing data types. The way Harlan Grove explained it to me near three years ago was that they are data-type nullifiers. These are only necessary if you're working across worksheets-- which in this case it appears you are. Also notice that you did not make your last array cells static. When you drag your equation down, the start and end cells will change accordingly, unless you place the $ before the row numbers. Hope this helps. If not, let me know. Best, Steve "judith" wrote: I know that there are loads of similar questions posted but I just dont seem to be able to figure this out. I am trying to use =SUMPRODUCT('order details'!$E$2:$E$1584=Summary!$E11,'order details'!$B$2:$B$1584=Summary!G$6,'order details'!F2:F1584) I highlighted range Summary!G11:Summary!G1573 Typed in the above Pressed Ctrl + Shift + enter What i want it to do in cell Summary!G11 On my order Details sheet I have an array where I want to pick out the value from column F if Column E matches the value in Summary!E11 and Column B matches the value in Summary!G$6. I want the value Summary!E11to move down the page with the array formula but Summary!G$6 will remain static Any suggestions please |
Thread Tools | |
Display Modes | |
|
|