<div>
you could write your formula as =sumproduct(sumif(J7:121,&quot;<wbr />x&quot;, section to sum )<br />
<br />
e.g. from my simple test sheet <br />
<br />
=SUMPRODUCT(SUMIF(N11:N26,<wbr />&quot;x&quot;,M11:M2<wbr />6),(SUMIF(<wbr />P11:P26,&quot;x<wbr />&quot;,O11:O26)<wbr />))<br />
<br />
in this scenario when I worked it out, I did NOT take subtotal into account, however, I ensured that there were no &quot;x&quot;s within the subtotal columns.
</div>


<div>
Thanks for the quick response. <br />
I tried your solution out but it does not operate the sumproduct function on visible rows with &nbsp;an &quot;x&quot;. Instead, it sums all visible row values (sumif) with and without an &quot;X&quot;. I am looking to calculate the sumproduct of two columns of visible, filtered rows that have an&quot;X&quot; in a third column using an added criteria. &nbsp; I tried two variations using sumif but both did not operate correctly on visible rows only.
</div>


<div>
Hello berrymetz, did you try my suggestion above? I think it will do what you want.....<br />
<br />
regards, barry
</div>


<div>
Thanks Barry<br />
This is exactly the simple, elegant formula I needed. <br />
Berry
</div>


<div>
<div class="content wysiwyg-content">
I am using this formula to return the product of two columns fonly or visible [filtered] rows. &nbsp;Now I would like to include the criteria, J7:J121=&quot;X&quot;, to &nbsp;=SUMPRODUCT(SUBTOTAL(9,OFF<wbr />SET($C7:$C<wbr />121,ROW($C<wbr />7:$C121)-M<wbr />IN(ROW($C7<wbr />:$C121)),0<wbr />,1)),$I7:$<wbr />I121)... so that the formula selects from visible rows only rows with an &quot;X&quot; in column J. &nbsp;I plan to use this formula as the divisor in column headings to calculate percent completion of each columns of data based on the new formula that uses the added criteria, J7:J121=&quot;X&quot; to further exclude rows without an &quot;X&quot; in column J [or K, L, M...] <br />
Thank you in advance<br />
berrymetz &nbsp; 
</div>
</div>


I am using this formula to return the product of two columns fonly or visible [filtered] rows.  Now I would like to include the criteria, J7:J121="X", to  =SUMPRODUCT(SUBTOTAL(9,OFFSET($C7:$C121,ROW($C7:$C121)-MIN(ROW($C7:$C121)),0,1)),$I7:$I121)... so that the formula selects from visible rows only rows with an "X" in column J.  I plan to use this formula as the divisor in column headings to calculate percent completion of each columns of data based on the new formula that uses the added criteria, J7:J121="X" to further exclude rows without an "X" in column J [or K, L, M...] 
Thank you in advance
berrymetz   

How to sumproduct using subtotal of visible rows with criteria

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.