SUMIFS with two wildcards

[yakolev]

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]

Try this criteria...

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

Or this...

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

 

[yakolev]

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]

try...

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

 

[yakolev]

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]

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.