Add range if number is 1 to 10, 11-21,22-30 ...and so

[Tanuvi]

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

 

[Krishnakumar]

Hi,


put 10 in D1


In D2 and copied down,


=ROWS($D$2:D2)*$D$1-$D$1+1 & " - " &ROWS($D$2:D2)*$D$1


In E2 and copied down,


=SUMPRODUCT(--($A$2:$A$100>=--LEFT(D2,FIND("-",D2)-2)),--($A$2:$A$100<=--MID(D2,FIND("-",D2)+1,5)),$B$2:$B$100)


Kris


adjust the range

 

[kaushik03]

Hi Tanuvi,


Welcome to this forum and we glad to have you here.


Say you have data as follows in column A and B:


Number Data

1 10

2 20

3 55

17 22

18 14

19 13

7 12

8 18

9 57

10 18

11 16

12 19

13 12

14 1

15 14

16 17

4 18

5 11

6 16

20 14


Now the formula to sum Col B for the range 1-10 wouldbe:


=SUMIFS(B:B,A:A,">=1",A:A,"<=10")...this will return you 235


You can keep the criteria ranges in cells and pass the same in the formula


Hope this helps..


Kaushik

 

[kaushik03]

Good one Kris...