Total Commission Calculation Using VLookUp | General Excel Questions & Answers

Total Commission Calculation Using VLookUp

Tererai Muridzo

Harare,Zimbabwe

Member

Members

Forum Posts: 8

Member Since:
February 5, 2016

Offline

January 30, 2017 - 10:46 pm

hi guys

What is the total commission made by Jackie? from the attached ,had used the formula below and its not working

"=sum(VLOOKUP("jackie",$B$5:$N$17,{((2*C20),(3*D20),(4*e20),(5*f20),(6*g20),(7*h20),(8*i20),(9*j20),(10*k20),(11*l20),(12*m20),(13*n20)},0)

Attached is the file

Regards

Tererai

See Answer

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 221

Member Since:
April 21, 2015

Offline

January 31, 2017 - 6:13 am

Don't understand completely what your formula is doing. But: in your formula you spelled the name without the capital "J" and because you looked for an exact match (the zero at the back of the formula) that's maybe one point to check out?

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 221

Member Since:
April 21, 2015

Offline

January 31, 2017 - 6:18 am

Second one. I don't see anything counting other than the percentages on row 20. So that doesn't work either. There is no relation between something in the table (lets say the values on the row of Jackie) and the commission and the counting then. Can you explain what you want to be counted? You are using two formulas in the wrong way together, as I see it at the moment

Tererai Muridzo

Harare,Zimbabwe

Member

Members

Forum Posts: 8

Member Since:
February 5, 2016

Offline

January 31, 2017 - 6:43 pm

Hi Frans

thank you ,got it ,check below formula for the calculation

=SUMPRODUCT(INDEX(C5:N17,MATCH("Jackie",B5:B17,0),),C20:N20)