Multiple conditions in counting

[jduffy0920]

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

[jduffy0920]

Essentially, I guess I'm asking how could I ask excel to sum the values of 1 for each state without having to manually select the cells for each 50 states. Thanks!

[newdoverman]

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.

=IFERROR(IF(FIND(" ",A2)>1,2),1)

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

=IFERROR(INDEX($C$2:$C$300,MATCH(1,INDEX((COUNTIF($F$1:F1,$C$2:$C$300)=0)*($C$2:$C$300<>""),0),0)),"")

...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:

=IF(F2="","",SUMIFS($B$2:$B$300,$C$2:$C$300,F2,$D$2:$D$2:$D$300,1))