Adding data in a list that meets a specific criteria
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!
|