If/Then Statements with Possible AND/OR or Nesting Stmts

[Kim-Kay]

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, the second tab has the actual values of each "x".  

Tab 1 is similar to this:

SECTION 1	Inadequate	Below Std	Standard	Above Std	Superior	Rating
Question 1
Question 2
Question 3
Q4
Q5
TOTAL

Tab 2 has the following:

DEFAULT VALUES for each "X" are determined by a formula where numbers may change, so I did not provide any of the actual default values in the chart below.

SECTION 1	Inadequate Default Value	Below Std Default Value	Standard Default Value	Above Std Default Value	Superior Default Value
Q1
Q2
Q3
Q4
Q5

Also on tab 2, I have a chart exactly like the one immediately above.  Each cell contains a formula that pulls the default value of whichever column has an "x" for each question that was answered on tab 1.

NOW... there are five sections total (I'm only showing the info for one).  If an "x" is placed in ANY of the Inadequate or Below Standard columns and there is no "x" in ANY of the Above Standard or Superior columns, I need the sum of the assigned values to calculate in the Rating TOTAL on the 1st tab as they are.  Likewise, if an "x" is placed in the Above Standard or Superior columns but there is no "x" in  ANY of the Inadequate or Below Standard columns, I also need the sum of the assigned values to calculate in the Rating TOTAL on the 1st tab as they are.

BUT... If an "x" is placed in ANY of the Inadequate or Below Standard columns and there is an "x" anywhere in the Above Standard or Superior column, then I need to limit the Rating TOTAL on tab 1 to the Total Standard Default Rating (not the values assigned, but the actual default total for Standard).

Can anyone help me with this?  I'm getting a few too many variables that I can't seem to work around.

[#2]

@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.

[#3]

Not working for what I'm needing, but my explanation probably wasn't great, either. The charts you have are good and the values are pulling over. And yes, the "standard" rating is universally accepted, as in your example. But, the standard maximum isn't being applied.
To make it what I'm needing... only one column can be chosen for each question and each question is to have the default value assigned and shown in the Rating column (which you already have shown). But when figuring the total rating, there is a standard maximum that needs to apply this way: (1) If an inadequate or below standard rating is marked for any question in the section and no above standard or superior ratings are marked, then the sum of the default values that were chosen is to show, (2) Same if only standard ratings are chosen - the sum of the default values that were chosen is to show, (3) If above standard or superior ratings are chosen and no inadequate or below standard ratings are chosen, then the sum of the values that were chosen is to show, (4) If above standard or superior ratings are chosen, and inadequate or below standard ratings are chosen as well, I want the the total to only show as the total maximum sum that can show for the standard ratings.
For example, if I rate Q1 and Q2 as inadequate, Q3 and Q4 are standard, and Q5 is above standard, I want the total rating for the section to be 15 (which is the sum of all standard values possible). I am actually rating the job performance of people using the form and I don't want their insufficiency in one or more areas to pull down their total more than the standard rating if they are excelling in one or more other areas.
I hope this makes more sense that I think it sounds to myself!

[#4]

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.

[#5 · Accepted solution]

@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)),"")

[#6]

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!