<div>
Hi,<br />
pls try<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42438773-1"><span>=SUMIFS(Report!H:H,Report!Q:Q,&quot;Civilian&quot;,Report!Z:Z,&quot;Sold&quot;)</span>
</code></pre>
or<br />

<pre><code class="code-1 nolinks" id="code-20-42438773-2"><span>=SUMIFS(Report!H:H,Report!Q:Q,&quot;Civilian&quot;,Report!S:S,&quot;06*&quot;,Report!Z:Z,&quot;Sold&quot;)</span>
</code></pre>
REgards
</div>


<div>
I made a mistake in the original question it needs to be OR and not AND question was edited
</div>


<div>
then try<br />

<pre><code class="code-1 nolinks" id="code-20-42438815-1"><span>=SUMIFS(Report!H:H,Report!Q:Q,&quot;Civilian&quot;,Report!S:S,&quot;06*&quot;)+SUMIFS(Report!H:H,,Report!Z:Z,&quot;Sold&quot;Report!S:S,&quot;06*&quot;)-SUMIFS(Report!H:H,Report!Q:Q,&quot;Civilian&quot;,Report!Z:Z,&quot;Sold&quot;,Report!S:S,&quot;06*&quot;)</span>
</code></pre>
</div>


<div>
tried this but did not work <br />
=SUMIFS(Report!H:H,Report!<wbr />Q:Q,&quot;Civil<wbr />ian&quot;,Repor<wbr />t!S:S,&quot;06*<wbr />&quot;),SUMIFS(<wbr />Report!H:H<wbr />,Report!Q:<wbr />Q,&quot;Civilia<wbr />n&quot;,Report!<wbr />S:S,&quot;05*&quot;,<wbr />Report!Z:Z<wbr />,&quot;Sold&quot;)
</div>


<div>
=SUMIFS(Report!H:H,Report!<wbr />Q:Q,&quot;Civil<wbr />ian&quot;,Repor<wbr />t!S:S,&quot;06*<wbr />&quot;)+SUMIFS(<wbr />Report!H:H<wbr />,,Report!Z<wbr />:Z,&quot;Sold&quot;R<wbr />eport!S:S,<wbr />&quot;06*&quot;)-SUM<wbr />IFS(Report<wbr />!H:H,Repor<wbr />t!Q:Q,&quot;Civ<wbr />ilian&quot;,Rep<wbr />ort!Z:Z,&quot;S<wbr />old&quot;,Repor<wbr />t!S:S,&quot;06*<wbr />&quot;)<br />
<br />
this one got an error pointing to ,&quot;Sold&quot;Report
</div>


<div>
Corrected
<pre><code class="code-1 nolinks" id="code-20-42438841-1"><span>=SUMIFS(Report!H:H,Report!Q:Q,&quot;Civilian&quot;,Report!S:S,&quot;06*&quot;)+SUMIFS(Report!H:H,Report!Z:Z,&quot;Sold&quot;,Report!S:S,&quot;06*&quot;)-SUMIFS(Report!H:H,Report!Q:Q,&quot;Civilian&quot;,Report!Z:Z,&quot;Sold&quot;,Report!S:S,&quot;06*&quot;)</span>
</code></pre>
</div>


<div>
I tested the solution but not sure it works...<br />
<br />
the requirements was <br />
<br />
<b>contains</b> SOLD isn't the formula set for = SOLD
</div>


<div>
<div class="content wysiwyg-content">
I have the following excel SUMIFS which works well but now I also need to add to the sum column H When Q = Civilian OR column Z contains SOLD<br />
<br />
=SUMIFS(Report!H:H,Report!<wbr />Q:Q,&quot;Civil<wbr />ian&quot;,Repor<wbr />t!S:S,&quot;06*<wbr />&quot;)
</div>
</div>


I have the following excel SUMIFS which works well but now I also need to add to the sum column H When Q = Civilian OR column Z contains SOLD

=SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!S:S,"06*")

Help with an excel formula

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

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.