Adding data in a list that meets a specific criteria

[Sarah]

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!

 

[ufo_pilot]

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

 

[Martin]

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...

 

[andy62]

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

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...

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!

 

[Sarah]

All excellent points - thank you VERY much!

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

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...

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!