SumProduct

[judith]

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

 

[RagDyer]

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)

 

[Bob Umlas]

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

 

[Steve]

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