SUM for Row with Variable Column Units

alexb523

Board Regular
Joined
Dec 12, 2013
Messages
115
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.

lettertime123456
a42100021000300009000600015000
b2650
c5400400400400

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


letterSUM in Time
a81000
b0
c1600

<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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
Try in B7 and copy down:
Code:
=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

Board Regular
Joined
Dec 12, 2013
Messages
115
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,804
Messages
5,598,140
Members
414,214
Latest member
marketingnumbersguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top