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

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.

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Stuck Student" <Stuck Student@discussions.microsoft.com> wrote in message
news:7D2E2203-B5A4-4888-AB77-C4665CE8A89C@microsoft.com...
> 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.

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.


"JulieD" wrote:

> 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
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "Stuck Student" <Stuck Student@discussions.microsoft.com> wrote in message
> news:7D2E2203-B5A4-4888-AB77-C4665CE8A89C@microsoft.com...
> > 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.
>
>
>

Okay

in E2 how about
=IF(SUM($B$2:B2)<=60,SUM($B$2:B2),"")
and fill down the column

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Stuck Student" <Stuck Student@discussions.microsoft.com> wrote in message
news:B32FB1F6-3D89-40F3-A89C-7D0778C1F8A1@microsoft.com...
> 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.
>
>
> "JulieD" wrote:
>
>> 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
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "Stuck Student" <Stuck Student@discussions.microsoft.com> wrote in
>> message
>> news:7D2E2203-B5A4-4888-AB77-C4665CE8A89C@microsoft.com...
>> > 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.
>>
>>
>>

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

"Stuck Student" <Stuck Student@discussions.microsoft.com> wrote in message
news:B32FB1F6-3D89-40F3-A89C-7D0778C1F8A1@microsoft.com...
> 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.
>
>
> "JulieD" wrote:
>
>> 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
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "Stuck Student" <Stuck Student@discussions.microsoft.com> wrote in
>> message
>> news:7D2E2203-B5A4-4888-AB77-C4665CE8A89C@microsoft.com...
>> > 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.
>>
>>
>>