How do I sum numbers up to an certain threshold in Excel?

G

Guest

I'm sure the answer is easy, but I can't figure it out. I'm trying to sum
one column up to a preset threshold (like 60) and then want to sum up
corresponding entries, but I only want the entries that coresspond to the
entries that don't exceed 60.

So far I've create a GPA calculator where you place credit hours in one
column, a letter grade in another, and then it uses a vlookup function to
find out how many points that letter grade is worth, and then uses sum
functions to sum up the points and the credit hours. It then divides the two
to get a GPA.

I'm stuck, however, in trying to figure out how to get it to do a "Last 60
hours GPA" where it will sum the credit hours up to 60, and then sum up the
corresponding grade points. I can figure it out by hand very easily, so I
figure there must be a simple automated way to do it, as well, but the best
I've come up with is to create a really huge nested IF statement where I say
"IF(cell1+cell2<60,IF(sum(cell1:cell2)+cell3<60,IF(sum(cell1:cell3)+cell4<60,
etc" and then just back out the IF statements with sum functions, but that's
just insane and it won't tell me which ones have been accepted for summing up
the grade points that correspond to the credit hours. Using this method I'd
then have to create another huge nested IF statement where IF the credit
hours, but have a sum of the grade points on teh back side of the function.
Anyone know of an easier way? I'd rather avoid anything that involves Visual
Basic. Thx in advance.
 
J

JulieD

Hi

i think SUMIF will give you what you want
=SUMIF(A1:A100,"<="&60,B1:B100)
where you're checking the range A1:A100 if it is less than or equal to 60
and summing the corresponding values in the range B1:B100
 
G

Guest

The SUMIF function doesn't work because as I have it set up now I list my
classes, class by class and it will simply sum everything because everything
is less than 60. For example, where first column is name, second is credit
hours, third is grade, fourth is weighted grade points:

ManEc 3 A 12
Biol 3 C 6
Acc 3 B 9
etc.

If I say SUMIF(Column2,<=60,Column2) it will sum up everything. I only want
the first X number of entries until some running total for column2 hits 60
and then don't want to sum up anything after that. So I don't think SUMIF
will work, unless there is something about it I don't understand. Thx again.
 
B

Biff

Hi!

Not sure what you're trying to sum as the final result but it sounds like
you need a helper column to identify where 60 is.

Assume you need a progressive sum of column 2 (B?):

=SUM(B$1:B1)

Copy down as needed.

Now you'll know where 60 is!

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top