I have a group of cells on a tab with a list of cities and states, multiple cities and multiple states span down this column. for instance column A on this tab is



Atlanta, Georgia
Savannah, Georgia
Augusta Georgia

they have a corresponding column with a number of users in each city. I also have a second tab with a list of all the states in one column, and a corresponding column on that tab with total users per state. it's basically just a roll-up from the previous tab.

So I need a formula that can sumif the first city/state tab to see if it has the word "Georgia" in a cell and show the total for all the cities in Georgia on this second tab. The problem with using the wildcard here is that there are multiple countries involved in this exercise so writing out the wildcard each time would be very time consuming.

Really the sumif is set up perfectly since each tab has a column that does include the state name in it, however one of those tabs has additional words as well. the tab is locked by a client or else I would do a cheap text to columns to separate the city from the state on the first tab.

any help is appreciated!

You can extract those columns from that secured tab, to another tab and then do the text to columns in that new tab....

However, I don't see why the wildcard won't work.

If you have the word "Georgia" in say A2 of your summary tab, why couldn't you use =SUMIF('Other tab'!A:A,"*"&A2&"*",'Other tab'!B:B)

This will look at A2 for the word to search for in column A of the Other tab, and sum column B.

You can copy formula down, for example, to get state that is in A3, A4, etc...

That's great! I was forgetting the & in the formula. To continue to my next question...

The third tab has country totals, so let's just say in this sample size the US country only has five states. So a single cell on the third tab will say, "Georgia, Florida, Kentucky, Alabama, Virginia." Since the second tab lists all of these states on individual cells/rows, and this tab is combining them into one cell/row, is there any way to do a sumif to match add together ALL of the states that are listed in this one cell?

i guess i will add at this point the subgroups are getting smaller, so at this point i have about 120 regions being grouped into 20. i could do them manually if i had too, but a formula sure would be helpful.

thanks for the help!

Probably not with SUMIF, but it can be done with SUMPRODUCT.

e.g.

=SUMPRODUCT(--(ISNUMBER(MATCH("*"&$C$1:$C$5&"*",A1,0))),$D$1:$D$ 5)

Where A1 is the cell containing the 5 states, and C1:C5 is the list you want to look in to find the matches and sum the adjacent cells D1: D5.

Warning... with Sumproduct it is advisable to not use full column ranges as this function is more processor intensive.