software

Hello, I am trying to stratify amounts in a given range of cells into
different tiers.

I have a range of $ values. Lets say from A1:E100 that is filled with
numerical values.

I am trying to summarize this data into tiers, for example
sum of amounts: from 10,000 to 15,000
sum of amounts: from 5,000 to 9,999
sum of amounts: from 0 to 4,999
sum of amounts: from (1) to (4,999)
sum of amounts: from (5,000) to (9,999)
sum of amounts: from (10,000) to (14,999)

How do I do this?

You want to know the SUM of the values in range X to Y
In A105 enter 10,000; in B105 enter 15,000; in C105 enter
=SUMPRODUCT(--($A$1:$E$100)gt;=A105, --($A$1:$E$100)lt;=B105), $A$1:$E$100)
If you just want a COUNT use
=SUMPRODUCT(--($A$1:$E$100)gt;=A105, --($A$1:$E$100)lt;=B105)

In A106 enter 5,000 and in B1006 enter 9,999 copy C105 to C106
et cetera

For explainations see
www.xldynamic.com/source/xld.SUMPRODUCT.html
mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;Johnny Strangerquot; lt;Johnny gt; wrote in
message ...
gt; Hello, I am trying to stratify amounts in a given range of cells into
gt; different tiers.
gt;
gt; I have a range of $ values. Lets say from A1:E100 that is filled with
gt; numerical values.
gt;
gt; I am trying to summarize this data into tiers, for example
gt; sum of amounts: from 10,000 to 15,000
gt; sum of amounts: from 5,000 to 9,999
gt; sum of amounts: from 0 to 4,999
gt; sum of amounts: from (1) to (4,999)
gt; sum of amounts: from (5,000) to (9,999)
gt; sum of amounts: from (10,000) to (14,999)
gt;
gt; How do I do this?
I tried it exactly how you wrote, but it tells me there is an error in the
formula.quot;Bernard Liengmequot; wrote:

gt; You want to know the SUM of the values in range X to Y
gt; In A105 enter 10,000; in B105 enter 15,000; in C105 enter
gt; =SUMPRODUCT(--($A$1:$E$100)gt;=A105, --($A$1:$E$100)lt;=B105), $A$1:$E$100)
gt; If you just want a COUNT use
gt; =SUMPRODUCT(--($A$1:$E$100)gt;=A105, --($A$1:$E$100)lt;=B105)
gt;
gt; In A106 enter 5,000 and in B1006 enter 9,999 copy C105 to C106
gt; et cetera
gt;
gt; For explainations see
gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt; mcgimpsey.com/excel/formulae/doubleneg.html
gt; best wishes
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;Johnny Strangerquot; lt;Johnny gt; wrote in
gt; message ...
gt; gt; Hello, I am trying to stratify amounts in a given range of cells into
gt; gt; different tiers.
gt; gt;
gt; gt; I have a range of $ values. Lets say from A1:E100 that is filled with
gt; gt; numerical values.
gt; gt;
gt; gt; I am trying to summarize this data into tiers, for example
gt; gt; sum of amounts: from 10,000 to 15,000
gt; gt; sum of amounts: from 5,000 to 9,999
gt; gt; sum of amounts: from 0 to 4,999
gt; gt; sum of amounts: from (1) to (4,999)
gt; gt; sum of amounts: from (5,000) to (9,999)
gt; gt; sum of amounts: from (10,000) to (14,999)
gt; gt;
gt; gt; How do I do this?
gt;
gt;
gt;

typo, i should be
=SUMPRODUCT(--($A$1:$E$100gt;=A105), --($A$1:$E$100lt;=B105), $A$1:$E$100)

sorry
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;Johnny Strangerquot; gt; wrote in
message ...
gt;I tried it exactly how you wrote, but it tells me there is an error in the
gt; formula.
gt;
gt;
gt; quot;Bernard Liengmequot; wrote:
gt;
gt;gt; You want to know the SUM of the values in range X to Y
gt;gt; In A105 enter 10,000; in B105 enter 15,000; in C105 enter
gt;gt; =SUMPRODUCT(--($A$1:$E$100)gt;=A105, --($A$1:$E$100)lt;=B105), $A$1:$E$100)
gt;gt; If you just want a COUNT use
gt;gt; =SUMPRODUCT(--($A$1:$E$100)gt;=A105, --($A$1:$E$100)lt;=B105)
gt;gt;
gt;gt; In A106 enter 5,000 and in B1006 enter 9,999 copy C105 to C106
gt;gt; et cetera
gt;gt;
gt;gt; For explainations see
gt;gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt;gt; mcgimpsey.com/excel/formulae/doubleneg.html
gt;gt; best wishes
gt;gt; --
gt;gt; Bernard V Liengme
gt;gt; www.stfx.ca/people/bliengme
gt;gt; remove caps from email
gt;gt;
gt;gt; quot;Johnny Strangerquot; lt;Johnny gt; wrote in
gt;gt; message ...
gt;gt; gt; Hello, I am trying to stratify amounts in a given range of cells into
gt;gt; gt; different tiers.
gt;gt; gt;
gt;gt; gt; I have a range of $ values. Lets say from A1:E100 that is filled with
gt;gt; gt; numerical values.
gt;gt; gt;
gt;gt; gt; I am trying to summarize this data into tiers, for example
gt;gt; gt; sum of amounts: from 10,000 to 15,000
gt;gt; gt; sum of amounts: from 5,000 to 9,999
gt;gt; gt; sum of amounts: from 0 to 4,999
gt;gt; gt; sum of amounts: from (1) to (4,999)
gt;gt; gt; sum of amounts: from (5,000) to (9,999)
gt;gt; gt; sum of amounts: from (10,000) to (14,999)
gt;gt; gt;
gt;gt; gt; How do I do this?
gt;gt;
gt;gt;
gt;gt;
I got it working now... thanks. I don't know why it didn't work the first
time.quot;Bernard Liengmequot; wrote:

gt; You want to know the SUM of the values in range X to Y
gt; In A105 enter 10,000; in B105 enter 15,000; in C105 enter
gt; =SUMPRODUCT(--($A$1:$E$100)gt;=A105, --($A$1:$E$100)lt;=B105), $A$1:$E$100)
gt; If you just want a COUNT use
gt; =SUMPRODUCT(--($A$1:$E$100)gt;=A105, --($A$1:$E$100)lt;=B105)
gt;
gt; In A106 enter 5,000 and in B1006 enter 9,999 copy C105 to C106
gt; et cetera
gt;
gt; For explainations see
gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt; mcgimpsey.com/excel/formulae/doubleneg.html
gt; best wishes
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;Johnny Strangerquot; lt;Johnny gt; wrote in
gt; message ...
gt; gt; Hello, I am trying to stratify amounts in a given range of cells into
gt; gt; different tiers.
gt; gt;
gt; gt; I have a range of $ values. Lets say from A1:E100 that is filled with
gt; gt; numerical values.
gt; gt;
gt; gt; I am trying to summarize this data into tiers, for example
gt; gt; sum of amounts: from 10,000 to 15,000
gt; gt; sum of amounts: from 5,000 to 9,999
gt; gt; sum of amounts: from 0 to 4,999
gt; gt; sum of amounts: from (1) to (4,999)
gt; gt; sum of amounts: from (5,000) to (9,999)
gt; gt; sum of amounts: from (10,000) to (14,999)
gt; gt;
gt; gt; How do I do this?
gt;
gt;
gt;

I'm curious... what is the purpose of the quot;--quot; between the two opening
brackets you have in your formula??

I typed my formula: =SUMPRODUCT((Datagt;=A11)*(Datalt;=B11),Data)
with quot;Dataquot; being the Range of cells, and it worked.

quot;Bernard Liengmequot; wrote:

gt; typo, i should be
gt; =SUMPRODUCT(--($A$1:$E$100gt;=A105), --($A$1:$E$100lt;=B105), $A$1:$E$100)
gt;
gt; sorry
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;Johnny Strangerquot; gt; wrote in
gt; message ...
gt; gt;I tried it exactly how you wrote, but it tells me there is an error in the
gt; gt; formula.
gt; gt;
gt; gt;
gt; gt; quot;Bernard Liengmequot; wrote:
gt; gt;
gt; gt;gt; You want to know the SUM of the values in range X to Y
gt; gt;gt; In A105 enter 10,000; in B105 enter 15,000; in C105 enter
gt; gt;gt; =SUMPRODUCT(--($A$1:$E$100)gt;=A105, --($A$1:$E$100)lt;=B105), $A$1:$E$100)
gt; gt;gt; If you just want a COUNT use
gt; gt;gt; =SUMPRODUCT(--($A$1:$E$100)gt;=A105, --($A$1:$E$100)lt;=B105)
gt; gt;gt;
gt; gt;gt; In A106 enter 5,000 and in B1006 enter 9,999 copy C105 to C106
gt; gt;gt; et cetera
gt; gt;gt;
gt; gt;gt; For explainations see
gt; gt;gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt; gt;gt; mcgimpsey.com/excel/formulae/doubleneg.html
gt; gt;gt; best wishes
gt; gt;gt; --
gt; gt;gt; Bernard V Liengme
gt; gt;gt; www.stfx.ca/people/bliengme
gt; gt;gt; remove caps from email
gt; gt;gt;
gt; gt;gt; quot;Johnny Strangerquot; lt;Johnny gt; wrote in
gt; gt;gt; message ...
gt; gt;gt; gt; Hello, I am trying to stratify amounts in a given range of cells into
gt; gt;gt; gt; different tiers.
gt; gt;gt; gt;
gt; gt;gt; gt; I have a range of $ values. Lets say from A1:E100 that is filled with
gt; gt;gt; gt; numerical values.
gt; gt;gt; gt;
gt; gt;gt; gt; I am trying to summarize this data into tiers, for example
gt; gt;gt; gt; sum of amounts: from 10,000 to 15,000
gt; gt;gt; gt; sum of amounts: from 5,000 to 9,999
gt; gt;gt; gt; sum of amounts: from 0 to 4,999
gt; gt;gt; gt; sum of amounts: from (1) to (4,999)
gt; gt;gt; gt; sum of amounts: from (5,000) to (9,999)
gt; gt;gt; gt; sum of amounts: from (10,000) to (14,999)
gt; gt;gt; gt;
gt; gt;gt; gt; How do I do this?
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
mcgimpsey.com/excel/formulae/doubleneg.html

Johnny Stranger wrote:
gt;
gt; I'm curious... what is the purpose of the quot;--quot; between the two opening
gt; brackets you have in your formula??
gt;
gt; I typed my formula: =SUMPRODUCT((Datagt;=A11)*(Datalt;=B11),Data)
gt; with quot;Dataquot; being the Range of cells, and it worked.
gt;
gt; quot;Bernard Liengmequot; wrote:
gt;
gt; gt; typo, i should be
gt; gt; =SUMPRODUCT(--($A$1:$E$100gt;=A105), --($A$1:$E$100lt;=B105), $A$1:$E$100)
gt; gt;
gt; gt; sorry
gt; gt; --
gt; gt; Bernard V Liengme
gt; gt; www.stfx.ca/people/bliengme
gt; gt; remove caps from email
gt; gt;
gt; gt; quot;Johnny Strangerquot; gt; wrote in
gt; gt; message ...
gt; gt; gt;I tried it exactly how you wrote, but it tells me there is an error in the
gt; gt; gt; formula.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Bernard Liengmequot; wrote:
gt; gt; gt;
gt; gt; gt;gt; You want to know the SUM of the values in range X to Y
gt; gt; gt;gt; In A105 enter 10,000; in B105 enter 15,000; in C105 enter
gt; gt; gt;gt; =SUMPRODUCT(--($A$1:$E$100)gt;=A105, --($A$1:$E$100)lt;=B105), $A$1:$E$100)
gt; gt; gt;gt; If you just want a COUNT use
gt; gt; gt;gt; =SUMPRODUCT(--($A$1:$E$100)gt;=A105, --($A$1:$E$100)lt;=B105)
gt; gt; gt;gt;
gt; gt; gt;gt; In A106 enter 5,000 and in B1006 enter 9,999 copy C105 to C106
gt; gt; gt;gt; et cetera
gt; gt; gt;gt;
gt; gt; gt;gt; For explainations see
gt; gt; gt;gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt; gt; gt;gt; mcgimpsey.com/excel/formulae/doubleneg.html
gt; gt; gt;gt; best wishes
gt; gt; gt;gt; --
gt; gt; gt;gt; Bernard V Liengme
gt; gt; gt;gt; www.stfx.ca/people/bliengme
gt; gt; gt;gt; remove caps from email
gt; gt; gt;gt;
gt; gt; gt;gt; quot;Johnny Strangerquot; lt;Johnny gt; wrote in
gt; gt; gt;gt; message ...
gt; gt; gt;gt; gt; Hello, I am trying to stratify amounts in a given range of cells into
gt; gt; gt;gt; gt; different tiers.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; I have a range of $ values. Lets say from A1:E100 that is filled with
gt; gt; gt;gt; gt; numerical values.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; I am trying to summarize this data into tiers, for example
gt; gt; gt;gt; gt; sum of amounts: from 10,000 to 15,000
gt; gt; gt;gt; gt; sum of amounts: from 5,000 to 9,999
gt; gt; gt;gt; gt; sum of amounts: from 0 to 4,999
gt; gt; gt;gt; gt; sum of amounts: from (1) to (4,999)
gt; gt; gt;gt; gt; sum of amounts: from (5,000) to (9,999)
gt; gt; gt;gt; gt; sum of amounts: from (10,000) to (14,999)
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; How do I do this?
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt;
gt; gt;
gt; gt;

--

Dave Peterson