Multiple levels of sumif?

[Peter Bellis]

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)

 

[Toppers]

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

 

[andy62]

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.

 

[Peter Bellis]

Thanks guys, appreciate the help.

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












- Show quoted text -