Excel Total Range of Values

Solved/Closed
-
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
-
Hello,
I'm trying to get a range of values totalled.
I have one set of columns, lets say column A1 to A200, ranging from -30 up to 1000 and another column adjeacent (lets say B1 to B200) with figures I need totalling
I want a formula to look at the first column A between the values-30 (MINUS 30) and 0 (ZERO) return the adjacent column B with a sum of what it found
Then following this in the next cell down I want it to look at the same criteria and show values between 1 and 30 and so on for selected ranges in column A

Can anyone help please? Thank you

5 replies

Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
160
For between 0 to -30 (including -30)

=SUMPRODUCT((A1:A200<=0)*(A1:A200>=-30)*A1:A200)

For 0 to 30 (including 30)

=SUMPRODUCT((A1:A200>=0)*(A1:A200<=30)*A1:A200)
Thank you for your response, however I'm nooking to get a total of this column, what I want it to do is look at these areas -100 to 0, 1-30, 31-60, 61-90, 91-120 and over 121
Once it has looked at these it need to return the value in cell B but add those together
-5 100.05
10 200.1
-2 300.15
29 400.2
150 500.25
190 600.3
66 700.35
67 800.4

Therefor -100 to 0 would equal 400.20
1 to 30 would equal 600.30
31to 60 would equal 0.00
61 to 90 would equal 1500.75
91 to 120 would equal 0.00
over 121 would equal 1100.55

Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
160
Just use below formula ... for 0 to -100 which will add column B ......

=SUMPRODUCT((A1:A200<=0)*(A1:A200>=-100)*(B1:B200))

91 to 120

=SUMPRODUCT((\$A\$1:\$A\$200<=120)*(\$A\$1:\$A\$200>=91)*(\$B\$1:\$B\$200))

Over 120 ....

=SUMPRODUCT((\$A\$1:\$A\$200>120)*(\$B\$1:\$B\$200))

and so on ........

Mubashir,

Brilliant, thank you very much for your help, very much appeciated,

Regards,
Matthew
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
160
u alwaz welcome ....