| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I show or hide detail for a group in a protected worksheet | Excel Discussion (Misc queries) | |||
| summary data sheet from worksheet to worksheet | Excel Worksheet Functions | |||
| Link worksheet totals to a summary worksheet in the same workbook | Excel Worksheet Functions | |||
| Summarize multiple worksheet detail on summary sheet | Excel Discussion (Misc queries) | |||
| Protected worksheet, showing and hiding Grouped detail | Excel Discussion (Misc queries) | |||
Linear Mode
