Excel formula calculation 70
<p>I am trying to come up with a formula that possibly uses the sumproduct function combined with another function.</p>

<p>Let me start by explaining what I have and what I want. If I have a list of 3 product numbers or more in a column, I would like excel to find the product numbers in 2 different possible columns and if they are listed there, I would like excel to multiply and sum.</p>

<p>I am currently using this formula:</p>

<pre><code>=SUM(SUMPRODUCT(($B$27:$B$46='Lists for Estimates'!K2)*($E$27:$E$46*$A$27:$A$46))
+SUM(SUMPRODUCT(($F$27:$F$46='Lists for Estimates'!K2)*($I$27:$I$46*$A$27:$A$46)))
+SUM(SUMPRODUCT(($B$27:$B$46='Lists for Estimates'!K3)*($E$27:$E$46*$A$27:$A$46))
+SUM(SUMPRODUCT(($F$27:$F$46='Lists for Estimates'!K3)*($I$27:$I$46*$A$27:$A$46)))))
</code></pre>

<p>The issue with this is I have to continue to add to the formula if I add more to "Lists for Estimates." I would like to change the formula from 'List for Estimates'!K3 and K2 to search the entire column.</p>

<p>Thank you in advance!</p>