# Sumifs with contains

##### Board Regular
Looking for help with SUMIFS. I need the sum if Column A contains "53", "Vat" or both.

 Charge Amount C5305-R-D - Duty 83,942 C5315-R-D - Gst Paid 411,816 5315-R-D - Cisf 2,985 5315-E-D - Gst Paid 233,045 153-R-D - Cisf 2,985 153-R-O - Cisf 32,410 VAT-E-D - Customs Vat 62,762 5319-E-D - Vat 1,436

<tbody>
</tbody>

Thanks for any help!

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### jtakw

##### Well-known Member
Hi,

If I understand correctly, this is one way:

The reason I'm unsure this is what you want, you didn't provide an Expected result, and for your sample, it happens that the entire Column A matches the criterion you mentioned.

#### DRSteele

##### Well-known Member
Using SUMIFS is fine here. But you can also try this array formula:

<b>Note: Do not try to enter the {} manually yourself</b></td></tr></table><br />

Last edited:

##### Board Regular
Hi,

If I understand correctly, this is one way:

ABCD
1ChargeAmount
2C5305-R-D - Duty83,942831,381
3C5315-R-D - Gst Paid411,816
45315-R-D - Cisf2,985
55315-E-D - Gst Paid233,045
6153-R-D - Cisf2,985
7153-R-O - Cisf32,410
8VAT-E-D - Customs Vat62,762
95319-E-D - Vat1,436

</tbody>
Sheet146

Worksheet Formulas
CellFormula
D2=SUM(SUMIFS(B2:B9,A2:A9,{"*53*","*Vat*"}))-SUMIFS(B2:B9,A2:A9,"*53*",A2:A9,"*Vat*")

</tbody>

<tbody>
</tbody>

The reason I'm unsure this is what you want, you didn't provide an Expected result, and for your sample, it happens that the entire Column A matches the criterion you mentioned.

My bad! I just realized that they all fall into that criteria. There are thousands of cells in the actual file with just a few matching the criteria. I just wasn't sure how to use OR with SUMIFS. I tried this formula out. I think the final result is higher than it should be, but I can definitely work with it. Thanks!

##### Board Regular

Using SUMIFS is fine here. But you can also try this array formula:

AB
1ChargeAmount
2C5305-R-D - Duty83,942
3C5315-R-D - Gst Paid411,816
45315-R-D - Cisf2,985
55315-E-D - Gst Paid233,045
6153-R-D - Cisf2,985
7153-R-O - Cisf32,410
8VAT-E-D - Customs Vat62,762
95319-E-D - Vat1,436
10831,381

</tbody>
Sheet17

Array Formulas
CellFormula
B10{=SUM(IF(ISNUMBER(SEARCH(53,A2:A9))+ISNUMBER(SEARCH("vat",A2:A9)),B2:B9))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>

I got a #N/A error with this formula. I did enter with Ctrl+Shift+Enter. I don't need to put 53 in quotation marks or make it or "vat" a wildcard?

#### jtakw

##### Well-known Member
No problem at all.

If my formula isn't giving you correct results, please provide a sample (with the expected result) where my formula fails, may be we can get it sorted out.

##### Board Regular

No problem at all.

If my formula isn't giving you correct results, please provide a sample (with the expected result) where my formula fails, may be we can get it sorted out.

In looking at it again, I think your formula is right. But what if I wanted to add another condition... say "gst"... So sum if Column A contains "53", "Vat", "gst" or a combination of these.

ChargeAmount
 C01-R-D - Import Gst

<tbody>
</tbody>
23,889
 3836-E-O - On & Off

<tbody>
</tbody>
2,186
 5315-E-D - Gst Paid

<tbody>
</tbody>
233,045
 5315-R-D - Cisf

<tbody>
</tbody>
2,985
 6319-E-D - Var

<tbody>
</tbody>
1,436
 C5305-R-D - Duty

<tbody>
</tbody>
83,942
 COMV-E-D - Frt Commission Subj To Vat

<tbody>
</tbody>
2,200
 CRT-R-O - Crating

<tbody>
</tbody>
5,153
 VAT-R-D - Customs Vat

<tbody>
</tbody>
61,235
 WO-R-O - Whse Other

<tbody>
</tbody>
4,495
TOTAL420,566

<tbody>
</tbody>

Expected result is
407,296

##### MrExcel MVP
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Charge</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Amount</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">53</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">C01-R-D - Import Gst</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">23,889</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">vat</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3836-E-O - On & Off</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">2,186</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">gst</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">5315-E-D - Gst Paid</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">233,045</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">5315-R-D - Cisf</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">2,985</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">407,296</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6319-E-D - Var</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">1,436</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">C5305-R-D - Duty</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">83,942</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">COMV-E-D - Frt Commission Subj To Vat</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">2,200</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">CRT-R-O - Crating</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">5,153</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">VAT-R-D - Customs Vat</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">61,235</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">WO-R-O - Whse Other</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">4,495</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">TOTAL</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">420,566</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p>

In D5 control+shift+enter, not just enter:

=SUM(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE(D1:D3),\$A\$2:\$A\$11))+0,ROW(D1:D3)^0),\$B\$2:\$B\$11))

##### Board Regular
ABCD
1ChargeAmount53
2C01-R-D - Import Gst23,889vat
33836-E-O - On & Off2,186gst
45315-E-D - Gst Paid233,045
55315-R-D - Cisf2,985407,296
66319-E-D - Var1,436
7C5305-R-D - Duty83,942
8COMV-E-D - Frt Commission Subj To Vat2,200
9CRT-R-O - Crating5,153
10VAT-R-D - Customs Vat61,235
11WO-R-O - Whse Other4,495
12TOTAL420,566

</tbody>
Sheet1

In D5 control+shift+enter, not just enter:

=SUM(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE(D1:D3),\$A\$2:\$A\$11))+0,ROW(D1:D3)^0),\$B\$2:\$B\$11))

Awesome! This is perfect because the specs on what to sum keep changing. Thanks so much!!

##### MrExcel MVP
Awesome! This is perfect because the specs on what to sum keep changing. Thanks so much!!

You are very welcome.

Replies
5
Views
627