# Excel Complex Summing

J

#### jpr.charron

I have a range starting in Row 4, made of one row of values followed by one row of codes.
This pattern repeats itself in the following rows. Last filled Row # is not known
In Row # 1 thru 3 I am trying to sum each Colum values by Code
At this point I am searching how I could use a combination of INDIRECT, MATCH & OFFSET.
A sample of the range B4:E9 I have to sum is displayed below.
Help appreciated.
Celeste
Col A Col B Col C Col D Col E
Row 1 e 0 650 400 200
Row 2 k 600 0 100 300
Row 3 m 500 100 100 200
..............................................
Row 4 Value 200 150 100 300
Row 5 Code m e k k
.... Value 600 500 100 200
Code k e m m
Value 300 100 400 200
Code m m e e

C

#### Claus Busch

Hi Celeste,

Am Sat, 26 Jul 2014 17:39:53 -0700 (PDT) schrieb (e-mail address removed):
Col A Col B Col C Col D Col E
Row 1 e 0 650 400 200
Row 2 k 600 0 100 300
Row 3 m 500 100 100 200
.............................................
Row 4 Value 200 150 100 300
Row 5 Code m e k k
... Value 600 500 100 200
Code k e m m
Value 300 100 400 200
Code m m e e

in B2 try:
=SUMPRODUCT(--(B\$5:B\$1001=\$A1),B\$4:B\$1000)
and copy to the right and down.

Regards
Claus B.

J

#### jpr.charron

Thank you Claus, a thousand times,
Truly amazing, I could not have come up with this answer.
Which means, by the way, I will have to revisit and study SUMPRODUCT.
Have a good day from Pennsylvania.

C

#### Claus Busch

Hi Celeste,

Am Sun, 27 Jul 2014 03:24:40 -0700 (PDT) schrieb (e-mail address removed):
Which means, by the way, I will have to revisit and study SUMPRODUCT.

you can also use SUMIF:

=SUMIF(B\$5:B\$1001,\$A1,B\$4:B\$1000)

Regards
Claus B.