Oct 14 2022 01:34 PM
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.
Oct 14 2022 03:18 PM - edited Oct 14 2022 03:20 PM
@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.
Oct 17 2022 08:35 AM
Oct 17 2022 12:13 PM
Oct 17 2022 02:21 PM
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)),"")
Oct 17 2022 02:24 PM
Oct 17 2022 02:21 PM
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)),"")