counting unique values in a given dataset Closed

Question

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.

ac3mark · Answer

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!

ebt · Answer

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.

ebt · Answer

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?

RayH · Answer

ZIP	Distinct Zip	Count11111	11111	222222	22222	333333	33333	277777	77777	288888	88888	266666	66666	144444	44444	122222	#N/A	033333	#N/A	011111	#N/A	022222	#N/A	088888	#N/A	077777	#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.

Counting unique values in a given dataset

4 responses

Similar discussions

Subscribe To Our Newsletter!