Summary worksheet reference to detail worksheet

  • Thread starter Thread starter Quimera
  • Start date Start date
Q

Quimera

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.
 
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.
 
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)
 
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"
Click to expand...

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
 
I forgot to mention that the codes in the Detail sheet are
non-contiguous, if that makes a difference.

J.
 
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.
 
the range should be the maximum expected in the detail worksheet. Is that
right?
Click to expand...

Yes. Also, the arrays must be the same size. For example, these arrays are
not the same size so the formula will return an error:

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