Search : in
By :

TOTAL RANGE OF VALUES

Last answer on Jun 5, 2009 12:03:27 pm BST MATTHEWRAD, on Jun 3, 2009 2:50:23 pm BST 
 Report this message to moderators

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

Configuration: Windows XP Internet Explorer 6.0

Best answers for « TOTAL RANGE OF VALUES » in :
Excel – Total Range of Values Show Excel – Total Range of Values Issue Solution Note Issue I'm trying to get a range of values totalled. I have one set of columns, let’s say column A1 to A200, ranging from -30 up to 1000 and another column adjacent (let’s say B1 to...
Excel Macro - Set and autofill a range? Show
Macro code for making a graph in excel Show
[VBA] Deleting a word in a range of cell Show[VBA] Deleting a word in a range of cell In the case you want to delete a word in a sentence, just create a small macro that removes the word. But it will become difficult when you have word like, for example, "Theword" or "THEWORD" or...
VB6 Finding the RGB values of a color ShowVB6 Finding the RGB values of a color Dim R as integer Dim G as integer Dim B as integer Sub FindRGB(Col As Long) R = &HFF& And Col G = (&HFF00& And Col ) \ 256 B = (&HFF0000 And Col ) \ 65536 End Sub Note: Here...
Excel - Using IF statement to add 1 to total ShowExcel - Using IF statement to add 1 to total Issue Solution Note Issue I am trying to get a formula to add + 1 to a total cell based on the word typed. Example : If I type the word Duty in cells B3:B18 then add + 1 to cell B35. If...
Download Total Video Converter ShowDescription: Total Video Converter is a converter that converts video and audio files. It will allow you to convert your video files for your mobile phones, PSP, PS3, iPod, iPhone, etc. It can read various video file formats like mpeg, avi, mov, flv...
Download Total Recorder Editor Show
Download TotalMedia ShowDescription The application is designed by ArcSoft Software Inc. Total Media is an all in one pack, it includes TV, Video recording, photo editing and media conversion. It allows you to edit photos or videos, rips CDs to MP3 and more. Simple and easy...
Spreadsheet - Formulas ShowIntroduction to Formulas The main use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of...
TCO (Total Cost of Ownership) ShowIntroduction to TCO TCO (Total Cost of Ownership is the total cost of a good (e.g. an information system) over its entire life cycle. TCO takes into account not only direct costs (equipment costs such as computers, network infrastructures, etc. or...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Ask your question Reply

1

mubashir aziz, on Jun 4, 2009 8:23:07 am BST

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)
Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

2

MATTHEWRAD, on Jun 4, 2009 1:04:52 pm BST

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

   
Reply to MATTHEWRAD

3

mubashir aziz, on Jun 5, 2009 6:42:59 am BST

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 ........


Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

4

MATTHEWRAD, on Jun 5, 2009 10:11:29 am BST

Mubashir,

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

Regards,
Matthew

   
Reply to MATTHEWRAD

5

 mubashir aziz, on Jun 5, 2009 12:03:27 pm BST

U alwaz welcome ....
Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz
Ask your question Reply
They need your help