Search code examples
google-sheets-formulasumifs

SUMIF staff sales bonus index based on product rank

I want to develop staff sales bonus index in google sheets based on product rank. So if the staff sells one brick their bonus index will be 1 and if they sell 1 cement bag, their bonus index will be 1200.

Here is the list of products with weightage of each product.

enter image description here

Following is the list of sales from which need to construct index. enter image description here

Here is the list of staff with correctly calculated Bonus Index but with poor formula; enter image description here

Here is my current formula in cell B2

=sumifs(K:K,H:H,A2,J:J,E$2)*F$2+sumifs(K:K,H:H,A2,J:J,E$3)*F$3+sumifs(K:K,H:H,A2,J:J,E$4)*F$4

As you can see there is issue with above formula that we need to add a sumifs for each product, so if there are 100+ products, this formula won't work.

Can above formula be combined to one single condition? May be possible with arrays but I am not good with arrays or may be some other way?

Thanks for help

Solution

  • You can use SUMPRODUCT function:

    =SUMPRODUCT((A2=$H$2:$H$16)*(TRANSPOSE($E$2:$E$4)=$J$2:$J$16)*TRANSPOSE($F$2:$F$4)*$K$2:$K$16)

    enter image description here