• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summing

S

sundeep

New Member
I have data in Column A i.e. A1 to A1000 now I want to sum two rows and need the value in Column B


So in B1 I have written =SUM(A1+A2) when I drag the formula to B2 its writing as =SUM(A2+A3) but in B2 I need =SUM(A3+A4)


How to make that ... Plzhelp
 
Hi Sundeep,


I assume you have data from A1 to A9 as follows:


1

2

3

4

5

6

7

8

9


Now create a helper column at Col C and write the following:


0

1

1

1

1

1

1

1

1

Now at B1 write the following and drag it down:

=SUM(OFFSET(A1,$C1,0),OFFSET(A2,$C1,0))


Meanwhile, you can use this. But I am looking for some better approach to do this.


Kaushik
 
kaushik Thanks a ton for the solution


Its working........


Like you, I am also waiting for some better approach
 
Hi Sundeep,


No need to create helper column...Here is the modified version of the formula..use it..


=SUM(OFFSET(A1,MOD(ROW($A$1),ROW($A1)),0),OFFSET(A2,MOD(ROW($A$1),ROW($A1)),0))


Hope this is fine..


Kaushik
 
@Kaushik & vallabha


Thanks for the reply guys......


but its not working......... Let me make myself clear one more time


For Ex

A1 to A10

1

2

3

4

5

6

7

8

9

10


Now in B1 the formula I had written is =sum(A1+A2) when I drag it to B2 excel is writing as =sum(A2+A3) but I need =sum(A3+A4)and so on


Guys if I am unable to understand your formula.... Plz explain me one more time bcoz I am beginner just learning ABC's of excel
 
Hi Sundeep,


Allow me to share my dumbest answer..

[pre]
Code: 
1	a1	a2
2	a3	a4
3	a5	a6
4	a7	a8
5	a9	a10
6	a11	a12
7	a13	a14
8	a15	a16
9	a17	a18
10	a19	a20[/pre]
In B1 write "A1" and in B2 write "A3", drag it toward bottom. It will automatically changes like A1,A3,A5.. and so on..


Same for C1.. A2,A4,A6... and so on..

Now in D1 write Formula.. =SUM(INDIRECT(B1),INDIRECT(C1))
and Double-click on the Right-Bottom-Joint of the cell..


Regards,

Deb..
 
Hi Deb


Thanks for the solution you have provided....... You have taught me a new idea (I am not aware of Indirect) Anyways its working..... But the probs is I have 400 Columns and 500 Rows


HOw can I apply this to all?? Anyway thanks for the solution
 
HI SUNDEEP


IT'S QUITE GOOD IF YOU UPLOAD A SAMPLE FILE WITH SAME 400 COLUMNS AND 500 ROWS


SO EASILY CAN SOLVE YOUR PROBLEM


WITH REGARDS


sp
 
Hi ,


I am not clear on your problem ; what I can think of is to enter the formula =A1+A2 in B1 , leave B2 blank , select B1 and B2 , and then drag down ; Excel will automatically insert the copied formula in cells B3 , B5 , B7 ,...


Narayan
 
Hi


I have data in Sheet1 from A1 to A500 and B1 to B500 and so on upto ABC1 to ABC 500......


Now in Sheet2 A1 I need =sum(sheet1!A1+sheet1!B1)


when I drag the formula from A1 to B1 in Sheet2 excel is writing the formula as =sum(sheet1!B1+sheet1!C1)........but I need =sum(sheet1!C1+sheet1!D1)


Narayan plz let me know whether I am clear to you
 
Hi Sundeep,


In Sheet2 (A1), write formula as.


Code: 
=SUM(INDEX(Sheet1!1:1,(COLUMN(A$1)*2)-1),INDEX(Sheet1!1:1,COLUMN(A$1)*2))

Drag it toward Left or down, and confirm if its working..


Regards,

Deb
 
Hi Sundeep,


I am not able to access your file due to restictions on my computer, have you tried my suggested solution.
 
Hi ,


Try this formula in Sheet2!B3 :


=SUM(OFFSET(Sheet1!$B5,0,(COLUMN(Sheet2!B3)-COLUMN(Sheet2!$B$3))*2,1,2))


Copy this across and down.


Narayan
 
Hi Sundeep,


Please enter this in Sheet2, Cell B3, drag to left and down:


Code: 
=SUM(INDIRECT(ADDRESS(ROW(A5),COLUMN()*2-2,,,"Sheet1")),INDIRECT(ADDRESS(ROW(A5),COLUMN()*2-1,,,"Sheet1")))


...hope that helps.


Faseeh
 
Ohhhhhh my GOD


I am very happy and over joyed to be in this forum and to meet you guys...... Here every one is kind hearted and happy to help......You guys have a magic wand with you.........Be it any problem in excel you will give solutions......... every one has their own unique solution........


I thank everyone by name who have given me solutions, who have replied to my problem and who have viewed my problem & put their effort to solve it


@ Deb .... It working dear .......Thanks for it


@ kchiba ...... its not working .....Maybe my knowledge is not upto mark to apply your formula.... Kindly could you explain the formula


@ Narayan ....As usual It working perfect ....Narayan could you plz explain me this formula has ""COLUMN(Sheet2!$B$3))*2,1,2))"" atlast... what does 1,2 indicate


@ Faseeh..... It working fine and Loved the avatar...... Faseeh could plz explain me the formula ...... I am just a beginner in excel.....Plz also explain why we had 3 commas in front of sheet2???


@ All Thanks you guys once again .......and I am confident that this forum creates more Ninjas as the present Ninjas (GURU'S of this forum) are happy to help and happy teach the beginners like me
 
Hi Sundeep!


Thanks for your those kind words.. These kind words are the only reward's which motivates us to help you better..


Regards,

Deb
 
Hi sundeep,


Thanks for your feedback. The two commas are part of ADDRESS() function. This has following syntex:


ADDRESS(row_num,column_num,abs_num,a1,sheet_text)


The last two commas are for abs_num & a1.


Abs_num Returns this type of reference i.e absolute or relative & A1 is a logical value that specifies the A1 or R1C1 reference style.


Hope that helps,


Faseeh
 
You must log in or register to reply here.
Back
Top