Sum by condtion

[sqrlking]

I'm don't think this is the right terminology, so I'm not sure what to search for. Please excuse me if this topic has already been discussed.

Here's what I want to do is sum numbers from all states listed in a cell. I have a list of states on sheet one, column b, and numbers in column c. on sheet two, i have regions, for example "Region 1 - Maine, Vermont, New Hampshire, Massachusetts, Connecticut, New York, Rhode Island". Is there a way to get totals based on that? or would i need to separate out the states and do a lookup for each before summing?

I think that was confusing. let me know if i can clarify.

Thanks!

[wmorrison49]

So do you want the sum by state, the sum by region, or both?

[sqrlking]

Sum by region

[VBA Noob]

Take a look at sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

VBA Noob

[wmorrison49]

Attach your spreadsheet and I will take a look at writing some code if the sumproduct help that VBA Noob posted didn't work.

[sqrlking]

I appreciate the offer to write code, but if it's not a formula in a cell, i can go ahead and break out the states into separate cells, and sum using those. it'll only take a couple minutes.

originally, i was going to use autohotkey to match the states in the string, but that seemed incredibly involved for what seems like it should be simple.

looks like there's no formula to match parts of a string in a cell.