Multiple conditions in counting

Hi guys-

I'd appreciate a second set of eyes on a problem. I have a huge data sheet on Farmer's Markets throughout the United States. I have to find out how many farmers markets there are per capita in each state. The catch is that I must exclude all Farmer's Markets in cities with more than 1 word in their name. I used a Len(Trim) equation to give me values for the number of words in the city name so it looks like this:

Screen shot 2015-07-07 at 2.54.06 PM.png

Assuming that your data is in columns A to C and down to row 300 (change ranges to suit your data)

Create a helper column D and enter this formula in D2 and fill down. This formula will enter a 2 if more than 1 word and 1 if a single word.

Formula:

Please Login or Register  to view this content.

Create another column for a unique listing of the states. I used column F and entered this formula and filled down:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

I then used this formula in column F starting in F2 and filled down. This sums all the values for each single word cities for each state:

Formula:

Please Login or Register  to view this content.