Forum Discussion
SUMPRODUCT Help
Hello!
I'm hoping someone can help with a SUMPRODUCT query. Well, I think SUMPRODUCT is what I am looking for!
I have a table with these headers:
Campus,Region,Country,Year,Series,ResultType,Candidates,#A*,#A,#B,#C,#D,#E,# U
I need to find the weighted percentage of the results PER YEAR:
% A*
% A*-A
% A*-B
% A*-C
% A*-D
% A*-E
Manually, I calculate the results as follows (example for weighted average of A*s):
Campus 1 (candidates*#A*) + Campus 2 (candidates*#A*) + Campus 3 (candidates*#A*) etc. / TOTAL CANDIDATES (in that year)
I can obviously pick the correct year results manually.
Now, however, everything is combined into one big table, and I'd like to use SUMPRODUCT to calculate the weighted averages.
Can anyone help?
Thanks!
6 Replies
- For weighted average of #A* as per your formula,
=SUMPRODUCT((YearsList=YearYouWant)*(ResultType="#A*"))/countif(YearsList,YearYouWant)Thanks very much for your help. I have tried that formula, but it doesn't seem to give me what I need.
I don't think I explained very well how my table is structured. Below is how it looks. So what I am trying to achieve is the weighted percentage per campus and year. For example, for the %A* in 2017:
(107*75)+(131*152)+(94*23) / Total 2017 candidates, i.e. 332.
Campus Region Country Year Results Type Candidates A* A B C D E U Total Campus 1 Europe Spain 2017 Actual 107 75 95 78 79 42 11 1 382 Campus 1 Europe Spain 2018 Actual 100 67 99 92 52 28 9 0 347 Campus 1 Europe Spain 2019 Actual 128 73 89 84 69 32 14 1 362 Campus 2 Americas Peru 2017 Actual 131 152 110 79 60 25 4 0 430 Campus 2 Americas Peru 2018 Actual 131 149 91 82 58 24 11 1 416 Campus 2 Americas Peru 2019 Actual 101 28 37 53 46 27 14 1 206 Campus 3 Europe UK 2017 Actual 94 23 36 68 56 41 14 1 239 Campus 3 Europe UK 2018 Actual 68 17 32 41 58 37 14 0 199 Campus 3 Europe UK 2019 Actual 91 29 25 43 35 25 5 0 162 Thanks for your help.
Actually, I can write the formula that will work automatically by changing Year and
Result(A*,A,B,C,D,E,U).
Let's say your data is converted to table and table name is Table1.
Campus Region Country Year ResultsType Candidates A* A B C D E U Total
Campus 1 Europe Spain 2017 Actual 107 75 95 78 79 42 11 1 382
Campus 1 Europe Spain 2018 Actual 100 67 99 92 52 28 9 0 347
Campus 1 Europe Spain 2019 Actual 128 73 89 84 69 32 14 1 362
Campus 2 Americas Peru 2017 Actual 131 152 110 79 60 25 4 0 430
Campus 2 Americas Peru 2018 Actual 131 149 91 82 58 24 11 1 416
Campus 2 Americas Peru 2019 Actual 101 28 37 53 46 27 14 1 206
Campus 3 Europe UK 2017 Actual 94 23 36 68 56 41 14 1 239
Campus 3 Europe UK 2018 Actual 68 17 32 41 58 37 14 0 199
Campus 3 Europe UK 2019 Actual 91 29 25 43 35 25 5 0 162List of the year and candidates are fixed column that do not need to be changed in the formula and I will keep as they are in the formula. Only formula for Result (A*,A,B,C,D,E,U) will be edited and you may write down the formula as below.
Let's assume, the cell address of Year you want is Y and Result type is R.
=Sumproduct((Year=Y)*Candidates*Index(Table1,0,Match(R,Table1[#Headers],0)))/Sumproduct((Year=Y)*Candidates)
