I am not used to working with Excel. This is what I want to happen, but I am
not sure how to translate it into Excel.

I Have a colum with order sizes of our companies product. So colum A is
values between 1 and 2000 units. I want to see this:

Total Units sold = X
% of X that were orders of 1-150 units
% of X that were orders of 150-300 units
% of X that were orders of 300-600 unites
% of X that were orders of 600-1000
% of X that were orders of 1000+

I can input them manually by typing "=SUM(A1:A12)/X" but each time I add an
order I have to redo them all.
So what I need is something like:

=SUM(ColumA) WHERE VALUE is equal to or less than 150

Can anyone help me with this?

Let's say that column A has the order sizes.
Let's also say that your data is in rows 2-2000. There is a way to
dynamically determine this, but that needs to wait.

Total units sold = SUM (A$2:A$2000)
Let's say that in J2 you have 1 and in K2 you have 150 (your range for the
first group)
To count the # of orders between 1 and 150, enter this:

=SUMPRODUCT(--(A$2:A$2000J2),--(A$2:A$2000<K2))

To dynamically determine the range, look up the OFFSET function and create a
named range. Maybe someone else can assist with that.


"m@" wrote:

I am not used to working with Excel. This is what I want to happen, but I am
not sure how to translate it into Excel.

I Have a colum with order sizes of our companies product. So colum A is
values between 1 and 2000 units. I want to see this:

Total Units sold = X
% of X that were orders of 1-150 units
% of X that were orders of 150-300 units
% of X that were orders of 300-600 unites
% of X that were orders of 600-1000
% of X that were orders of 1000+

I can input them manually by typing "=SUM(A1:A12)/X" but each time I add an
order I have to redo them all.
So what I need is something like:

=SUM(ColumA) WHERE VALUE is equal to or less than 150

Can anyone help me with this?

m@

I would suggest entering the following in 4 different cells. They need to
be entered as array formulas so press ctrl+shift+enter to enter them.
Adjust your range to suit

percent below 150
=SUMPRODUCT(--(A1:A7<=150),A1:A7)/SUM(A1:A7)
percent greater than 150 but less than 300
=SUMPRODUCT(--(A1:A7150),--(A1:A7<=300),A1:A7)/SUM(A1:A7)
percent greater than 300 and less than 600
=SUMPRODUCT(--(A1:A7300),--(A1:A7<=600),A1:A7)/SUM(A1:A7)
percent greater than 600 and less than 1000
=SUMPRODUCT(--(A1:A7600),--(A1:A7<=1000),A1:A7)/SUM(A1:A7)
percent greater than 1000
=SUMPRODUCT(--(A1:A71000),A1:A7)/SUM(A1:A7)


"m@" wrote in message
...
I am not used to working with Excel. This is what I want to happen, but I
am
not sure how to translate it into Excel.

I Have a colum with order sizes of our companies product. So colum A is
values between 1 and 2000 units. I want to see this:

Total Units sold = X
% of X that were orders of 1-150 units
% of X that were orders of 150-300 units
% of X that were orders of 300-600 unites
% of X that were orders of 600-1000
% of X that were orders of 1000+

I can input them manually by typing "=SUM(A1:A12)/X" but each time I add
an
order I have to redo them all.
So what I need is something like:

=SUM(ColumA) WHERE VALUE is equal to or less than 150

Can anyone help me with this?

On 23/6/06 20:28, in article
, "m@"
wrote:

I am not used to working with Excel. This is what I want to happen, but I am
not sure how to translate it into Excel.

I Have a colum with order sizes of our companies product. So colum A is
values between 1 and 2000 units. I want to see this:

Total Units sold = X
% of X that were orders of 1-150 units
% of X that were orders of 150-300 units
% of X that were orders of 300-600 unites
% of X that were orders of 600-1000
% of X that were orders of 1000+

I can input them manually by typing "=SUM(A1:A12)/X" but each time I add an
order I have to redo them all.
So what I need is something like:


=SUM(ColumA) WHERE VALUE is equal to or less than 150

That part is straightforward enough:

=SUMIF(A1:A8,"<=150")

But you wouldn't able to use it for the other parameters, as these involve 2
logical checks i.e 150 and <=300 and SUMIF can only do one logical
comparison.

However you can sum based on more than one criteria by using DSUM which is
one of XL's database functions - you would need to enter the paramater
criteria above your data - Check out this page for more info:

http://www.ozgrid.com/Excel/sum-if.htm

And check out your Excel Help for DSUM examples.

HTH

-Jay-