Summary worksheet reference to detail worksheet
 

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.

Summary worksheet reference to detail worksheet
 

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.

Summary worksheet reference to detail worksheet
 

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.

Summary worksheet reference to detail worksheet
 

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.

Summary worksheet reference to detail worksheet
 

I forgot to mention that the codes in the Detail sheet are
non-contiguous, if that makes a difference.

J.


"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.

Summary worksheet reference to detail worksheet
 

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.

Summary worksheet reference to detail worksheet
 

the range should be the maximum expected in the detail worksheet. Is that
right?

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)


--
Biff
Microsoft Excel MVP


"Quimera" wrote in message
news:hYPEi.25310$bO6.4479@edtnps89...
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.