Perhaps this set up can be used:
A1:H1 contains these headings:
Question....Criteria_01....Criteria_02....Criteria_03....Score....Crit_01_Base....Crit_01_Base....Crit_01_Base
The Crit fields contain the base number for each criteria. Any Col_B through Col_D values will be added to the base.
Examples:
Question....Criteria_01....Criteria_02....Criteria_03....Score....Crit_01_Base....Crit_01_Base....Crit_01_Base
Question1 1 1 1 1 0 0
Question2 1 1 1 9 9 0
The score is calculated using this formula, copied down:
E2: =SUMPRODUCT((B2:D2>0)*F2:H2)+SUM(B2:D2)
With that example, these are the results:
Question Criteria_01 Criteria_02 Criteria_03 Score Crit_1_Base Crit_2_Base Crit_3_Base
Question1 1 1 1 4 1 0 0
Question2 2 1 0 21 9 9 0
Is that something you can work with?
Bookmarks