title: Calculate the total of Amount * occurrences in other cell per row
body: <p>How can I get the sum of amount x times the total count of occurrences of "Qx" in a column in the same row?</p>
  
  <p>Example data: </p>
  
  <p><a href="https://i.stack.imgur.com/Gtvff.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Gtvff.png" alt="enter image description here"></a></p>
  
  <p><strong>What do I want?</strong></p>
  
  <p>I want to count the total of occurrences of Qx and multiply that with the amount on the corresponding row. The sum of whole column should be shown next to the Q2 string (E2:E5). </p>
  
  <p>Example function:</p>
  
  <pre><code>Row 4:
  (Q1 occurrences =) 1 * (Amount in A4 =) 10 = 10
  
  Row 6:
  (Q1 occurrences =) 2 * (Amount in A6 =) 20 = 40
  
  Total to show in E2: 50
  </code></pre>
  
  <p>I can count all the occurrences of Q2 on a single row <code>=(LEN(B5)-LEN(SUBSTITUTE(B5;"Q2";"")))/LEN("Q2")</code>, but I do not know how I should expand this in order to fit my needs. </p>
  
  <p>I think I should use <code>SUMPRODUCT</code> here since I am dealing with multiple ranges, but I cannot seem to find out how I should implement this. Does anyone have any tips on this?</p>