How do I make this happen

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$2000>J2),--(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:A7>150),--(A1:A7<=300),A1:A7)/SUM(A1:A7)
percent greater than 300 and less than 600
=SUMPRODUCT(--(A1:A7>300),--(A1:A7<=600),A1:A7)/SUM(A1:A7)
percent greater than 600 and less than 1000
=SUMPRODUCT(--(A1:A7>600),--(A1:A7<=1000),A1:A7)/SUM(A1:A7)
percent greater than 1000
=SUMPRODUCT(--(A1:A7>1000),A1:A7)/SUM(A1:A7)


"m@" <m@discussions.microsoft.com> wrote in message
news:40949BAA-61DF-4272-B6B0-1B91A983EB38@microsoft.com...
>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
40949BAA-61DF-4272-B6B0-1B91A983EB38@microsoft.com, "m@"
<m@discussions.microsoft.com> 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-