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

Tanuvi 3 Posts Thursday October 4, 2012Registration date October 4, 2012 Last seen - Oct 4, 2012 at 02:04 AM - Latest reply: Tanuvi 3 Posts Thursday October 4, 2012Registration date October 4, 2012 Last seen
- Oct 4, 2012 at 04:59 AM
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
See more 

6 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Oct 4, 2012 at 03:40 AM
1
Thank you
extremely sorry for 2 mistakes

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

Thank you, venkat1926 1

Something to say? Add comment

CCM has helped 1814 users this month

Tanuvi 3 Posts Thursday October 4, 2012Registration date October 4, 2012 Last seen - Oct 4, 2012 at 04:58 AM
Thanks..
Best answer
Zohaib R 2421 Posts Sunday September 23, 2012Registration date July 16, 2018 Last seen - Oct 4, 2012 at 03:42 AM
1
Thank you
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.

Thank you, Zohaib R 1

Something to say? Add comment

CCM has helped 1814 users this month

Tanuvi 3 Posts Thursday October 4, 2012Registration date October 4, 2012 Last seen - Oct 4, 2012 at 04:59 AM
Hi Zohaib,

Thank you very much.. formula is very helpful..
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Oct 4, 2012 at 03:37 AM
0
Thank you
=SUMIF(A2:A11,">=1",B2:B11)-SUMIF(A2:A2,">=10",B2:B12)

mdoify this formula to suit you
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Oct 4, 2012 at 03:38 AM
0
Thank you
slight mistake

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