sum formula

hardeep.kanwar

hardeep.kanwar

Well-known Member
Joined
Aug 13, 2008
Messages
691
Hi!

Hello I am Trying to Get the Sum of Column A in Column B.

I want Column C like Formula. I try lots but couldn't get any Success.

Example.

Excel Workbook
ABCD
39Column AColumnBColumnC
40No.I want ThisSum Functions
411262
4220
433062
44
451552
4619
471852
48
4920571
5021
5125
52269
53236571
54
55215250
5621
5714250
58
59236516
60256
6124516
original Data
Cell Formulas
RangeFormula
D41=IF(A42 ="",SUM(A$40:A41) -SUM($D$40:D40),"")




Can anybody Help me.

Thanks in Advance
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:51px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; font-family:Verdana; ">No.</td><td style="font-weight:bold; font-family:Verdana; ">I want This</td><td style="font-family:Verdana; "> </td><td style="font-weight:bold; font-style:italic; font-family:Verdana; ">Sum Functions</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Verdana; text-align:right; ">12</td><td style="font-family:Verdana; text-align:right; ">62</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Verdana; text-align:right; ">20</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Verdana; text-align:right; ">30</td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; "> </td><td style="font-family:Verdana; text-align:right; ">62</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D4</td><td >=SUM(A2:A4)</td></tr></table></td></tr></table>
 
Upvote 0
Cell B40 must not contain a number or an error.

Enter this formula in B41 and copy down as needed:

=IF(AND(A41<>"",A42=""),SUM(A$41:A41)-SUM(B$40:B40),"")
 
Upvote 0
==================== EDITED =========================


Or perhaps.....
Code: 
Private Sub doit_click()
    tmp_sum = 0
    For x = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        If Cells(x, 1) <> "" Then
            tmp_sum = tmp_sum + Cells(x, 1)
        Else
            Cells(x, 2) = tmp_sum
            tmp_sum = 0
        End If
    Next    'x
End Sub
 
Last edited:
Upvote 0
#NAME? said:
Cell B40 must not contain a number or an error.

Enter this formula in B41 and copy down as needed:

=IF(AND(A41<>"",A42=""),SUM(A$41:A41)-SUM(B$40:B40),"")
Click to expand...


It works but same as function put in Column D

i want to Sum the Range after every Blank row and get the result in first Cell of the Another column.

Like This

Excel Workbook
AB
40No.I want This
411262
4220
4330
44
451552
4619
4718
48
4920571
5021
5125
52269
53236
54
55215250
5621
5714
58
59236516
60256
6124
original Data
 
Upvote 0
try this
Excel Workbook
AB
39No.IwantThis
401262
4120
4230
43
441552
4519
4618
47
4820571
4921
5025
51269
52236
53
54215250
5521
5614
57
58236516
59256
6024
Sheet1
Cell Formulas
RangeFormula
B40=IF(ISNUMBER(A39),"",SUM(A40:INDEX(A40:A$61,MATCH(TRUE,INDEX(A40:A$61="",0),0))))


BTW, here is the orginating thread

http://www.mrexcel.com/forum/showthread.php?t=401514
 
Upvote 0
Cell Formulas
RangeFormula
B40=IF(ISNUMBER(A39),"",SUM(A40:INDEX(A40:A$61,MATCH(TRUE,INDEX(A40:A$61="",0),0))))



Once again Sanrv1f Brilliant !!!!!!!!!!!!!
 
Upvote 0
phxsportz said:
Cell Formulas
RangeFormula
B40=IF(ISNUMBER(A39),"",SUM(A40:INDEX(A40:A$61,MATCH(TRUE,INDEX(A40:A$61="",0),0))))



Once again Sanrv1f Brilliant !!!!!!!!!!!!!
Click to expand...


Yes, You are right Phxsportz.

This is i am Looking for

thanks All of U, Especially Sanrv1f
 
Upvote 0
Another slight variation that may suit. Just ensure the '$100' parts of the formula point to a cell below where the last data is or could be. Formula then copied down.

Excel Workbook
AB
40No.I*want*This
411262
4220
4330
44
451552
4619
4718
48
4920571
5021
5125
52269
53236
54
55215250
5621
5714
58
59236516
60256
6124
62
63
Sums
 
Upvote 0
You must log in or register to reply here.

Similar threads

MrDB4Excel
  • Solved
Disregard Negative Results
Replies
3
Views
165
Joe4
Joe4
C
  • Question
Daily Run data (copy data in loop by daily in worksheet)
Replies
3
Views
244
dermie_72
D
S
  • Question
Sum Subtotals Based on numbered subsections
2
Replies
11
Views
249
sameri
S
J
  • Solved
Formula Help
2 3
Replies
20
Views
280
Fluff
Fluff
M
  • Solved
I can't sum up hours anymore
Replies
7
Views
119
awoohaw
awoohaw

Forum statistics

Threads
1,217,078
Messages
6,134,430
Members
449,872
Latest member
Big Jake

Share this page

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