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

5 replies

mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 160
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)
0
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
0
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 160
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 ........


0
Mubashir,

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

Regards,
Matthew
0

Didn't find the answer you are looking for?

Ask a question
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 160
Jun 5, 2009 at 07:03 AM
u alwaz welcome ....
0