Sumifs with contains

adrienne0914

adrienne0914

Board Regular
Joined
Mar 22, 2018
Messages
73
Office Version
  1. 365
Platform
  1. Windows
Looking for help with SUMIFS. I need the sum if Column A contains "53", "Vat" or both.

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Charge[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]C5305-R-D - Duty[/TD]
[TD]83,942[/TD]
[/TR]
[TR]
[TD]C5315-R-D - Gst Paid[/TD]
[TD]411,816[/TD]
[/TR]
[TR]
[TD]5315-R-D - Cisf[/TD]
[TD]2,985[/TD]
[/TR]
[TR]
[TD]5315-E-D - Gst Paid[/TD]
[TD]233,045[/TD]
[/TR]
[TR]
[TD]153-R-D - Cisf[/TD]
[TD]2,985[/TD]
[/TR]
[TR]
[TD]153-R-O - Cisf[/TD]
[TD]32,410[/TD]
[/TR]
[TR]
[TD]VAT-E-D - Customs Vat[/TD]
[TD]62,762[/TD]
[/TR]
[TR]
[TD]5319-E-D - Vat[/TD]
[TD]1,436[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for any help!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Hi,

If I understand correctly, this is one way:


Book1
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
Sheet146
Cell Formulas
RangeFormula
D2=SUM(SUMIFS(B2:B9,A2:A9,{"*53*","*Vat*"}))-SUMIFS(B2:B9,A2:A9,"*53*",A2:A9,"*Vat*")


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.
 
Upvote 0
Using SUMIFS is fine here. But you can also try this array formula:


Book1
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
Sheet17
Cell Formulas
RangeFormula
B10{=SUM(IF(ISNUMBER(SEARCH(53,A2:A9))+ISNUMBER(SEARCH("vat",A2:A9)),B2:B9))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
jtakw said:
Hi,

If I understand correctly, this is one way:

ABCD
ChargeAmount
C5305-R-D - Duty
C5315-R-D - Gst Paid
5315-R-D - Cisf
5315-E-D - Gst Paid
153-R-D - Cisf
153-R-O - Cisf
VAT-E-D - Customs Vat
5319-E-D - Vat

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]83,942[/TD]
[TD="align: right"][/TD]
[TD="align: right"]831,381[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]411,816[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2,985[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]233,045[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2,985[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]32,410[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]62,762[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1,436[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet146​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=SUM(SUMIFS(B2:B9,A2:A9,{"*53*","*Vat*"}))-SUMIFS(B2:B9,A2:A9,"*53*",A2:A9,"*Vat*")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.
Click to expand...


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!
 
Upvote 0
DRSteele said:
Using SUMIFS is fine here. But you can also try this array formula:

AB
C5305-R-D - Duty
C5315-R-D - Gst Paid
5315-R-D - Cisf
5315-E-D - Gst Paid
153-R-D - Cisf
153-R-O - Cisf
VAT-E-D - Customs Vat
5319-E-D - Vat

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Charge[/TD]
[TD="bgcolor: #FFF2CC"]Amount[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]83,942[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]411,816[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2,985[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]233,045[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2,985[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]32,410[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]62,762[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1,436[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]831,381[/TD]

</tbody>
Sheet17​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B10[/TH]
[TD="align: left"]{=SUM(IF(ISNUMBER(SEARCH(53,A2:A9))+ISNUMBER(SEARCH("vat",A2:A9)),B2:B9))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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?
 
Upvote 0
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.
 
Upvote 0
jtakw said:
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.
Click to expand...

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Charge[/TD]
[TD="align: center"]Amount[/TD]
[/TR]
[TR]
[TD][TABLE="width: 318"]
<tbody>[TR]
[TD="class: xl63, width: 318"]C01-R-D - Import Gst[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]23,889[/TD]
[/TR]
[TR]
[TD][TABLE="width: 318"]
<tbody>[TR]
[TD="class: xl65, width: 318"]3836-E-O - On & Off[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]2,186[/TD]
[/TR]
[TR]
[TD][TABLE="width: 318"]
<tbody>[TR]
[TD="class: xl65, width: 318"]5315-E-D - Gst Paid[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]233,045[/TD]
[/TR]
[TR]
[TD][TABLE="width: 318"]
<tbody>[TR]
[TD="class: xl65, width: 318"]5315-R-D - Cisf[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]2,985[/TD]
[/TR]
[TR]
[TD][TABLE="width: 318"]
<tbody>[TR]
[TD="class: xl65, width: 318"]6319-E-D - Var[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]1,436[/TD]
[/TR]
[TR]
[TD][TABLE="width: 318"]
<tbody>[TR]
[TD="class: xl65, width: 318"]C5305-R-D - Duty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]83,942[/TD]
[/TR]
[TR]
[TD][TABLE="width: 318"]
<tbody>[TR]
[TD="class: xl65, width: 318"]COMV-E-D - Frt Commission Subj To Vat[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]2,200[/TD]
[/TR]
[TR]
[TD][TABLE="width: 318"]
<tbody>[TR]
[TD="class: xl65, width: 318"]CRT-R-O - Crating[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]5,153[/TD]
[/TR]
[TR]
[TD][TABLE="width: 318"]
<tbody>[TR]
[TD="class: xl65, width: 318"]VAT-R-D - Customs Vat[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]61,235[/TD]
[/TR]
[TR]
[TD][TABLE="width: 318"]
<tbody>[TR]
[TD="class: xl65, width: 318"]WO-R-O - Whse Other[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]4,495[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD="align: right"]420,566[/TD]
[/TR]
</tbody>[/TABLE]

Expected result is 407,296

Thanks again for your help!
 
Upvote 0

Book1
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
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))
 
Upvote 0
Aladin Akyurek said:
ABCD
Charge
vat
3836-E-O - On & Offgst
6319-E-D - Var
CRT-R-O - Crating
WO-R-O - Whse Other
TOTAL

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Amount[/TD]
[TD="align: right"][/TD]
[TD="align: right"]53[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFF00"]C01-R-D - Import Gst[/TD]
[TD="align: right"]23,889[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2,186[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFF00"]5315-E-D - Gst Paid[/TD]
[TD="align: right"]233,045[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFF00"]5315-R-D - Cisf[/TD]
[TD="align: right"]2,985[/TD]
[TD="align: right"][/TD]
[TD="align: right"]407,296[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1,436[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFF00"]C5305-R-D - Duty[/TD]
[TD="align: right"]83,942[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFF00"]COMV-E-D - Frt Commission Subj To Vat[/TD]
[TD="align: right"]2,200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]5,153[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFF00"]VAT-R-D - Customs Vat[/TD]
[TD="align: right"]61,235[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]4,495[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]420,566[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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))
Click to expand...

Awesome! This is perfect because the specs on what to sum keep changing. Thanks so much!! :grin:
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Macro/VBA Help with Attempting to Consolidate Multiple Worksheets into One.... with a Twist
Replies
5
Views
1K
doublejtacoma
D

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top