Excel Total Range of Values

Solved/Closed
MATTHEWRAD - Jun 3, 2009 at 09:50 AM
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 - Jun 5, 2009 at 07:03 AM
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
Related:

5 responses

mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 166
Jun 4, 2009 at 03:23 AM
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)
Muhammad,
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

Thanks for your help
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 166
Jun 5, 2009 at 01:42 AM
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
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 166
Jun 5, 2009 at 07:03 AM
u alwaz welcome ....