In this tutorial, you will learn how to count unique values in Excel with formulas, and how to get an automatic count of distinct values in a pivot table. We will also discuss a number of formula examples for counting unique names, texts, numbers, cased-sensitive unique values, and more. Continue reading
Comments on: How to count unique values in Excel
by Svetlana Cheusheva, updated on
Comments page 4. Total comments: 236
Hi,
This was very useful, but I am having some issues:
1) I have column A as the month (1=Jan, 1=Feb, etc), and column B as the Client. When counting TOTAL distinct clients for the entire year (ie all months), how can I adjust the formula so it does not count cell B1 (the header/name of column), and updates dynamically as more data is added to the raw data sheet?
2) How can I adjust this formula so I can find # distinct clients broken down by month, updated dynamically as more data is added?
Thank you!
Hello Olivia!
To count the number of unique values in column B, use the instructions provided earlier in this article. Specify the counting range large, for example B2: B9999
To calculate the number of unique customers per month, use the additional condition - the number of the month.
If the month number = 2, then you can use something like this formula
=SUM(IF(ISTEXT(B2:B100)*(--IF(A2:A100=1,1,0))*COUNTIF(B2:B100,B2:B100)=1,1,0))
I hope it’ll be helpful.
Hello,
I'm creating a data table with that shows the count of people in each expertise level. The count is created by a 'COUNTIFS' formula. Formula looks as follow:
=(IF(COUNTIFS(SkillsetData!B:B,IndustryExpert_ORTEMS_CSVREADY!B2,SkillsetData!CP:CP,">0",SkillsetData!AD:AD,IndustryExpert_ORTEMS_CSVREADY!$L$2)=0,"",(COUNTIFS(SkillsetData!B:B,IndustryExpert_ORTEMS_CSVREADY!B2,SkillsetData!CP:CP,">0",SkillsetData!AD:AD,IndustryExpert_ORTEMS_CSVREADY!$L$2))))
The next challenge is to base the count on unique values. I have one column called "name' (A) with duplicates in it. Now I am looking for a solution to integrate a formula in the existing formula that only counts the unique 'name' from column A before starting to look at the other criteria in the COUNTIFS formula.
I really appreciate your help.
Have a nice day!
Hello Erik!
Unfortunately, without seeing your data it is impossible to give you advice.
I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
We'll look into your task and try to help.
Hello Alexander,
Thank you for your reply. I did some more research today and I have found a way to create the result that I was looking for. I needed to define per row if there are any duplicated based on two columns (without removing the data). I found a nice trick with a reverse COUNTIFS formula. Starting from the bottom and dragging it up.
Anyway thanks for your reply and offer to help!
Have a nice day.
Good day,
could you support me with a solution for a such output : the result should be at the end of the list (the last cell), I have huge list of columns and I need to know the distribution of each one, without passing through Pivot table:
Item
0
0
11
11
11
11
4
4
4
6
6
6
6
0(2),11(4),4(3),6(4)
Thank you!
Hello!
I recommend this article: How to get a list of unique and distinct values in Excel
Hi,
I have a question about my excel datasheet. In column A i have audit partner names and in column B i have different issuer cik numbers. I want to count how many different companies one audit partner audits. So, how many audit clients one audit partner has. Is there an excel formula for this?
Kind regards,
Rohan Kapoor
Hello Rohan!
I could help you if I knew what "issuer cik numbers" are. If you give an example of your data, I will try to help you. Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.
I have a spredsheet having column A with repeated names. The an other column B contains the month. One more column C contains some text like fresh/renewal/enhancement. I need to count unique text or names entered in Column A, with criteria if month in column B is "MAY" and Column C contains text "Fresh". the formula should works If there is blank cell also and if we added the data frequently at below.
Hello!
If I understand your task correctly, to calculate the number of unique values by conditions, use the formula:
=SUMPRODUCT(--(B2:B17="MAY"), --(C2:C17="Fresh"), --($A$2:$A$17=A2))
I hope this will help
I want to count the number of unique values based on the string in the cell and the format as my data is distingquished by format. So if I have 50 SAC 25 blue and 43 green. It would be counted as 2 in the occurances.
Hello Jeffrey!
I recommend to study the article How to count cells by color in Excel
Describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.
Hi,
I have 2 Columns with different range
column G - column H
Rock Grades - Surveyed Qty
1-500 kg - 3,411
1-1000 kg - 8,673
300-1000 kg - 11,757
60-300 Kg - 3,001
750 mm - 7,916
60-300 Kg 4,186
I want a formula how to calculate these Rock Grade values with particular categories wise
Hello!
I’m sorry but your task is not entirely clear to me.
What exactly do you want to count? For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.
Hi, I’m looking for a formula to count unique reference numbers which contains both numbers and letters, example 12345 MCP107 in the cell range E2:E57 only if meets the criteria of “N” in the range G2:G57. I am using excel 2010 - can you help me out with this please?
I am using this formula to count how many times a particular word appear within a range of cells, and it works...however I need to include additional characters after the particular word (example: "candidate 10/10")
=SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range, "candidate","")))/LEN("candidate"))
Hello, I apologize in advance for asking for help but hopefully, this is an easier one for you, less so for me.
I have a large data set with one unique identifier, that being the VIN number of a vehicle. When we process a service transaction for a vehicle, sometimes there will be more than one tech working on the same vehicle and that will require more than one entry into the spreadsheet. To accommodate this requirement I have set up a macro that copies all of the data on the selected row and places a duplicate on the next available row. The problem I have is that in doing so, it creates a duplicate of everything including several items that I don't want to be duplicated such as total job hours and total hours quoted as these will distort the records if my staff fail to remove these manually each time.
What I am trying to achieve is to have a formula that can be used in the total hour's sum cell (B50) that detects the duplicated record created by the macro having detected the VIN as identical to the same VIN already used previously in that column, A1:A50.
The problem I am trying to solve occurs because the entire job hours and job $ values are recorded as a whole when the record is first created. The macro then creates an entirely new duplicate record though artificially doubling the sum value by adding new row with a clone of the same data. I want to keep the clone data as-is but I just don't want to count the additional hours that now appear in the hour's column C1:C50.
I intend to use the same formula for several items including hours and all dollar values for that job as the problem affects several cells. I just need one working formula and I can modify it from there for each column.
I sincerely hope that makes sense.I am happy to send you the sheet if that makes this easiler to understand.
How do you get this formaula to also ignore errors as well as Error values such as "#NUM"
You can use sumif function.
Before that, pls insert new coloumn and enter "1" in to all lines.
And select and copy all names in to new coloumn.and remove the duplicates.and use the sumif function and get the sum of services each patient.
I have two columns. Column A contains 5 different treatments for vehicles, let's say treatments S, T, U, V and W. These five treatments are applied to vehicles in column B. The same vehicle might receive more than one treatment per month, sometimes the same treatment twice. I want to know how many distinct vehicles in column B received treatments S, T and V. Thus distinct values based on multiple OR criteria from the same column A. Typical 2500 to 5000 rows per month.
Please help me with an Excel formula that will solve this problem?
Ps. Thank you, your site has been very helpful to me!
AN EXCEL SHEET CONTAINS DATA
EG:-IN CERTAIN CELLS CONTAIN ONLY A NAME (Eg:- "RONY")
IN CERTAIN CELLS CONTAIN TWO or more NAMES (Eg:- "RONY,SONA")
HOW CAN I COUNT ONLY ONE PARTICULAR NAME IN THAT SHEET using formula?
Dear all,
Request you to kindly help me understand this formula:
=SUM(IFERROR(1/IF($A$2:$A$10"", FREQUENCY(IF(EXACT($A$2:$A$10, TRANSPOSE($A$2:$A$10)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), ""), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0), 0)). I posted a comment earlier as well and it was removed.
Thank you!
Dear all,
Could you please elaborate the working of this formula: "=SUM(IFERROR(1/IF($A$2:$A$10"", FREQUENCY(IF(EXACT($A$2:$A$10, TRANSPOSE($A$2:$A$10)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), ""), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0), 0))".
Thank you in advance!
Dear All,
Please i need help, I want to count unique values with criteria and reported to a general summery to another "sheet".
please advice
Month Agent Sales Agent Status
JAN A 0.02 A 1
JAN B 0.00 B 1
JAN C 0.02 C 1
JAN D 0.03 D 1
JAN E 0.06 E 1
JAN F 0.05 F 1
FEB A 0.00
FEB B 0.00
FEB C 0.04
FEB D 0.02
FEB E 0.01
FEB F 0.00
MAR A 0.04
MAR B 0.03
MAR C 0.02
MAR D 0.06
MAR E 0.05
MAR F 0.06
Hi...
Request you to please go thru' the above concern.
We have 6 agents (A,B,C,D,E,F) did sales more than 2k (it's a must) a period of 3 months.
At the end of quarter we wish to know all agents did one time sales i.e.>=2k (either one time or more) or not.
If we have the data like above, please suggest a formula.
A B
00:00 E/F
01:00 E/F
02:00 E/F
03:00 10
04:00 E/F
05:00 E/F
06:00 5
07:00 10
08:00 L/S
09:00 L/S
10:00 L/S
11:00 L/S
12:00 L/S
13:00 L/S
14:00 12
15:00 P/O
16:00 P/O
17:00 P/O
18:00 P/O
19:00 O/C
20:00 O/C
21:00 O/C
22:00 E/F
23:00 E/F
There are 3 sets of E/F here.How will i get excel to output 3 counts? That is 1 count for each set of E/F.Thanks
Hello, Im Trying to create a formula for this one, I need to count distinct values per week, only the “Open” status. The hard part is, you dont need to count the”Open” status if at a certain time that week it was already closed.
SR Reference# Week # Status
19999 32 Closed
189898 32 Open
189898 32 Closed
189898 29 Closed
189898 31 Open
19999 33 Open
19999 33 Open
Thanks in advance!
None of this works. Rubbish article.
Hello Samuel,
You can download our sample workbook and make sure all the formulas work exactly as described.
=IF(ISNUMBER(MATCH("Apples",M:M,0)),COUNTIF(N:N,"Green"),0)
This formula is not perfect. I want to see if there are "Apples" in one column and "Green" in corresponding column, it should count all Green Apples.
Thanks.
Hi,
How can I count unique value within a range IF these values match those of another column? For example, in column A I have Apples, Oranges, Bananas.
In column B I have several types of cars.
How can I count the different types of car associated with each fruit (eg. banana)?
Hello,
I want to find exact text located in two cells in one column and cout it. How to do this?
For example:
Need to find how many times 'cat black' is repeated in one column:
Cat
Blue
Cat
Black
Cat
White
Cat
Black
And so on
In the example the count of 'cat black' must be 2.
Thanks for help.
I need to count the number of distinct text values in a column (ignoring blanks) but the following formula is returning an incorrect value of "1".
=SUM(IF(ISTEXT(range),1/COUNTIF(range, range),""))
How to count total quantity per unique parts and identify fast moving items. Pls help
Example
Screw 7 amount 80usd
Hose 3 60usd
Screw 10 amount 80usd
Keyboard 5 5usd
Hose 5 60 usd
Thanks a lot
Hello There,
I have data of my sales and which is having SKU ID which is not in same form and i want to count with COUNTIF formula however i am not able to do that, kindly advise.
I am attempting to count a column of 7-9 digit (patient medical record) numbers, excluding any duplicates and only if a specific value (ie: MICU) is listed in another field of the same row. (Basically I need to count how many different patients where in a specific unit.) Can you help? I have only gotten as far as counting unique patient numbers.
How to use subtotal to make counting filter dependent?
I want to know that How can I get no. of unique company with user wise with formula in excel.
Example:-
Commapny Name User
WeTalkive Jalpesh
WeTalkive Jalpesh
Codeveloped BV Brijesh
Codeveloped BV Brijesh
The Red Corner B.V Jalpesh
The Red Corner B.V Jalpesh
Jumbo Golf Brijesh
Jumbo Golf Brijesh
Jumbo Golf Brijesh
Jumbo Golf Brijesh
JEKA Industriële Efficiency Jalpesh
JEKA Industriële Efficiency Jalpesh
I need to count unique values based on:
- unique values storage (SAP sheet column B)
- on a specific date (SAP sheet column C)
- on a production line (SAP sheet column G).
I need to storage the data in sheet DataBase:
- Column W for produciton line 3 based on date on column I
- Column 0 for producion line 4 based on date on column I
I sent you the file to support@ablebits.com (file called T1XX- from Javier Castorena)
Hi,
Please help me to find the solution:
I have a survey with hundreds respondents which the answers are their hometown. For example:
London
Milan
Basel
Tokyo
Paris
Tokyo
Paris
Basel
Tokyo
Madrid
Amsterdam
Basel
Amsterdam
Amsterdam
Amsterdam
I want to count how many people come from each city. The result I want is like this
Amsterdam 4
Basel 3
Madrid 1
Tokyo 3
Milan 1
London 1
Paris 2
Is there any solutions to do it automatically?
Thanks in advance.
Hi,
Thank you for all of the information on here.
I am experiencing a range limit on the use of this calculation that is much lower than 125. For some reason it is only letting me check 20 rows, anything above that is returning a result of 0. Is this normal? Is there anyway around it.
What I really want to do is check an entire column in a table for values (there is about 1000), but as soon as i do this the returned value is zero. If it is less than 20, the returned value is acurate.
I am using;
=SUM(IF(ISTEXT(A1:A20)*COUNTIF(A1:A20,A1:A20)=1,1,0)) - works
=SUM(IF(ISTEXT(A1:A30)*COUNTIF(A1:A30,A1:A30)=1,1,0)) - total is zero
Awesome, thanks for your help..
I created a pivot chart using distinct values and now want to group the dates portion of the data to get distinct counts by month, however, the group function is grayed out. How can I group this information if the group function is not available without manual working / adding columns? Thank you.
For "distinct" data, ie, I needed to know how many individual customers I had in a list of transaction I did this:
Sorted data by customer name, then added
a column of the formula: =IF(EXACT(A2,A3)=TRUE,0,1) in each row,
then summed the column.
Easy, accurate, no special arrays etc. had to hide the column when sharing.
Hello,
please help me find a solution:
I need to count the number of distinct text and number values in column $M:$M , when in column $E:$E is written "*italija*", and when in column $F:$F is written "saus", also - it should not count blank cells.
Now I am doing this in pivot: filtering column F with "saus", than filtering column E with "italija", than copying column M in another sheet, removing dublicates and counting the cells.
Hello, Karolis,
Would it be possible for you to send us a small sample workbook with your source data and the result you want to get? Please shorten your table to 10-20 rows / columns and email it to support@ablebits.com. Please also don't forget to include the link to your comment in the email.
We'll look into your task and try to find a solution.
Hello,
I'm using Excel 2016, and used Distinct Count within a pivot table.
My result looks like this:
Row Label Distinct Count
Item 1 1
Item 2 39
Item 3 3
Grand Total 40
See, the grand total does not match the sum of each item. I've checked each item's details and I figure the grand total should be 43 (1+39+3) instead of 40 that the pivot calculates.
Any ideas on why this might be different?
Thank you.
Hi Svetlana Cheusheva,
I want to count no of billing documents with Order number wise and number of billing dates for order number.
Order number Billing Date No of billing Dates Billing Document No of invoices
2407571 18-Jan-18 1 8014769109 1
2407573 8-Jan-18 1 8014769017 1
2407574 8-Jan-18 1 8014769017 1
2407575 5-Jan-18 3 8014769004 3
10-Jan-18 8014769041
16-Jan-18 8014769086
2407576 16-Jan-18 1 8014769086 1
2407577 9-Jan-18 8014769018
2407578 5-Jan-18 8014769002
9-Jan-18 8014769025
16-Jan-18 8014769083
Hi Pandu,
I am sorry, your data look distorted in the comment above. If you can send us a small sample workbook with your data and the result you want to get, we'll be able to help you better.
Please shorten your table to 10-20 rows / columns and email it to support@ablebits.com. Please also don't forget to include the link to your comment in the email.
Thank you.
Hello,
Similar to Fiona's question posted 09 April 2016, I have a list of patients in column b with duplicates, their procedure in column c, and the date of the procedure in column d. I need to report how many patients received each kind of procedure quarterly. Is it possible to have a formula that will report this information in the same worksheet? I have been able to figure out how to count how many "unique" patients for the whole year, but not in a date range based on each procedure.
Thanks in advance for any help.
Hi,
I'm working with Excel 2010. I'm working on a document that keeps track of all our projects. I need to know how many organizations we worked with in a month period. We keep track of every interaction so the same organization appears multiple times in the list. How can I have the number of distinct organizations by a date range?
I use this formula to get all the organizations in the column, but I can't figure out how to condition it by date range
=SUMPRODUCT((' Activities'!K2:K100"")/COUNTIF(' Activities'!K2:K100,' Activities'!K2:K100&""))
Any help is very much appreciated.
Thank you
Dear, I am working on the attached Sheet. I need to calculate the Values in Column "G" i.e. to Count unique text values based on multiple (Two) criteria, but criteria are in NUMBERS not in TEXT. Further, Its is big sheet, therefore I want to use the cell reference in range and in criteria. I am very confused. I want to count the column C i.e. Degree based on the Criteria E and F. That is, Look E2 in column A and Look F2 in column B and count the unique text values in Column C. Hope it is clear.
Plz help. Thank you
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
how to count each sales person bill number and unique
Hi Guys,
How would you ensure that duplicates are not included for the first criteria i.e ('GRP310 for P&L Variance'!$C$5:$C$1000='P&L Variance MTD'!B$5) ???
=+SUMPRODUCT('GRP310 for P&L Variance'!$E$5:$BZ$1000,('GRP310 for P&L Variance'!$C$5:$C$1000='P&L Variance MTD'!B$5)*('GRP310 for P&L Variance'!$D$5:$D$1000='P&L Variance MTD'!$A7)*('GRP310 for P&L Variance'!$E$3:$BZ$3='P&L Variance MTD'!B$6)*('GRP310 for P&L Variance'!$E$4:$BZ$4='P&L Variance MTD'!$A$5))
Thanks in advance.
Hello Victor,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
Hi,
I have sought to use the following formula:
=SUM(IF(COUNTIF(A2:A10,A2:A10)=1,1,0))
No matter how implemented it returns 0. I have even created some simple new text data in a new column (Jim, Bob, Jim, Sally, Joe, Bob, Sally, Joe, Jim) in a new test spreadsheet, and the same happens.
I can only conjecture that the reason for this is that COUNTIF(RNG1,RNG1) returns an array, not a single number, and so produces 0?
The concept seems so simple, but I have spent that last few hours trying to resolve this maddening issue.
Help! Please!
B
Hello,
This is an array formula, so be sure to press Ctrl + Shift + Enter to complete it properly. Once you do this, Excel will automatically enclose the formula in {curly braces}. Typing the curly braces manually won't work.
Hope it will help you!
Hello! I need help
I have a list and I want to count the product in this way.
product A = 1
product A = 1
product A = 1
product B = 2
product B = 2
product C = 3
product D = 4
product D = 4
product D = 4
product D = 4
Please help me.
Hello, Rahul,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
Hi
Excellent page and tutorial!
I have a pivot table created with patients coming in every month. I want to count the new patients that have come in each month, so basically compare the previous months data and put as null if the patient visited the previous months.
Thanks in advance
Peter, this is a Array Formula. To activate you must select CTRL+SHIFT+ENTER to activate. This will automatically add the { and } characters to the formula. They can not be added manually.
Hope this helps!
Many thanks to the folks at AbleBits for this tutorial and examples. I could not get my counts to work correctly.
On a side note, I've been burned by bad data in the past so I also recommend trimming your data to remove any trailing spaces and removing any punctuations like the ' in O'Brien. If your data contains mixed case I would also recommend reformatting everything to the same case format. O'Brien and Obrien both become OBRIEN. You'll be happy you did.
Great page!
However, when I copy/paste the formula and enter the correct ranges, I get an error message, not recognising this as a valid formula.
I have had this issue with other formulas too, so it might be a cell formatting issue?
Great many thanks in advance!
Hello there,
May I ask, is it possible to exclude blanks when using Distinct Count in a pivot table?
Thanks in advance for any guidance! :)
Chuck