Hi
I have a long column of data in descending order. I want to add up all the
data that fits a specific criteria e.g. all data below 10, then all data
between 10 and 20 etc etc. I have to repeat this often and the data set
changes regularly so I need to use a function rather than set ranges. Any
ideas?? Thanks!

=SUMIF(A1:A150,"<10")
=SUMIF(A1:A150,"=10")
You will need a row for each criteria.


"Sarah" wrote:

Hi
I have a long column of data in descending order. I want to add up all the
data that fits a specific criteria e.g. all data below 10, then all data
between 10 and 20 etc etc. I have to repeat this often and the data set
changes regularly so I need to use a function rather than set ranges. Any
ideas?? Thanks!

Sumif is OK but won't work for more than one criteria as far as I know which
you need for greater than 10 and less than 20.

This is probably a case for a database function (haven't used these since
the days of Lotus 1-2-3 in the early 90s!). Your column will need a heading
- I've assumed that the word "Heading" has been typed above your figures.
DSUM needs a criteria range which is the heading(s) in a another cell with
the criteria below (AND works across, OR works down):

e.g. (criteria typed across top)

A B C
Heading Heading Heading
<10 =10 <20

for less than 10:

=DSUM([your range of data],1,A1:A2)
(1 is the column number in the range)

for 10 - 20:

=DSUM([your range of data],1,A1:C2)

etc.

Hope that makes sense but Help is good on Excel functions these days...


"Sarah" wrote:

Hi
I have a long column of data in descending order. I want to add up all the
data that fits a specific criteria e.g. all data below 10, then all data
between 10 and 20 etc etc. I have to repeat this often and the data set
changes regularly so I need to use a function rather than set ranges. Any
ideas?? Thanks!

What I do that seems to work and may be simpler is to have the first tally
sumif <10, then the second tally is simply sumif <20 minus all the previous
tallies, then the third tally is sumif <30 minus the sum of all the previous
tallies, etc. Here is is in excel lingo:

C1=SUMIF(A1:A5000,<10)
C2=SUMIF(A1:A5000,<20)-SUM($C$1:C1)
copy this second formula down to as many cuts as you need, then replace the
"<20"s with the remaining cutoffs. But you need to arrange the cutoffs in
increasing order for this to work.

HTH

"Martin" wrote:

Sumif is OK but won't work for more than one criteria as far as I know which
you need for greater than 10 and less than 20.

This is probably a case for a database function (haven't used these since
the days of Lotus 1-2-3 in the early 90s!). Your column will need a heading
- I've assumed that the word "Heading" has been typed above your figures.
DSUM needs a criteria range which is the heading(s) in a another cell with
the criteria below (AND works across, OR works down):

e.g. (criteria typed across top)

A B C
Heading Heading Heading
<10 =10 <20

for less than 10:

=DSUM([your range of data],1,A1:A2)
(1 is the column number in the range)

for 10 - 20:

=DSUM([your range of data],1,A1:C2)

etc.

Hope that makes sense but Help is good on Excel functions these days...


"Sarah" wrote:

Hi
I have a long column of data in descending order. I want to add up all the
data that fits a specific criteria e.g. all data below 10, then all data
between 10 and 20 etc etc. I have to repeat this often and the data set
changes regularly so I need to use a function rather than set ranges. Any
ideas?? Thanks!

All excellent points - thank you VERY much!

"andy62" wrote:

What I do that seems to work and may be simpler is to have the first tally
sumif <10, then the second tally is simply sumif <20 minus all the previous
tallies, then the third tally is sumif <30 minus the sum of all the previous
tallies, etc. Here is is in excel lingo:

C1=SUMIF(A1:A5000,<10)
C2=SUMIF(A1:A5000,<20)-SUM($C$1:C1)
copy this second formula down to as many cuts as you need, then replace the
"<20"s with the remaining cutoffs. But you need to arrange the cutoffs in
increasing order for this to work.

HTH

"Martin" wrote:

Sumif is OK but won't work for more than one criteria as far as I know which
you need for greater than 10 and less than 20.

This is probably a case for a database function (haven't used these since
the days of Lotus 1-2-3 in the early 90s!). Your column will need a heading
- I've assumed that the word "Heading" has been typed above your figures.
DSUM needs a criteria range which is the heading(s) in a another cell with
the criteria below (AND works across, OR works down):

e.g. (criteria typed across top)

A B C
Heading Heading Heading
<10 =10 <20

for less than 10:

=DSUM([your range of data],1,A1:A2)
(1 is the column number in the range)

for 10 - 20:

=DSUM([your range of data],1,A1:C2)

etc.

Hope that makes sense but Help is good on Excel functions these days...


"Sarah" wrote:

Hi
I have a long column of data in descending order. I want to add up all the
data that fits a specific criteria e.g. all data below 10, then all data
between 10 and 20 etc etc. I have to repeat this often and the data set
changes regularly so I need to use a function rather than set ranges. Any
ideas?? Thanks!