Okay, So I used your function and this is what happened:
I initially tested it out on one cell (C4). My formula was =IF(ISNA(C4 ),"",IF(C4="TestMaster","10",IF(C4=" ","",SUMIF(C4)))) and it returned the correct value of 10 because C4 was a "TestMaster" cell. But when I tried to get that formula to apply to the entire range (C4:U4), it did not work. Here are the variations I attempted to use: 1) =IF(ISNA(C4),"",IF(C4="TestMaster","10",IF(C4=" ","",SUMIF(C4:U4)))) this returned the value of 10 again, when the it should be returning a value of 20 2) =IF(ISNA(C4:U4),"",IF(C4:U4="TestMaster","10",IF(C4:U4=" ","",SUMIF(C4:U4)))) this returned "#VALUE" and the error stated: "Range has no entry corresponding to this cell." 3) =IF(ISNA(C4:U4),"",IF(C4="TestMaster","10",IF(C4=" ","",SUMIF(C4:U4)))) this had the same issue as above (#2). Any idea of where to go from here? On Thursday, July 11, 2013 2:23:24 PM UTC-7, Bob Puffer wrote: > > I'd be looking at nesting the SUMIF function inside a couple of IF > functions that test for your other conditions. EX: > =IF(ISNA(A1),"",IF(A1="TestMaster","",SUMIF(RANGE)))) > > On Thursday, July 11, 2013 2:52:46 PM UTC-5, Josh wrote: >> >> My company uses a spreadsheet to compile employee's scores from training >> quizzes. I need to figure out a way to be able to add all of the employee's >> scores up and then divide them by the total possible points to provide a >> percentage based-score of the all the quizzes. I also need to use a formula >> to create an average quiz score for each employee. >> >> The problem is that the spreadsheet is setup so that if an employee has >> not taken a quiz yet, instead of the score, the respective cell says #NA. >> There are also other employees who created quizzes and their cells in those >> cases say TestMaster. There are also blank cells that need to be >> disregarded in the formula too. >> >> I need to figure out how to setup the SUMIF equation to disregard the #NA >> and TestMaster input. And then, if it isn't to complicated, I'd like to >> remove those scores from the overall score so they are not negatively >> impacted for not having taken a quiz yet. If that is too complicated, then >> I will settle for replacing the #NA for a score of zero and the TestMaster >> for a score of ten. >> >> Similarly, the Average Quiz Score needs to reflect only the quizzes that >> have been taken. And same substitutes for the #NA and TestMaster as above. >> >> I've attached a screen shot of the spreadsheet. It uses an example email >> address so all of the score fields just say #NA but hopefully it will help >> you visualize the issue. >> >> >> -- You received this message because you are subscribed to the Google Groups "Google Spreadsheets API" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
