# Solved Multiple IFs in Excel

November 26, 2020 at 02:55:48
Specs: Windows 8
 I'm creating a worksheet where I can get the score of each employee in a risk evaluation. A2 = Employee NameB2 = Question ID. The value of the cell can be numbers 92 to 100 or 115 or 116.C2 = Procedure IDD2 = Pass, Fail or N/AE2 = Points lostI need a formula in E2 for these equations:1. If D2=Fail and A2=92, 93, 94, 96, 97, 115 or 116 then TRUE (1), FALSE (0)2. If D2=Fail and A2=95, 98, 99 or 100, then TRUE (10), FALSE (0)Question IDs 92, 93, 94, 96, 97, 115 and 116 are worth 1 point each.Question IDs 95, 98, 99 and 100 are worth 10 points each.F2 = Total Score or (47-E2) / 47Is this possible?

See More: Multiple IFs in Excel

November 26, 2020 at 08:23:51
 Since I do not know your work process, I can only base my answer on your example data.If you put the following formula in F2 and drag it down, it will produce the 77% for John's Pro1234 but...1 - It will return 77% in both F2 and F32 - If multiple employees have the same Pro ID e.g. Pro1234, then all employees with Pro1234 will have their Loss Points included in their results. It would look something like this if Mark also displayed Pro1234:```Employee QID Pro ID Result Loss Points Score John 98 Pro1234 Fail 10 77% John 115 Pro1234 Fail 1 77% John 97 Pro1236 Fail 1 98% Mark 98 Pro1234 Pass 0 77% ```If multiple employees having matching Pro ID's can not happen, then you should be good to go. If that can happen, then I'll need to do some more work.Try this in F2:=(47-SUMIF(\$C\$2:\$C\$5,C2,\$E\$2:\$E\$5))/47Note: Do me a favor. If you have any more criteria that need to be met by the solution, please let me know all of them at once. If you keep adding additional criteria like 'I need to sum John's Point Loss values', at some point all previous work may have to be thrown out and I may have to start from the beginning. It's much easier if I only have to work on one overall solution. Thanks.

#1
November 26, 2020 at 06:28:43
 If A2 contains the Employee Name, how can A2 also contain your numbers?I assume you mean:1. If D2=Fail and B2=92, 93, 94, 96, 97, 115 or 116 then TRUE (1), FALSE (0)2. If D2=Fail and B2=95, 98, 99 or 100, then TRUE (10), FALSE (0)message edited by DerbyDad03

#2
November 26, 2020 at 06:37:09
 Yes, my bad. I was pertaining to B2 for the Question IDs.

#3
November 26, 2020 at 06:42:23
 Try this in E2:=IF(D2="Fail",IF(OR(B2=92,B2=93,B2=94,B2=96,B2=97,B2=115,B2=116),1,10),0)This assumes that Data Validation is used for B2 & D2 so that only those specific values can be chosen.message edited by DerbyDad03

Related Solutions

#4
November 26, 2020 at 07:27:09
 Thanks, DerbyDad03. Follow up question: What if I want to combine the lost points if the procedure ID is the same?```Employee QID Pro ID Result Loss Points Score John 98 Pro1234 Fail 10 79% John 115 Pro1234 Fail 1 98% John 97 Pro1236 Fail 1 98% Mark 98 Pro1237 Pass 0 100% ```For example, John got two fails in QID 98 and 115 for the same procedure. His score for Pro1234 should be (47-11)/47 or 77%. Is there a way that the formula can be enhanced or perhaps another solution so the loss points will be added if the procedure id is the same? The data will be converted to a PivotTable and my goal is to get the score for each employee for each procedure ID on a weekly and monthly basis.

#5
November 26, 2020 at 08:23:51
 Since I do not know your work process, I can only base my answer on your example data.If you put the following formula in F2 and drag it down, it will produce the 77% for John's Pro1234 but...1 - It will return 77% in both F2 and F32 - If multiple employees have the same Pro ID e.g. Pro1234, then all employees with Pro1234 will have their Loss Points included in their results. It would look something like this if Mark also displayed Pro1234:```Employee QID Pro ID Result Loss Points Score John 98 Pro1234 Fail 10 77% John 115 Pro1234 Fail 1 77% John 97 Pro1236 Fail 1 98% Mark 98 Pro1234 Pass 0 77% ```If multiple employees having matching Pro ID's can not happen, then you should be good to go. If that can happen, then I'll need to do some more work.Try this in F2:=(47-SUMIF(\$C\$2:\$C\$5,C2,\$E\$2:\$E\$5))/47Note: Do me a favor. If you have any more criteria that need to be met by the solution, please let me know all of them at once. If you keep adding additional criteria like 'I need to sum John's Point Loss values', at some point all previous work may have to be thrown out and I may have to start from the beginning. It's much easier if I only have to work on one overall solution. Thanks.