Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching and summing across sheets
Can someone help me out with a formula to produce the results in Column I in
the following example? Sheet Name: Master A B C D E F G H I 111 AAA Todd 2 Tim 4 1 222 ABB Tony 6 Todd 5 3 333 BBB Tim 8 Sue 3 Jim 7 11 Sheet Name: Todd 111 50 1 222 100 0 Sheet Name: Tim 111 100 0 333 50 4 Sheet Name: Tony 222 50 3 Sheet Name: Sue 333 100 0 Sheet Name: Jim 333 0 7 Basically, for a given row in the Master sheet, for each name in C, E, and G, go to the sheet with the same name, locate the row where A matches, find the value in C, and sum the values. Thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching and summing across sheets
Try...
I1, copied down: =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Todd","Tim","Tony ","Sue","Jim"}&"'!A1:A1 00"),A1,INDIRECT("'"&{"Todd","Tim","Tony","Sue","J im"}&"'!C1:C100"))) or =SUMPRODUCT(SUMIF(INDIRECT("'"&$M$1:$M$5&"'!A1:A10 0"),A1,INDIRECT("'"&$M$ 1:$M$5&"'!C1:C100"))) ....where M1:M5 contains the sheet names, Todd, Tim, Tony, etc. Hope this helps! In article , Todd Lietha wrote: Can someone help me out with a formula to produce the results in Column I in the following example? Sheet Name: Master A B C D E F G H I 111 AAA Todd 2 Tim 4 1 222 ABB Tony 6 Todd 5 3 333 BBB Tim 8 Sue 3 Jim 7 11 Sheet Name: Todd 111 50 1 222 100 0 Sheet Name: Tim 111 100 0 333 50 4 Sheet Name: Tony 222 50 3 Sheet Name: Sue 333 100 0 Sheet Name: Jim 333 0 7 Basically, for a given row in the Master sheet, for each name in C, E, and G, go to the sheet with the same name, locate the row where A matches, find the value in C, and sum the values. Thanks in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching and summing across sheets
Thank you! I used the INDIRECT version and it works wonderfully for me.
"Domenic" wrote: Try... I1, copied down: =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Todd","Tim","Tony ","Sue","Jim"}&"'!A1:A1 00"),A1,INDIRECT("'"&{"Todd","Tim","Tony","Sue","J im"}&"'!C1:C100"))) or =SUMPRODUCT(SUMIF(INDIRECT("'"&$M$1:$M$5&"'!A1:A10 0"),A1,INDIRECT("'"&$M$ 1:$M$5&"'!C1:C100"))) ....where M1:M5 contains the sheet names, Todd, Tim, Tony, etc. Hope this helps! In article , Todd Lietha wrote: Can someone help me out with a formula to produce the results in Column I in the following example? Sheet Name: Master A B C D E F G H I 111 AAA Todd 2 Tim 4 1 222 ABB Tony 6 Todd 5 3 333 BBB Tim 8 Sue 3 Jim 7 11 Sheet Name: Todd 111 50 1 222 100 0 Sheet Name: Tim 111 100 0 333 50 4 Sheet Name: Tony 222 50 3 Sheet Name: Sue 333 100 0 Sheet Name: Jim 333 0 7 Basically, for a given row in the Master sheet, for each name in C, E, and G, go to the sheet with the same name, locate the row where A matches, find the value in C, and sum the values. Thanks in advance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching and summing across sheets
I used your second suggestion in my prototype and it worked well as I had a
known fixed length list of names. But in production, the list of names (Todd, Tim, Tony, etc...) is in a separate sheet named Resources and I do not necessarily know the length of the list of names. The list starts in cell C5 and runs vertically to cell 45 (C5:C45). This week there might be 8 names on the list and next week there might be 10 or 12 names. If I use a range of C5:C45, I get a #REF error. But if I limit the range to only the populated cells (e.g. C5:C12 for eight names), it works fine. Do to the number of instances of the spreadhseet, it isn't practical to edit the formula each time the size of the list changes. Any ideas on how to overcome this? Thank you in advance. "Domenic" wrote: Try... I1, copied down: =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Todd","Tim","Tony ","Sue","Jim"}&"'!A1:A1 00"),A1,INDIRECT("'"&{"Todd","Tim","Tony","Sue","J im"}&"'!C1:C100"))) or =SUMPRODUCT(SUMIF(INDIRECT("'"&$M$1:$M$5&"'!A1:A10 0"),A1,INDIRECT("'"&$M$ 1:$M$5&"'!C1:C100"))) ....where M1:M5 contains the sheet names, Todd, Tim, Tony, etc. Hope this helps! In article , Todd Lietha wrote: Can someone help me out with a formula to produce the results in Column I in the following example? Sheet Name: Master A B C D E F G H I 111 AAA Todd 2 Tim 4 1 222 ABB Tony 6 Todd 5 3 333 BBB Tim 8 Sue 3 Jim 7 11 Sheet Name: Todd 111 50 1 222 100 0 Sheet Name: Tim 111 100 0 333 50 4 Sheet Name: Tony 222 50 3 Sheet Name: Sue 333 100 0 Sheet Name: Jim 333 0 7 Basically, for a given row in the Master sheet, for each name in C, E, and G, go to the sheet with the same name, locate the row where A matches, find the value in C, and sum the values. Thanks in advance! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
matching and summing across sheets
First of all, another referencing approach with this type of formula is to
use a named range, where the cell range containing the sheet names is assigned it's own name. So, if you selected C5 to C12 in the Resources Sheet, clicked in the name box (left of the formula bar), and typed in: List Then <Enter, You could revise your formula *FROM*: =SUMPRODUCT(SUMIF(INDIRECT("'"&Resources!C5:C12&"' !A1:A100"),A1,INDIRECT("'"&Resources!C5:C12&"'!C1: C100"))) *TO* =SUMPRODUCT(SUMIF(INDIRECT("'"&List&"'!A1:A100"),A 1,INDIRECT("'"&List&"'!C1:C100"))) BUT ... since your range size will change from time to time, you can create a *dynamic range*, and use that instead. From the Menu Bar, <Insert <Name <Define In the "Names In Workbook" box, type: Mstrlist Then, *change* whatever's in the "Refers To" box to: =OFFSET(Resources!$C$5,0,0,COUNTA(Resources!$C5:$C 45),1) Then <OK Now, starting in C5 of the Resources sheet, as you enter *OR* remove sheet names from the Mstrlist, this formula should work, without any revisions: =SUMPRODUCT(SUMIF(INDIRECT("'"&Mstrlist&"'!A1:A100 "),A1,INDIRECT("'"&Mstrlist&"'!C1:C100"))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Todd Lietha" wrote in message ... I used your second suggestion in my prototype and it worked well as I had a known fixed length list of names. But in production, the list of names (Todd, Tim, Tony, etc...) is in a separate sheet named Resources and I do not necessarily know the length of the list of names. The list starts in cell C5 and runs vertically to cell 45 (C5:C45). This week there might be 8 names on the list and next week there might be 10 or 12 names. If I use a range of C5:C45, I get a #REF error. But if I limit the range to only the populated cells (e.g. C5:C12 for eight names), it works fine. Do to the number of instances of the spreadhseet, it isn't practical to edit the formula each time the size of the list changes. Any ideas on how to overcome this? Thank you in advance. "Domenic" wrote: Try... I1, copied down: =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Todd","Tim","Tony ","Sue","Jim"}&"'!A1:A1 00"),A1,INDIRECT("'"&{"Todd","Tim","Tony","Sue","J im"}&"'!C1:C100"))) or =SUMPRODUCT(SUMIF(INDIRECT("'"&$M$1:$M$5&"'!A1:A10 0"),A1,INDIRECT("'"&$M$ 1:$M$5&"'!C1:C100"))) ....where M1:M5 contains the sheet names, Todd, Tim, Tony, etc. Hope this helps! In article , Todd Lietha wrote: Can someone help me out with a formula to produce the results in Column I in the following example? Sheet Name: Master A B C D E F G H I 111 AAA Todd 2 Tim 4 1 222 ABB Tony 6 Todd 5 3 333 BBB Tim 8 Sue 3 Jim 7 11 Sheet Name: Todd 111 50 1 222 100 0 Sheet Name: Tim 111 100 0 333 50 4 Sheet Name: Tony 222 50 3 Sheet Name: Sue 333 100 0 Sheet Name: Jim 333 0 7 Basically, for a given row in the Master sheet, for each name in C, E, and G, go to the sheet with the same name, locate the row where A matches, find the value in C, and sum the values. Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional summing across several sheets | Excel Worksheet Functions | |||
Matching two sheets | Excel Discussion (Misc queries) | |||
Summing accross sheets | Excel Worksheet Functions | |||
Summing across multiple sheets with a twist | Excel Worksheet Functions | |||
Summing Sheets - SUMIF ? | Excel Worksheet Functions |