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!