Forum Discussion
SUMPRODUCT Help
=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)Hello Starrysky1988
Thanks so much for your help and support so far.
I've tried the options you mentioned but have not been able to get it working quite right, I must be making a mistake. I've attached an Excel showing what I am trying to work on. The Group Results tab is where I am trying to place the formula, and I've added a comment on what the value should be in a couple of cells. Essentially, it is the %s at each level (A*, A*-A, A*-B etc.) multiplied by number of students per school, divided by total students.
As you will see on the Excel, I get an #VALUE message.
Are you able to help?
Many thanks
- Please use the formula below for A* and you may change the year 2017 to what you want.
=Sumproduct((Year=2017)*Candidates*A*)/Sumproduct((Year=2017)*Candidates)
Year, Candidates and A* represent all the relevant list.
