Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple levels of sumif?
Hi All, I hope someone can help me.
I have been asked to take a matrix of source data (see "Source Data" below) With the values in Column A repeating themselves (possibly hundreds of times), and the values in Row 1 being unique, but belonging to specific groups (see "Source Group" below), and to collapse this matrix into a Summary matrix with all the values in column A only appearing once, and all the Row 1 Groups only appearing once (see "Desired Result" below), with the rows & columns transposed. I have solved this by a 2 step Sumif, one vertical, and one horisontal, step one being the "Intermediate Step" below, and Step 2 being the "Desired Result". I realise that this can be done with pivot tables, but the user is not happy with that. They want to be able to open a spreadsheet, paste the source data, and then print out the result, with no further interaction.. I have the feeling that there must be another single-step, elegant solution to this, but I cannot think of it. I would appreciate any input. Thanks, Peter Source Data A1001 B200 AB2 ADDE FE A 1 2 3 4 5 B 2 3 4 5 6 C 3 4 5 6 7 D 4 5 6 7 8 E 5 6 7 8 9 F 6 7 8 9 10 G 7 8 9 10 11 A 8 9 10 11 12 A 9 10 11 12 13 D 10 11 12 13 14 D 11 12 13 14 15 E 12 13 14 15 16 Source Group A1001 A10 B200 B200 AB2 A10 ADDE AD FE AD Intermediate Step (VLookup on Source - Group) A10 B200 A10 AD AD A 18 21 24 27 30 B 2 3 4 5 6 C 3 4 5 6 7 D 25 28 31 34 37 E 17 19 21 23 25 F 6 7 8 9 10 (Sumifs vertically on =A, etc) Desired result A B C D E F A10 42 6 8 56 38 14 AD 57 11 13 71 48 19 B200 21 3 4 28 19 7 (Sumifs horisontally & transpose) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple levels of sumif?
one way:
Add a VLOOKUP line to convert the Source/Group A1001 B200 AB2 ADDE FE A10 B200 A10 AD AD '<=== Vlookup A 1 2 3 4 5 B 2 3 4 5 6 C 3 4 5 6 7 D 4 5 6 7 8 E 5 6 7 8 9 F 6 7 8 9 10 G 7 8 9 10 11 A 8 9 10 11 12 A 9 10 11 12 13 D 10 11 12 13 14 D 11 12 13 14 15 E 12 13 14 15 16 Asume table above is A1:F14 My results table was I1 to O3 A B C D E F A10 42 6 8 56 38 14 AD 57 11 13 71 48 19 B200 21 3 4 28 19 7 in J2: =SUMPRODUCT(--($B$2:$F$2=$I2)*($A$3:$A$14=J$1)*$B$3:$F$14) Copy across and down HTH "Peter Bellis" wrote: Hi All, I hope someone can help me. I have been asked to take a matrix of source data (see "Source Data" below) With the values in Column A repeating themselves (possibly hundreds of times), and the values in Row 1 being unique, but belonging to specific groups (see "Source Group" below), and to collapse this matrix into a Summary matrix with all the values in column A only appearing once, and all the Row 1 Groups only appearing once (see "Desired Result" below), with the rows & columns transposed. I have solved this by a 2 step Sumif, one vertical, and one horisontal, step one being the "Intermediate Step" below, and Step 2 being the "Desired Result". I realise that this can be done with pivot tables, but the user is not happy with that. They want to be able to open a spreadsheet, paste the source data, and then print out the result, with no further interaction.. I have the feeling that there must be another single-step, elegant solution to this, but I cannot think of it. I would appreciate any input. Thanks, Peter Source Data A1001 B200 AB2 ADDE FE A 1 2 3 4 5 B 2 3 4 5 6 C 3 4 5 6 7 D 4 5 6 7 8 E 5 6 7 8 9 F 6 7 8 9 10 G 7 8 9 10 11 A 8 9 10 11 12 A 9 10 11 12 13 D 10 11 12 13 14 D 11 12 13 14 15 E 12 13 14 15 16 Source Group A1001 A10 B200 B200 AB2 A10 ADDE AD FE AD Intermediate Step (VLookup on Source - Group) A10 B200 A10 AD AD A 18 21 24 27 30 B 2 3 4 5 6 C 3 4 5 6 7 D 25 28 31 34 37 E 17 19 21 23 25 F 6 7 8 9 10 (Sumifs vertically on =A, etc) Desired result A B C D E F A10 42 6 8 56 38 14 AD 57 11 13 71 48 19 B200 21 3 4 28 19 7 (Sumifs horisontally & transpose) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple levels of sumif?
Another way, assuming the souce data structure never changes (and with source
data in cells A1:F13): Set up the Desired Solution range as follows: A B C D E F A10 AD B200 In the 3 cells under the "A" column put in the following formulas respectively: =SUMIF($A2:$A13,B23,$B2:$B13)+SUMIF($A2:$A13,B23,$ D2:$D13) =SUMIF($A2:$A13,B23,$E2:$E13)+SUMIF($A2:$A13,B23,$ F2:$F13) =SUMIF($A2:$A13,B23,$C2:$C13) Copy those formulas across horizontally to fill the Desired Solution table. "Toppers" wrote: one way: Add a VLOOKUP line to convert the Source/Group A1001 B200 AB2 ADDE FE A10 B200 A10 AD AD '<=== Vlookup A 1 2 3 4 5 B 2 3 4 5 6 C 3 4 5 6 7 D 4 5 6 7 8 E 5 6 7 8 9 F 6 7 8 9 10 G 7 8 9 10 11 A 8 9 10 11 12 A 9 10 11 12 13 D 10 11 12 13 14 D 11 12 13 14 15 E 12 13 14 15 16 Asume table above is A1:F14 My results table was I1 to O3 A B C D E F A10 42 6 8 56 38 14 AD 57 11 13 71 48 19 B200 21 3 4 28 19 7 in J2: =SUMPRODUCT(--($B$2:$F$2=$I2)*($A$3:$A$14=J$1)*$B$3:$F$14) Copy across and down HTH "Peter Bellis" wrote: Hi All, I hope someone can help me. I have been asked to take a matrix of source data (see "Source Data" below) With the values in Column A repeating themselves (possibly hundreds of times), and the values in Row 1 being unique, but belonging to specific groups (see "Source Group" below), and to collapse this matrix into a Summary matrix with all the values in column A only appearing once, and all the Row 1 Groups only appearing once (see "Desired Result" below), with the rows & columns transposed. I have solved this by a 2 step Sumif, one vertical, and one horisontal, step one being the "Intermediate Step" below, and Step 2 being the "Desired Result". I realise that this can be done with pivot tables, but the user is not happy with that. They want to be able to open a spreadsheet, paste the source data, and then print out the result, with no further interaction.. I have the feeling that there must be another single-step, elegant solution to this, but I cannot think of it. I would appreciate any input. Thanks, Peter Source Data A1001 B200 AB2 ADDE FE A 1 2 3 4 5 B 2 3 4 5 6 C 3 4 5 6 7 D 4 5 6 7 8 E 5 6 7 8 9 F 6 7 8 9 10 G 7 8 9 10 11 A 8 9 10 11 12 A 9 10 11 12 13 D 10 11 12 13 14 D 11 12 13 14 15 E 12 13 14 15 16 Source Group A1001 A10 B200 B200 AB2 A10 ADDE AD FE AD Intermediate Step (VLookup on Source - Group) A10 B200 A10 AD AD A 18 21 24 27 30 B 2 3 4 5 6 C 3 4 5 6 7 D 25 28 31 34 37 E 17 19 21 23 25 F 6 7 8 9 10 (Sumifs vertically on =A, etc) Desired result A B C D E F A10 42 6 8 56 38 14 AD 57 11 13 71 48 19 B200 21 3 4 28 19 7 (Sumifs horisontally & transpose) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple levels of sumif?
Thanks guys, appreciate the help.
On Aug 20, 6:14 pm, Toppers wrote: one way: Add a VLOOKUP line to convert the Source/Group A1001 B200 AB2 ADDE FE A10 B200 A10 AD AD '<=== Vlookup A 1 2 3 4 5 B 2 3 4 5 6 C 3 4 5 6 7 D 4 5 6 7 8 E 5 6 7 8 9 F 6 7 8 9 10 G 7 8 9 10 11 A 8 9 10 11 12 A 9 10 11 12 13 D 10 11 12 13 14 D 11 12 13 14 15 E 12 13 14 15 16 Asume table above is A1:F14 My results table was I1 to O3 A B C D E F A10 42 6 8 56 38 14 AD 57 11 13 71 48 19 B200 21 3 4 28 19 7 in J2: =SUMPRODUCT(--($B$2:$F$2=$I2)*($A$3:$A$14=J$1)*$B$3:$F$14) Copy across and down HTH "Peter Bellis" wrote: Hi All, I hope someone can help me. I have been asked to take a matrix of source data (see "Source Data" below) With the values in Column A repeating themselves (possibly hundreds of times), and the values in Row 1 being unique, but belonging to specific groups (see "Source Group" below), and to collapse this matrix into a Summary matrix with all the values in column A only appearing once, and all the Row 1 Groups only appearing once (see "Desired Result" below), with the rows & columns transposed. I have solved this by a 2 step Sumif, one vertical, and one horisontal, step one being the "Intermediate Step" below, and Step 2 being the "Desired Result". I realise that this can be done with pivot tables, but the user is not happy with that. They want to be able to open a spreadsheet, paste the source data, and then print out the result, with no further interaction.. I have the feeling that there must be another single-step, elegant solution to this, but I cannot think of it. I would appreciate any input. Thanks, Peter Source Data A1001 B200 AB2 ADDE FE A 1 2 3 4 5 B 2 3 4 5 6 C 3 4 5 6 7 D 4 5 6 7 8 E 5 6 7 8 9 F 6 7 8 9 10 G 7 8 9 10 11 A 8 9 10 11 12 A 9 10 11 12 13 D 10 11 12 13 14 D 11 12 13 14 15 E 12 13 14 15 16 Source Group A1001 A10 B200 B200 AB2 A10 ADDE AD FE AD Intermediate Step (VLookup on Source - Group) A10 B200 A10 AD AD A 18 21 24 27 30 B 2 3 4 5 6 C 3 4 5 6 7 D 25 28 31 34 37 E 17 19 21 23 25 F 6 7 8 9 10 (Sumifs vertically on =A, etc) Desired result A B C D E F A10 42 6 8 56 38 14 AD 57 11 13 71 48 19 B200 21 3 4 28 19 7 (Sumifs horisontally & transpose)- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If, Then formula - Multiple levels | Excel Worksheet Functions | |||
Multiple Subtotal levels in Excel 2003 | Excel Worksheet Functions | |||
sum sub levels. | Excel Worksheet Functions | |||
How do I create a formula for multiple levels of discounts? | Excel Worksheet Functions | |||
How do I get Multiple subtotal levels in EXCEL? | Excel Discussion (Misc queries) |