LinkBack URL
About LinkBacks
So I have multiple columns of numerical data,
I have to find the sum of all numbers between 10 and 40 within a column, NOT cell 10 to 40 but the actual number 10 and 40.
How do I do that?
Is it SUMIF? I cannt seem to grasp this.
THANKS FOR THE HELP
So I have multiple columns of numerical data,
I have to find the sum of all numbers between 10 and 40 within a column, NOT cell 10 to 40 but the actual number 10 and 40.
How do I do that?
Is it SUMIF? I cannt seem to grasp this.
THANKS FOR THE HELP
Hello johnbeckman,
I think you have to use CUSTOM AUTO FILTER.
__________________
Regards
Rahul Nagar
Founder of www.myshortcutkeys.com.
If you get the answer of your questions then please click EDIT in your original post then click on GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.
I imagine a SUMPRODUCT on the same range over and over:
=SUMPRODUCT((A1:A20>10)*(A1:A20<40)*(A1:A20))
Note, this doesn't INCLUDE 10 or 40.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
hi,
can you guys specify a little?
try the sum product thing, did not seem to work
Hi,
You could try this
=SUMIF(A1:A20,10,A1:A20)+SUMIF(A1:A20,40,A1:A20)
Where column A1:A20 includes numbers 10 and 40 amongst others, if the range you need to add is not in column A but in column B, then the formula would be
=SUMIF(A1:A20,10,B1:B20)+SUMIF(A1:A20,40,B1:B20)
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
Are we perhaps mis-reading your problem?
Do you want to find the numbers 10 and 40 within a column of numbers,
then sum all the numbers that occur between these two locations?
If so the solution is very different from those suggested so far...
Mark.
Hopefully johnbeckman will enlighten us
64.000
80.000
80.000
32.000
72.000
80.000
352.000
80.000
28.800
32.000
11.100
26.100
12.200
11.700
22.400
22.240
80.000
160.000
70.880
70.400
160.000
80.000
80.000
34.880
80.000
35.200
35.200
80.000
32.000
80.000
Okay, so I have this column of data, a random sample.
I need to find the sum of all numbers between 20-60. So it would be 35 +32 + etc...
Sorry for the confusion.
The formula I originally suggested does it.
=SUMPRODUCT((A1:A100>20)*(A1:A100<60)*(A1:A100))
Result = 300.82 (data in column A)
To INCLUDE 20 and 60, just change < and > to =< and >=.
Make this formula variable by replacing the underlined sections above with cell references.
Last edited by JBeaucaire; 12-30-2008 at 04:18 PM.
Another approach, if the SUMIF has to be the chosen function:
=SUM(A:A)-SUMIF(A:A,"<20")-SUMIF(A:A,">60")
or
=SUMIF(A:A,">=20")-SUMIF(A:A,">60")
(these include 20 and 60)
Last edited by JBeaucaire; 12-30-2008 at 04:27 PM.
GREAT! That worked really well, you definitely saved my *** at work.
ONE MORE RELATED QUESTION,
Say, that I have a second column of numerical data, column B. Column A corresponds to column B. Column A is what we just did and represents weight and column B consists of the corresponding prices.
How do I add the prices that correspond to 20-60 up? Meaning adding only the prices for those weight that fall within 20-60 zone.
THANKS SO MUCH MAN, I actually just got out of college and this is my like first week at work so I don't want to look like a complete idiot.
The first two ranges restrict the list, the third was the actual "sum", so just change the range of that third range to your prices:
=SUMPRODUCT((A1:A100>20)*(A1:A100<60)*(B1:B100))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks