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","E J7: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"}))

Not clear (to me, anyway) what you are trying to do
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Billy B" wrote in message
...
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","E J7: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"}))

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.

"Bernard Liengme" wrote:

Not clear (to me, anyway) what you are trying to do
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Billy B" wrote in message
...
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","E J7: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"}))

Just a thought

=SUMPRODUCT(SUMIF(INDIRECT({"F7:AS7","AW7:CG7","CK 7: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?

--
---
HTH

Bob


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



"Billy B" wrote in message
...
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","E J7: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"}))

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Billy B" wrote in message
...
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","E J7: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"}))