Sumifs with contains

adrienne0914

Board Regular
Joined
Mar 22, 2018
Messages
54
Looking for help with SUMIFS. I need the sum if Column A contains "53", "Vat" or both.

ChargeAmount
C5305-R-D - Duty83,942
C5315-R-D - Gst Paid411,816
5315-R-D - Cisf2,985
5315-E-D - Gst Paid233,045
153-R-D - Cisf2,985
153-R-O - Cisf32,410
VAT-E-D - Customs Vat62,762
5319-E-D - Vat1,436

<tbody>
</tbody>

Thanks for any help!
 

Some videos you may like

Excel Facts

Add Bullets to Range
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
Joined
Jun 29, 2014
Messages
5,146
Hi,

If I understand correctly, this is one way:

<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=";">Charge</td><td style=";">Amount</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">C5305-R-D - Duty</td><td style="text-align: right;;">83,942</td><td style="text-align: right;;"></td><td style="text-align: right;;">831,381</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">C5315-R-D - Gst Paid</td><td style="text-align: right;;">411,816</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">5315-R-D - Cisf</td><td style="text-align: right;;">2,985</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">5315-E-D - Gst Paid</td><td style="text-align: right;;">233,045</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">153-R-D - Cisf</td><td style="text-align: right;;">2,985</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">153-R-O - Cisf</td><td style="text-align: right;;">32,410</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">VAT-E-D - Customs Vat</td><td style="text-align: right;;">62,762</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">5319-E-D - Vat</td><td style="text-align: right;;">1,436</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;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)">Sheet146</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=SUM(<font color="Blue">SUMIFS(<font color="Red">B2:B9,A2:A9,{"*53*","*Vat*"}</font>)</font>)-SUMIFS(<font color="Blue">B2:B9,A2:A9,"*53*",A2:A9,"*Vat*"</font>)</td></tr></tbody></table></td></tr></table><br />

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
Joined
Mar 31, 2015
Messages
2,284
Office Version
  1. 365
Platform
  1. Windows
Using SUMIFS is fine here. But you can also try this array formula:

<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 /></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></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="background-color: #FFF2CC;;">Charge</td><td style="background-color: #FFF2CC;;">Amount</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">C5305-R-D - Duty</td><td style="text-align: right;;">83,942</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">C5315-R-D - Gst Paid</td><td style="text-align: right;;">411,816</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">5315-R-D - Cisf</td><td style="text-align: right;;">2,985</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">5315-E-D - Gst Paid</td><td style="text-align: right;;">233,045</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">153-R-D - Cisf</td><td style="text-align: right;;">2,985</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">153-R-O - Cisf</td><td style="text-align: right;;">32,410</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">VAT-E-D - Customs Vat</td><td style="text-align: right;;">62,762</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">5319-E-D - Vat</td><td style="text-align: right;;">1,436</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">831,381</td></tr></tbody></table><p style="width:5.6em;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)">Sheet17</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B10</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">SEARCH(<font color="Purple">53,A2:A9</font>)</font>)+ISNUMBER(<font color="Green">SEARCH(<font color="Purple">"vat",A2:A9</font>)</font>),B2:B9</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try to enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

adrienne0914

Board Regular
Joined
Mar 22, 2018
Messages
54
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet146

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

<thead>
</thead><tbody>
</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!
 

adrienne0914

Board Regular
Joined
Mar 22, 2018
Messages
54

ADVERTISEMENT

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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet17

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

<thead>
</thead><tbody>
</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
Joined
Jun 29, 2014
Messages
5,146
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.
 

adrienne0914

Board Regular
Joined
Mar 22, 2018
Messages
54

ADVERTISEMENT

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

Thanks again for your help!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
<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))
 

adrienne0914

Board Regular
Joined
Mar 22, 2018
Messages
54
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</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!! :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,584
Messages
5,523,720
Members
409,532
Latest member
Lmfacc

This Week's Hot Topics

Top