SUMIFS with two wildcards

yakolev

New Member
Joined
Oct 3, 2019
Messages
4
Hi everyone,

I am trying to use a version of the SUMIF function to convert 'one-way' flight segments to 'round trip' segments (e.g. sum all flights trips Atlanta - Nashville, and Nashville - Atlanta into one row 'Atlanta - Nashville'.

The formulas I come up are quite extensive and long, except the one below which seems ideal, but it still gives me incorrect values as the two wildcards pick up all instances where Atlanta appears + all instances where Nashville appears. The data returned is obviously wrong.

=SUM(SUMIFS(sum_range,criteria_range,{"*Atlanta*","*Nashville*"}))

What I want is ALL instances where Atlanta AND Nashville appear. Is that possible with wildcards?

Also - I am trying to use cell reference (instead of having to type cities "*manually*" I understand you can cell reference with a wildcard by doing "*"A1"*" but when adding another wildcard it doesn't seem to work. Thoughts?

Thanks in advance,
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Try this criteria...

{"*Atlanta*Nashville*","*Nashville*Atlanta*"}

Or this...

{"*"&A1&"*"&B1&"*","*"&B1&"*"&A1&"*"}
 
Upvote 0
Thanks AlphaFrog, but I get an 'invalid' error when trying to enter the below:

{"*"&A1&"*"&B1&"*","*"&B1&"*"&A1&"*"}

I am trying to send a screenshot of the error but can't find a way to submit through here.

Any other ideas? Could it be that you' can't use more than one wildcard?
 
Upvote 0
try...

=SUMIFS(sum_range,criteria_range,"*"&A1&"*"&B1&"*",criteria_range,"*"&B1&"*"&A1&"*")
 
Upvote 0
I had another try now removing the {} and it works only if you use use 1 wildcard.

Anything after the comma (below) creates an error - which makes me suspect one can only use 1 wildcard.

{"*"&A1&"*"&B1&"*","*"&B1&"*"&A1&"*"}
 
Upvote 0
I've tried but it returns 0.

I think it's because of the AND feature of the SUMIFS formula.

Instead of AND I need it to be OR so it could return either Atlanta - Nashville OR viceversa.

But since both can't be true, then it returns 0.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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