Counting unique values in a given dataset
Closed
ebt
Posts
3
Registration date
Sunday May 4, 2014
Status
Member
Last seen
May 4, 2014
-
May 4, 2014 at 12:00 PM
RayH - May 6, 2014 at 10:54 PM
RayH - May 6, 2014 at 10:54 PM
Related:
- Counting unique values in a given dataset
- Unique code to confirm your identity on facebook - Facebook Forum
- Generate unique alphanumeric id in excel ✓ - Excel Forum
- Based on the values in cells b77 b81 ✓ - Excel Forum
- Count if cell contains a number - Excel Forum
- Count the number of times a value appears in a column ✓ - Office Software Forum
4 responses
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!
=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
Posts
3
Registration date
Sunday May 4, 2014
Status
Member
Last seen
May 4, 2014
May 4, 2014 at 04:23 PM
May 4, 2014 at 04:23 PM
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
Posts
3
Registration date
Sunday May 4, 2014
Status
Member
Last seen
May 4, 2014
May 4, 2014 at 04:28 PM
May 4, 2014 at 04:28 PM
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.
