Adding up values in one table based on values in another table

formula911

New Member
Joined
Nov 29, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I'm using Excel to calculate quality assessment scores conducted by our quality auditors as they audit our order team's order processing quality.

Our auditors are filling out a form with questions about each quality element that captures a Yes, Partial, and No based on how successfully the employee completed each aspect of our order processing flow based on their department's quality criteria. That form data is being captured in an Excel worksheet where I'm converting each question's text response into weighted scores using a separate weighted scoring table, then capturing a total score out of 100 for each audit entry.

That's been working great but now the auditors would like some questions to be answered as "NA" if the quality criteria is not applicable to the specific audit they're entering. In those cases, the audit's score can't be calculated as simply as adding up the total values captured for each audit entry - I now need to exclude any NA values and do the calculation based on only the questions that were submitted with quality scoring results, so instead of out of 100 that denominator needs to vary when a question is submitted with an NA value.

I've made a mockup table below. Range A2:G5 contains each audit's scoring by order and employee, which I'm calculating using the form response results for each question by matching them up the associated value in the weighted scoring table (range A8:E13).

Column H what I have today - just a simple total of the audit's scores. Since it's out of a 100 possible points if the audit responses were "Yes" for all questions, the total is the same as a quality score percentage without me having to do any further calculations.

Column I is where I've been trying to figure out how to calculate the maximum number of possible points so I can factor out any questions that contain the NA value "X". Once I can calculate max points based on response values, I figure I can use column J to do a simple quality score percentage calculation by dividing the total scored points by the max points.

My initial thinking was that for each audit, I'd be able to look at the values in the audited orders range C:G to then add the up the values in the weighted scoring table's values in column B (the "Yes" results being the max value for each question) and sum up only the Yes values for questions that do not have the NA value of "X" in the audit results. But I haven't found a way to do any kind of conditional summing when the conditions I'm using are not all in the same table. I've tried various combinations of SUMIF, index/matches, and array formulas to achieve the result I'm looking for with no success.

TLDR;
I want to be able to calculate the maximum quality score each audit can have by looking at the scoring results for Q1-Q5 for the audit and adding up the maximum available for each result where there is a non-NA value to get the maximum score excluding NA answered questions, then take the total score divided by this varying maximum and produce a quality score percentage.

Test score calc.xlsx
ABCDEFGHI
1ResultsQ1Q2Q3Q4Q5TotalMax%
2John2020103020100100100%
3Bob10201015207510075%
4Fred20101030X708088%
5
6WeightingYesPartialNoNA
7Q120100X
8Q220100X
9Q31050X
10Q430150X
11Q520100X
Sheet1
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,821
How about:

Book2
ABCDEFGHI
1ResultsQ1Q2Q3Q4Q5TotalMax%
2John2020103020100100100%
3Bob10201015207510075%
4Fred20101030X708088%
5
6WeightingYesPartialNoNA
7Q120100X
8Q220100X
9Q31050X
10Q430150X
11Q520100X
Sheet8
Cell Formulas
RangeFormula
G2:G4G2=SUM(B2:F2)
H2:H4H2=MMULT(--(B2:F2<>"X"),$B$7:$B$11)
I2:I4I2=G2/H2
 
Solution

formula911

New Member
Joined
Nov 29, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
How about:

Book2
ABCDEFGHI
1ResultsQ1Q2Q3Q4Q5TotalMax%
2John2020103020100100100%
3Bob10201015207510075%
4Fred20101030X708088%
5
6WeightingYesPartialNoNA
7Q120100X
8Q220100X
9Q31050X
10Q430150X
11Q520100X
Sheet8
Cell Formulas
RangeFormula
G2:G4G2=SUM(B2:F2)
H2:H4H2=MMULT(--(B2:F2<>"X"),$B$7:$B$11)
I2:I4I2=G2/H2
Very cool, I've never used the MMULT function before! Thanks, Eric! That did the trick. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,795
Messages
5,638,375
Members
417,024
Latest member
Mrpica01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top