title: SUMIF staff sales bonus index based on product rank
body: <p>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.</p>
  <p>Here is the list of products with weightage of each product.</p>
  <p><a href="https://i.stack.imgur.com/nUebE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nUebE.png" alt="enter image description here" /></a></p>
  <p>Following is the list of sales from which need to construct index.
  <a href="https://i.stack.imgur.com/BmSJ9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BmSJ9.png" alt="enter image description here" /></a></p>
  <p>Here is the list of staff with correctly calculated Bonus Index but with poor formula;
  <a href="https://i.stack.imgur.com/7o70i.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7o70i.png" alt="enter image description here" /></a></p>
  <p>Here is my current formula in cell B2</p>
  <pre><code>=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
  </code></pre>
  <p>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.</p>
  <p>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?</p>
  <p>Thanks for help</p>