To the genius...

Success! The first try didn't balance because the range to 10 was too
small. If I understand it correctly, the range should be the maximum
expected in the detail worksheet. Is that right?

J.



"T. Valko" wrote in message
...
If you tried using entire columns as range references in the
SUMPRODUCT formula it won't work unless you're using Excel 2007. If
you're not using Excel 2007 you must use less than the entire column
as a range reference.

What I wouldn't give for a Boolean "and"

In a SUMPRODUCT function (and many others), "and" is achieved by
multiplying 2 or more arrays. That's exactly how SUMPRODUCT works, it
multiplies the arrays you specify as arguments.

=SUMPRODUCT(--(Detail!A$2:A$10=A2),--(Detail!D$2:D$10=B2),Detail!F$2:F$10)

Translated:

if A = A2 *and* D = B2 sum F


--
Biff
Microsoft Excel MVP


"Quimera" wrote in message
news:uOKEi.25132$bO6.14999@edtnps89...
Sorry, I tried but it didn't compute.

I have another summary sheet that summarizes by Item # only, and this
formula works fine.(Week1! is the detail worksheet)

=SUMIF(Week1!$D:$D,$A2,Week1!$F:$F) where D is the Item # column
and F is the quantity column on the Week1 worksheet.

Is there was some way I could code $A:$A (and) $D:$D, A2 (and) B2,
Week1!$F:$F) to match the Division and Item # on both sheets? (What
I wouldn't give for a Boolean "and" )!

J.






D:$D,$A2,Week1!$F:$F)
"T. Valko" wrote in message
...
Maybe something like this entered on your Summary sheet in column D:

=SUMPRODUCT(--(Detail!A$2:A$10=A2),--(Detail!D$2:D$10=B2),Detail!F$2:F$10)

Copy down if needed.

--
Biff
Microsoft Excel MVP


"Quimera" wrote in message
news:xrIEi.25460$Pd4.12841@edtnps82...
I need to summarize by Division/Item # but I don't know how to refer
to the concatenatation (?) of the two fields on the Summary sheet.

Detail worksheet:
- Column A = Division Column D = Item # Column F = Quantity

Summary worksheet
- Column A = Division Column B - Item # Column D = Summary
Quantity

Can someone help me with this? Sorry I'm still learning, but the
last help I asked for (LOOKUP) - worked like a charm, so I am
emboldened to ask one more question.

Thanks.