I've attached a spreadsheet showing what I need. I need the formula to count how many individual markets are in each region without double counting them- So if column B states West for example count only the markets in Column A that are in the West region without counting markets twice.
I get different results than those you show in the sample file.
First, change the column headers in F7:I7 to be the actual region.
Central, North East, South Central, West
Then, enter this array formula** in F8 and copy across to I8:
=SUM(IF(FREQUENCY(IF($C8:$C67=F7,MATCH($B8:$B67,$B8:$B67,0)),ROW(B8:B67)-ROW(B8)+1),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
The results I get are:
Central = 6
North East = 9
South Central = 10
West = 6
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
djreddy 
Register To Reply
Bookmarks