Help please sum formula to sum based on conditions

mina91709

New Member
Joined
Mar 25, 2017
Messages
25
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]OIGINAL AMOUNT[/TD]
[TD]REV1[/TD]
[TD]REV2[/TD]
[TD]REV3[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]150[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]300[/TD]
[TD]800[/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]150[/TD]
[TD]30[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]60[/TD]
[TD]30[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

I need a formula to sum all based on the latest value entered from the example above it will pick up
200 for row a
50 for row b
300 for row c
50 for row d
50 for row e
7 for row f
total should be 657

thank you very much
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi Mina,

With your data in range a1:e7, use below formula in F2 and drag down:-

=INDEX($A2:$E2,0,MAX(IF($B2:$E2<>"",COLUMN($B$2:$E$2),"")))

Confirm with key combination ctrl shift enter, instead of just enter.

Now you can sum the column F to get your desired result.

Regards,
DILIPandey
 
Upvote 0
Try if this works for you
Excel Workbook
ABCDE
1*Original amtRev1Rev2Rev3
2a50100150200
3b10050**
4c300800300*
5d1503050*
6e603050*
7f11587
8*****
9*****
10*Total of latest value657**
Sheet
 
Upvote 0
Hi Mina,
For me it is working, post your file over a public portal and provide the link here.

Regards,
DILIPandey
 
Upvote 0
mina,

if, by chance, you think of applying my my formula
please use this one as earlier one had a small mistake in reference.
=LOOKUP(9.99E+307,B2:E2)+LOOKUP(9.99E+307,B3:E3)+LOOKUP(9.99E+307,B4:E4)+LOOKUP(9.99E+307,B5:E5)+LOOKUP(9.99E+307,B6:E6) +LOOKUP(9.99E+307,B7:E7)
 
Upvote 0
[TABLE="class: grid, width: 588"]
<tbody>[TR]
[TD][/TD]
[TD]OIGINAL AMOUNT[/TD]
[TD]REV1[/TD]
[TD]REV2[/TD]
[TD]REV3[/TD]
[TD]last value[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]150[/TD]
[TD]200[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]300[/TD]
[TD]800[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]150[/TD]
[TD]30[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]60[/TD]
[TD]30[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]


In F2 enter and copy down:

=LOOKUP(9.99999999999999E+307,B2:E2)

Then run:

=SUM(F:F)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top