title: Sumproduct formula returns a #VALUE! error when the last array refers to a column with formulas in every row. MS Excel 2010
body: <p>I am trying to find an easy way to calculate commissions off of sales on multiple sheets within a workbook.  Each month, I need to find the total net profit for only items sold within the specified month.  </p>
  
  <p>The formula I am currently using is: </p>
  
  <pre><code>=SUMPRODUCT((TEXT('Sheet Name'!$P$3:P24,"MY")=TEXT($G$4,"MY"))*'Sheet Name'!$M$3:M24)
  </code></pre>
  
  <ul>
  <li>Column P shows the Sold Date, </li>
  <li>Column M includes a formula in each row to calculate the net profit, and </li>
  <li>cell G4 is where I would enter the month &amp; year I am currently working with.</li>
  </ul>
  
  <p>I have come to the conclusion that it only gives me the #VALUE! error because of the formula <em>in each row</em> of Column M (example: <code>=IF(OR(F15=0,G15=0)," ",(F15-L15))</code> ).  </p>
  
  <p>When I reference a different column (in place of Column M) that does not contain formulas it works perfectly (example: <code>=SUMPRODUCT((TEXT('Sheet Name'!$P$3:P24,"MY")=TEXT($G$4,"MY"))*'Sheet Name'!$G$3:G24)</code> ). Also, changing the astrisk to a comma causes the formula to calculate incorrectly and add the <code>(--(TEXT</code> double negative does not fix the problem.</p>
  
  <p>How to I get this array to calculate without removing the formulas from Column M?</p>
  
  <p>Thanks for your attention.</p>