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,
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,185
Try this criteria...

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

Or this...

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

yakolev

New Member
Joined
Oct 3, 2019
Messages
4
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?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,185
try...

=SUMIFS(sum_range,criteria_range,"*"&A1&"*"&B1&"*",criteria_range,"*"&B1&"*"&A1&"*")
 

yakolev

New Member
Joined
Oct 3, 2019
Messages
4
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&"*"}
 

yakolev

New Member
Joined
Oct 3, 2019
Messages
4
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.
 

Forum statistics

Threads
1,085,309
Messages
5,382,894
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top