SUMIF question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Billy B - 30 Jan 2008 20:34 GMT

I am trying to calculate a percent based on the SUMIF function using
non-continuous cells. Row 5 of the worksheet contains the possible points and
row 7 the points the student has received. Have been working at it a while
and having problems getting it to work. Below is what I have so far. Any
help would be appreciate

=IF(SUM(F7:AS7,AW7:CG7,CK7:EE7,EJ7:EX7,FB7:FS7,FW7:GW7,HB7)=0,0,SUMIF({"F7:AS7"AW7:CG7","CK7:EE7","EJ7:EX7","FB7:FS7","FW7:GW7","HB7"},">=0",{"F7:AS7","AW7:CG7","CK7:EE7","EJ7:EX7","FB7:FS7","FW7:GW7","HB7"}/{"$F$5:$AS$5","$AW$5:$CG$5","$CK$5:EE$5","EJ$5:EX$5","FB$5:FS$5","FW$5:GW$5","HB$5"}))

Reply to this Message

Bernard Liengme - 30 Jan 2008 20:44 GMT

Not clear (to me, anyway) what you are trying to do
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

>I am trying to calculate a percent based on the SUMIF function using
> non-continuous cells. Row 5 of the worksheet contains the possible points
[quoted text clipped - 4 lines]
>
> =IF(SUM(F7:AS7,AW7:CG7,CK7:EE7,EJ7:EX7,FB7:FS7,FW7:GW7,HB7)=0,0,SUMIF({"F7:AS7"AW7:CG7","CK7:EE7","EJ7:EX7","FB7:FS7","FW7:GW7","HB7"},">=0",{"F7:AS7","AW7:CG7","CK7:EE7","EJ7:EX7","FB7:FS7","FW7:GW7","HB7"}/{"$F$5:$AS$5","$AW$5:$CG$5","$CK$5:EE$5","EJ$5:EX$5","FB$5:FS$5","FW$5:GW$5","HB$5"}))

Reply to this Message

Billy B - 30 Jan 2008 21:31 GMT

Bernard,

I am trying to calculate a percentage based on possible points. If a student
has three assignments A,B,C and each is worth 10 points the total possible
would be 30. But, the student has only completed assignment A and received 8
points. The formula result would be 80% because he had only done one
assignment of the three possible. The total should reflect the percent based
on completed work only (row 7) divided by the possible points (row 5) for
only the completed work. Since the range of cells in my workbook for their
homework is not continuous, I am trying to develop a formula that would do
the above. I hope that explains it OK.

> Not clear (to me, anyway) what you are trying to do
> best wishes
[quoted text clipped - 6 lines]
> >
> > =IF(SUM(F7:AS7,AW7:CG7,CK7:EE7,EJ7:EX7,FB7:FS7,FW7:GW7,HB7)=0,0,SUMIF({"F7:AS7"AW7:CG7","CK7:EE7","EJ7:EX7","FB7:FS7","FW7:GW7","HB7"},">=0",{"F7:AS7","AW7:CG7","CK7:EE7","EJ7:EX7","FB7:FS7","FW7:GW7","HB7"}/{"$F$5:$AS$5","$AW$5:$CG$5","$CK$5:EE$5","EJ$5:EX$5","FB$5:FS$5","FW$5:GW$5","HB$5"}))

Reply to this Message

Bob Phillips - 30 Jan 2008 22:04 GMT

Just a thought

=SUMPRODUCT(SUMIF(INDIRECT({"F7:AS7","AW7:CG7","CK7:EE7","EJ7:EX7","FB7:FS7","FW7:GW7","HB7"}),">0"))/
SUMPRODUCT(SUMIF(INDIRECT({"F7:AS7","AW7:CG7","CK7:EE7","EJ7:EX7","FB7:FS7","FW7:GW7","HB7"}),">0",
(INDIRECT({"F5:AS5","AW5:CG5","CK5:EE5","EJ5:EX5","FB5:FS5","FW5:GW5","HB5"}))))

but how about redesigning the data?

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Reply to this Message

Don Guillett - 30 Jan 2008 22:44 GMT

This may? help.
A macro I did recently for a similar problem. Adapt to suit

Sub averagearray()
For Each c In Array(1, 2, 3, 10, 23, 34)
If Cells(c, "a") > 4.5 Then
ms = ms + Cells(c, "a")
cc = cc + 1
End If
Next c
MsgBox ms
MsgBox cc
MsgBox ms / cc
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

Reply to this Message