Formula with 2 conditions
 

I have a spreadsheet with 20000 rows capturing numbers from .01 to $1,000,000
in column D.
What I want to do is break these rows into ranges.
For example the first range would be .01-1.00,
then the next range 501 to 20,000
Then the next range 20,001 to 100,000.

I've tried the Sum and Sumif funtion but cannot get them to work.

Would this be considered an Array function?

Formula with 2 conditions
 

The lowest group could be summed like this:

=SUMIF(A1:A20000,"<=1")

Say the above formula was put in B1.

Then to get the next group use in B2:

=SUMIF(A1:A20000,"<=500")-B1

Then

=SUMIF(A1:A20000,"<=20000")-SUM(B1:B2)

Etc.

--
Jim
"Joey" wrote in message
...
|I have a spreadsheet with 20000 rows capturing numbers from .01 to
$1,000,000
| in column D.
| What I want to do is break these rows into ranges.
| For example the first range would be .01-1.00,
| then the next range 501 to 20,000
| Then the next range 20,001 to 100,000.
|
| I've tried the Sum and Sumif funtion but cannot get them to work.
|
| Would this be considered an Array function?

What worked the best
 

=COUNTIF($A$3:$A$11792,"1")-COUNTIF($A$3:$A$11792,"=500")

"Jim Rech" wrote:

The lowest group could be summed like this:

=SUMIF(A1:A20000,"<=1")

Say the above formula was put in B1.

Then to get the next group use in B2:

=SUMIF(A1:A20000,"<=500")-B1

Then

=SUMIF(A1:A20000,"<=20000")-SUM(B1:B2)

Etc.

--
Jim
"Joey" wrote in message
...
|I have a spreadsheet with 20000 rows capturing numbers from .01 to
$1,000,000
| in column D.
| What I want to do is break these rows into ranges.
| For example the first range would be .01-1.00,
| then the next range 501 to 20,000
| Then the next range 20,001 to 100,000.
|
| I've tried the Sum and Sumif funtion but cannot get them to work.
|
| Would this be considered an Array function?