Sumifs with contains

[adrienne0914]

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!

 

[jtakw]

Hi,

If I understand correctly, this is one way:

Book1
	A	B	C	D
1	Charge	Amount
2	C5305-R-D - Duty	83,942		831,381
3	C5315-R-D - Gst Paid	411,816
4	5315-R-D - Cisf	2,985
5	5315-E-D - Gst Paid	233,045
6	153-R-D - Cisf	2,985
7	153-R-O - Cisf	32,410
8	VAT-E-D - Customs Vat	62,762
9	5319-E-D - Vat	1,436
Sheet146

Cell Formulas
Range	Formula
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.

 

[DRSteele]

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

Book1
	A	B
1	Charge	Amount
2	C5305-R-D - Duty	83,942
3	C5315-R-D - Gst Paid	411,816
4	5315-R-D - Cisf	2,985
5	5315-E-D - Gst Paid	233,045
6	153-R-D - Cisf	2,985
7	153-R-O - Cisf	32,410
8	VAT-E-D - Customs Vat	62,762
9	5319-E-D - Vat	1,436
10		831,381
Sheet17

Cell Formulas
Range	Formula
B10	{=SUM(IF(ISNUMBER(SEARCH(53,A2:A9))+ISNUMBER(SEARCH("vat",A2:A9)),B2:B9))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[adrienne0914]

Hi,

If I understand correctly, this is one way:

	A	B	C	D
1	Charge	Amount
2	C5305-R-D - Duty	83,942		831,381
3	C5315-R-D - Gst Paid	411,816
4	5315-R-D - Cisf	2,985
5	5315-E-D - Gst Paid	233,045
6	153-R-D - Cisf	2,985
7	153-R-O - Cisf	32,410
8	VAT-E-D - Customs Vat	62,762
9	5319-E-D - Vat	1,436

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

Sheet146​

Worksheet Formulas Cell Formula 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]

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

	A	B
1	Charge	Amount
2	C5305-R-D - Duty	83,942
3	C5315-R-D - Gst Paid	411,816
4	5315-R-D - Cisf	2,985
5	5315-E-D - Gst Paid	233,045
6	153-R-D - Cisf	2,985
7	153-R-O - Cisf	32,410
8	VAT-E-D - Customs Vat	62,762
9	5319-E-D - Vat	1,436
10		831,381

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

Sheet17​

Array Formulas Cell Formula 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]

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]

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.

Charge	Amount
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
TOTAL	420,566

<tbody>
</tbody>

Expected result is 407,296

Thanks again for your help!

 

[Aladin Akyurek]

Book1
	A	B	C	D
1	Charge	Amount		53
2	C01-R-D - Import Gst	23,889		vat
3	3836-E-O - On & Off	2,186		gst
4	5315-E-D - Gst Paid	233,045
5	5315-R-D - Cisf	2,985		407,296
6	6319-E-D - Var	1,436
7	C5305-R-D - Duty	83,942
8	COMV-E-D - Frt Commission Subj To Vat	2,200
9	CRT-R-O - Crating	5,153
10	VAT-R-D - Customs Vat	61,235
11	WO-R-O - Whse Other	4,495
12	TOTAL	420,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))

 

[adrienne0914]

	A	B	C	D
1	Charge	Amount		53
2	C01-R-D - Import Gst	23,889		vat
3	3836-E-O - On & Off	2,186		gst
4	5315-E-D - Gst Paid	233,045
5	5315-R-D - Cisf	2,985		407,296
6	6319-E-D - Var	1,436
7	C5305-R-D - Duty	83,942
8	COMV-E-D - Frt Commission Subj To Vat	2,200
9	CRT-R-O - Crating	5,153
10	VAT-R-D - Customs Vat	61,235
11	WO-R-O - Whse Other	4,495
12	TOTAL	420,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!!

 

[Aladin Akyurek]

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

You are very welcome.