SUMPRODUCT or INDEX/MATCH When Summing for Certain Criteria

[alexb523]

Hello Board Users,

I am trying to SUM everything that is the same letter and under a certain column count. The formula i am using seems to work when the "columns" are in asending order, but I would like to modify it to only Sum the columns below a certain count.

Here is the Formula i am currently using:

=SUMPRODUCT($B$2:$G$9*($A$2:$A$9=$A12)*($B$1:$G$1<=$B12))

The Table below starts in "A1". The SUM field is what I would like the out come to be.
For example, letter "a" should SUM every row from the input table that is "a" and less than the 3rd column of the table. Right now, it SUMS everything that is "a" row that is less than the Num.

Is this possilbe? Or will i have to adjust my table?

Thanks in advance. Really appreciate anybody's help.

alexb523

 

[alexb523]

Sorry! Below is the table!

	10	11	12	1	2	3
a	1	2	3	4	5	6
a	3	6	9	12	15	18
b	9	18	27	36	45	54
b	27	54	81	108	135	162
c	81	162	243	324	405	486
c	243	486	729	972	1215	1458
d	729	1458	2187	2916	3645	4374
a	2187	4374	6561	8748	10935	13122
Letter	Num	Should SUM	NOW
a	12	13146	46011
b	11	108	648
c	2	4860	2916
d	1	2916	2916

<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Jonmo1]

Try

=SUMPRODUCT($B$2:INDEX($B$9:$G$9,MATCH(B12,$B$1:$G$1,0))*($A$2:$A$9=$A12))

 

[alexb523]

Thanks Jonmo1! That is slick.

Let me try to understand this:

SUMPRODUCT($B$2:INDEX($B$9:$G$9 --> that gets you your "MAX" range you would SUM(B2:G9)

Then the match tells you how many columns should be in that range (B2:NumColumns in Row 9) to look up based on the "Num" Column and what row numbers to SUM from the letter.

Correct?

Why is it not a Array Formula? I feel like that is usually the case with "MATCH(X*Y)" criteria.

Thanks Again!

 

[Jonmo1]

That's about right.

The match isn't actually used as part of the X*Y calculation.
It's only purpose is to determine the ending column of the first range.

=SUMPRODUCT($B$2:INDEX($B$9:$G$9,MATCH(B12,$B$1:$G$1,0))*($A$2:$A$9=$A12))

Say the value of B12 was found in E1 for example
MATCH(B12,$B$1:$G$1,0) = 4

=SUMPRODUCT($B$2:INDEX($B$9:$G$9,4)*($A$2:$A$9=$A12))

INDEX($B$9:$G$9,4) = E9 (E is the 4th column in B9:G9)

=SUMPRODUCT($B$2:$E$9*($A$2:$A$9=$A12))

From there, it works the same as your original formula (but now removed the < B12 criteria).

 

[alexb523]

Hello Junmo,

I use this function all time now. It is very useful in my industry/position in finding the LT usage.

I am wondering, how will this be effected but a Table? will indexing the last row change if the size/length of the table changes?

Please see my table question here.