Wildcards in SUMIFS formula

[kbdavis11]

I am attempting to use the SUMIFS formula in a spreadsheet, particularly on in-state and out of state totals. The codes for out of state will have a 3 digit number followed by a 2 letter state code followed by another two letter client code. So for example, you may see something like this:

100COAA
120COAA
100MOAA
120MOAA

There are numerous codes such as this, but the only thing I care about is the 2 letter state code. I am needing to sum all the out of state codes and in a separate total I need to sum all the in-state codes.

All the out of states are: CO, KS, MO, NM; and then the in-state doesn't use a two letter code, meaning I will need to use the not equal to '<>' for that formula.

The Sum Range is J:J
The criteria range is B:B

I attempted to use a formula such as this:

=SUMIFS(J:J,B:B,="*CO*",B:B,="*MO*"....) but evidently it doesn't like my wildcards.

then the other formula would be something like this:

=SUMIFS(J:J,B:B,<>"*CO*",B:B,<>"*MO*"....)

Any thoughts??

 

[svendiamond]

Your formula isn't working because it will only SUM column J if B:B is both *CO* and *MO* ... not OR. If you want to say SUM column J if column B is like *CO* OR *MO* then try something like this:

=SUMPRODUCT((B:B="*CO*")+(B:B="*MO*")+(J:J))

 

[BobUmlas]

If the data extends to row 18, ctrl+shift+enter:
=SUM(J1:J18)-SUM(J1:J18*(MID(B1:B18,4,2)={"CO","KS","MO","NM"}))

Adjust the 18 as needed

 

[BobUmlas]

Questioner wanted NOT=. You picked out =. Changing = to <> in your formula doesn't do the job.

 

[ScatmanKyle]

For your first formula, try this:

=SUM(SUMIFS(J:J,B:B,{"*CO*","*MO*"}))

 

[ScatmanKyle]

For your first formula, try this:

=SUM(SUMIFS(J:J,B:B,{"*CO*","*MO*"}))

And then for the "Not" formula, I suppose just take the whole and subtract the "Is". Something like this:

=Sum(J:J)-SUM(SUMIFS(J:J,B:B,{"*CO*","*MO*"}))

 

[kbdavis11]

Thank you all for the replies! I have tried your formulas and they all seem to do the trick just fine!


Thanks again for the assistance!