Excel Complex Summing

[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

 

[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.

 

[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.

 

[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.