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
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 - Jun 5, 2009 at 07:03 AM
Related:
- Excel Total Range of Values
- Total copy - Download - File management
- Total video converter for pc - Download - Video converters
- Total war warhammer 3 free download - Download - Strategy
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
5 responses
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
165
Jun 4, 2009 at 03:23 AM
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)
=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
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
165
Jun 5, 2009 at 01:42 AM
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 ........
=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 ........
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
165
Jun 5, 2009 at 07:03 AM
Jun 5, 2009 at 07:03 AM
u alwaz welcome ....
