Add range if number is 1 to 10, 11-21,22-30 ...and so [Solved/Closed]

Report
Posts
3
Registration date
Thursday October 4, 2012
Status
Member
Last seen
October 4, 2012
-
Posts
3
Registration date
Thursday October 4, 2012
Status
Member
Last seen
October 4, 2012
-
Hi ,

I am new to this forum. Looking for small help..


if 1 to 10 is in column A i want to add the numbers which are column B.

A B
1 34
2 22
3 12
4 14
5 14
6 56
7 67
8 68
9 45
10 50

if range in column A is 1-10 what is the sum in column B, if range in column A is 11 - 20 what is the sum in column B.

Please suggest some simple formula.

Thanks

4 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
extremely sorry for 2 mistakes

=SUMIF(A2:A11,">=1",B2:B11)-SUMIF(A2:A11,">10",B2:B111)
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
3
Registration date
Thursday October 4, 2012
Status
Member
Last seen
October 4, 2012

Thanks..
Posts
2401
Registration date
Sunday September 23, 2012
Status
Moderator
Last seen
December 13, 2018
543
Hi Tanuvi,

There is a worksheet function in Microsoft Excel that can be used. Below formula will fetch you the desired results:

SUM(INDIRECT("B"&C2&":B"&D2))

Follow the step by step instructions mentioned below to use the above formula:

1. Enter the values 1, 2, 3 ... in the cells from A1 to A10.
2. Enter the other set of values 34, 22, 12 ... in the cells from B1 to B10.
3. Enter the labels in the cells as mentioned below:
C1 - Range Start
D1 - Range End
E1 - Range Sum
4. In E2 enter the above Formula.
5. If your range starts at A1 and ends at A5, enter 1 in C2 and 5 in D2, your desired result will be displayed in E2.

You can read more about this formula from the following link:

http://www.cpearson.com/Excel/indirect.htm

Please revert for clarification.

1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
3
Registration date
Thursday October 4, 2012
Status
Member
Last seen
October 4, 2012

Hi Zohaib,

Thank you very much.. formula is very helpful..
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
=SUMIF(A2:A11,">=1",B2:B11)-SUMIF(A2:A2,">=10",B2:B12)

mdoify this formula to suit you
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
slight mistake

=SUMIF(A2:A11,">=1",B2:B11)-SUMIF(A2:A2,">10",B2:B12)