Count how often a value occurs but wont know what the value will be

**Rosszx10r** · 06-26-2020, 09:15 AM

So i have a excel sheet getting its data from a Microsoft form.
My problem is this someone enters a unique value and some times they will use same one, charging code for example
i need to count how many times this code is used, now the code will be a 1-6 digit number so i couldn't enter that many Counif formulas lol
is there an easy way to do this?

**CK76** · 06-26-2020, 09:33 AM

Without knowing your workbook/data structure it's bit hard to help. I'd recommend uploading desensitized sample, see yellow banner at top for how.

If you don't need to set it up as dynamic calculation. You can just copy the column into another sheet. Use Remove duplicate tool.
Then use Countif using cell references.

**Rosszx10r** · 06-26-2020, 09:56 AM

So keeping it simple
column A is all I'm interested in.

But i dont know what the user will put here its unique to each user the could be 2000+ but if the have 2 inputs id like to count them

Eg

USER
1000
2000
1000
2000
3000

USER 1000 = 2
USER 2000 = 2
USER 3000 = 3

but again i wont know this user number until they put it in does that help

**CK76** · 06-26-2020, 10:13 AM

So like I mentioned.

You'd add another sheet (Sheet2). Then copy column A from original (Sheet1) over. Then use Data -> Data tools -> Remove Duplicates.
Then in column beside the result, assuming Row 1 is header, enter following formula and copy down.
=COUNTIF(Sheet1!A:A,Sheet2!A2)

If you need it to be dynamic... you could just use Pivot Table rather than formula.

**Rosszx10r** · 06-26-2020, 10:15 AM

i need this data to update each week and would rather it automatic otherwise id end up with lots of extra sheets
Pivot table could would, but id like to do sums with the data im left with

**CK76** · 06-26-2020, 10:21 AM

Really depends on your set up. Like I wrote, upload sample workbook to get more direct help, follow direction in the yellow banner.

If you have access to Get & Transform (Power Query) and/or PowerPivot I'd use that.

Count how often a value occurs but wont know what the value will be

LinkBack

Thread Tools

Rate This Thread

Display

Count how often a value occurs but wont know what the value will be

Re: Count how often a value occurs but wont know what the value will be

Re: Count how often a value occurs but wont know what the value will be

Re: Count how often a value occurs but wont know what the value will be

Re: Count how often a value occurs but wont know what the value will be

Re: Count how often a value occurs but wont know what the value will be

Thread Information

Users Browsing this Thread

Similar Threads

Count numbers when certain name occurs in a category

Count how often a value/name occurs

Count Until Next Value Occurs

[SOLVED] Excel 2007 : how to count if multiple criteria occurs

[SOLVED] Excel 2007 : Count how many a value occurs with given set of criteria

Count how often a value occurs – multiple criteria

I would like to count the # of times a value occurs in Col B base

Bookmarks

Bookmarks

Posting Permissions