Stratify Data In a Range

[Guest]

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?

 

[Bernard Liengme]

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)>=A105, --($A$1:$E$100)<=B105), $A$1:$E$100)
If you just want a COUNT use
=SUMPRODUCT(--($A$1:$E$100)>=A105, --($A$1:$E$100)<=B105)

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

For explainations see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[Guest]

I tried it exactly how you wrote, but it tells me there is an error in the
formula.

 

[Bernard Liengme]

typo, i should be
=SUMPRODUCT(--($A$1:$E$100>=A105), --($A$1:$E$100<=B105), $A$1:$E$100)

sorry

 

[Guest]

I got it working now... thanks. I don't know why it didn't work the first
time.

 

[Guest]

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

I typed my formula: =SUMPRODUCT((Data>=A11)*(Data<=B11),Data)
with "Data" being the Range of cells, and it worked.

 

[Dave Peterson]

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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