Forum Discussion

smc1906's avatar
smc1906
Copper Contributor
Mar 18, 2022

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...
BI & Data Analysis
excel
Starrysky1988's avatar
Starrysky1988
Iron Contributor
Mar 18, 2022
For weighted average of #A* as per your formula,
=SUMPRODUCT((YearsList=YearYouWant)*(ResultType="#A*"))/countif(YearsList,YearYouWant)
smc1906's avatar
smc1906
Copper Contributor
Mar 19, 2022

Hi Starrysky1988 

 

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.

 

CampusRegionCountryYearResults TypeCandidatesA*ABCDEUTotal
Campus 1EuropeSpain2017Actual1077595787942111382
Campus 1EuropeSpain2018Actual100679992522890347
Campus 1EuropeSpain2019Actual1287389846932141362
Campus 2AmericasPeru2017Actual13115211079602540430
Campus 2AmericasPeru2018Actual13114991825824111416
Campus 2AmericasPeru2019Actual1012837534627141206
Campus 3EuropeUK2017Actual942336685641141239
Campus 3EuropeUK2018Actual681732415837140199
Campus 3EuropeUK2019Actual91292543352550162

 

Thanks for your help.

  • Starrysky1988's avatar
    Starrysky1988
    Iron Contributor
    Mar 21, 2022

    smc1906 

    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  162

    List 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)

    • smc1906's avatar
      smc1906
      Copper Contributor
      Apr 18, 2022

      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

  • Starrysky1988's avatar
    Starrysky1988
    Iron Contributor
    Mar 21, 2022
    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.

Resources