Counting unique values in a given dataset [Closed]

Report
Posts
3
Registration date
Sunday May 4, 2014
Status
Member
Last seen
May 4, 2014
-
 RayH -
I am using Excel 2007. I have to track the number of visitors to various farmers markets by zip codes on a given market day. I have all of the zip codes in a column on one spreadsheet and I have counted the total number of zip codes to that market for that particular day and the number of different zip codes that visited it as well. My problem is that i have to give a count for how many times a particular zip code appeared in the data on "sheet 1" in a column on " sheet 2. Any solutions would be extremely helpful. Thanks.

4 replies


Ok, hang in there. Try to use the COUNTIF(range,what_value) function.

=COUNTIF(B2:B35,A1)
The above will look at what "zipcode" is in cell A1, and count the number of times it occurs in between the cells of B2 through B35.

I hope this helps.

Have fun!

Posts
3
Registration date
Sunday May 4, 2014
Status
Member
Last seen
May 4, 2014

Thanks! This function works great for one cell. Is there a way I can make it work for multiple cells across multiple columns? For example a column of 61 rows( 61 different ZIP Codes) and 28 columns (for 28 weeks worth of data)? When I try to cut and paste this it into the different cells itchanges the parameters of the function.
Posts
3
Registration date
Sunday May 4, 2014
Status
Member
Last seen
May 4, 2014

Thanks! This works for one cell but I cant get it to work for the entire sheet, which is 61 rows ( 61 Zip Codes) by 29 columns ( 29 weeks of data). Any suggestions?

ZIP Distinct Zip Count
11111 11111 2
22222 22222 3
33333 33333 2
77777 77777 2
88888 88888 2
66666 66666 1
44444 44444 1
22222 #N/A 0
33333 #N/A 0
11111 #N/A 0
22222 #N/A 0
88888 #N/A 0
77777 #N/A 0

Column A: ZIP Codes:

Formula in column B (distinct zip):
=INDEX($A$2:$A$14,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$14),0,0),0))

Formula in column C (count of distinct zip):
=COUNTIF(A2:A14,INDEX($A$2:$A$14,MATCH(0,INDEX(COUNTIF($A$1:A1,$A$2:$A$14),0,0),0)))

Column B is not really required and is just provided for clarity.

Hopes this helps. If not, it could be useful on other problems.

Would be nice see a sample layout of your Sheet1 as visual clues are often better than vague descriptions.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!