Forum Discussion
Solved
If/Then Statements with Possible AND/OR or Nesting Stmts
I have two tabs in the same Excel spreadsheet. The first tab is a page that will be completed by the end user, who will answer questions by placing an "x" in the column that they choose. Then, th...
Kim-Kay I'm really not sure if I understand what all you want but here is a first crack at it:
=IF(LEN(CONCAT(B2:F2)),IF(XOR(LEN(B2&C2),LEN(E2&F2)),SUMIF(B2:F2,"<>",Sheet2!B2:F2),Sheet2!D2),"")
BTW you completely excluded mention of the 'standard' rating and if that is universally accepted (as in the attached example) or should be treated as an exclusion in both cases or something else completely.
I think I figured out another way around this. If I total the inadequate and below standard values that were assigned in one cell and total the above standard and superior values in another cell, I am able to do an IF AND statement that says if the two cells with the sums aren't blank, then it should reference the total standard value available (in my case, each standard question is worth 5 points, so the total is 25). Otherwise, add the sum of all the values as assigned.
Kim-Kay If that works great. I think you could also just 'extend' the formula I used for a single line to all the lines:
=IF(LEN(CONCAT(B2:F6)),IF(XOR(LEN(CONCAT(B2:C6)),LEN(CONCAT(E2:F6))),SUMIF(B2:F6,"<>",Sheet2!B2:F6),SUM(Sheet2!D2:D6)),"")- I have someone testing my spreadsheet and all of its functionality. If what I did doesn't work, then I'll try doing yours with the formula extended and go from there. I'll mark your answer as the best response since it will be my next "go to" if what I did doesn't work. Thank you so much for your help! This MS Community has some awesome people in it!