SUM for Row with Variable Column Units

[alexb523]

Hello,

I am trying to SUM the values that are lower than "time" in the below example. In other words, I would like letter "a" to be summed for anything less than or equal to the "4" in the time column.

"SUM in Time" is the solution column. "Letter" is in A1.

letter	time	1	2	3	4	5	6
a	4	21000	21000	30000	9000	6000	15000
b	2			650
c	5	400	400		400	400

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

letter	SUM in Time
a	81000
b	0
c	1600

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

I have tried SUMIFS with some embedded index-match functions but they are not logical. I know the function has to "call out" how many columns to add in a row that matches the the row but am getting stumped.

Hope you can help. Thanks in advance!
-Alex

 

[MarcelBeug]

Try in B7 and copy down:

=SUMPRODUCT($C$2:$H$4*($A$2:$A$4=$A7)*($C$1:$H$1<=$B$2:$B$4))

where the letter you are looking for is in $A7 (and down).

 

[alexb523]

Thanks. Works Great!

I have modified it to only look at the current reference "time" in $B2

=SUMPRODUCT($C$2:$H$4*($A$2:$A$4=$A7)*($C$1:$H$1<=$B2))