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 5. Total comments: 236
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
you guys r awesome
Hello
I am a teacher trying to use google sheets to gather data. I'd like to break each assessment question into two parts: one for understanding the concept, and the other for computation. The only value that will go in each will be an x indicating that there was an error. I then want to accumulate separate totals based on these entries. Because this same rule doesn't apply to every question, not all questions will have a concept and computation part.
I am trying to use COUNTIF as follows:
COUNTIF(C7,"x")+(G7,"x")+(I7,"x") etc. I am getting a Formula parse error in the cell.
Any help you can give is appreciated!
Hello, Greg,
I'm sorry, but it's not exactly clear what result you want to get. Maybe this formula will do:
=COUNTIF(C7,"x")+COUNTIF(G7,"x")+COUNTIF(I7,"x")
If you still need our help, you can send us a small sample workbook with your data and the result you want to get to support@ablebits.com. Don't forget to mention this article and your comment.
I have pulled data from several states with account, I want to look at only one state & only count unique Business in that state, no duplicates
Thank you! It helped a lot!
Hi,
I have an excel sheet for training records which has columns named "name of course", "instructor", "course number", "attendee", "duration"etc. As I have multiple attendees on each training, training number is remaining same for different attendees. I would like to see the total duration for each instructor seperately on pivot table however, when I use "sum of duration" as a pivot table column, it is calculating all the numbers shown under duration column. (I mean, if there are 5 attendees for same training and if the duration is 8 hours, value that I want to see on pivot table is 8 hours, unfortunately it multiplies the number of attendees and duration and seems 40 hours) Is there a way to solve this problem?
Thanks in advance.
Hi Team,
Need help in adding total development hours of particular developer,irrespective of their name in Dev1, Dev2 or Dev3
Ticket ID Dev1 Dev2 Dev3 #Dev1hrs #Dev2hrs #Dev3hrs
1 Basanth Sriram Shoks 10 12 10
2 Shoks Basanth Sriram 5 10 17
my inputs are
Abc 2 500
def 1 2000
abc 4 300
Output should be:
Abc 6 800
def 1 2000
First - thank you so much for this site. Very helpful.
I have a spreadsheet with a large number of distinct account numbers. Each time an account number appears on the spreadsheet, whether duplicate or unique, it represents a different transaction with a value for each transaction. There are 3 columns, (A) for acct. #; (B) for transaction 1; and (C) for interest on transaction 1. I would like to get a list of distinct account numbers with the sum of the values for each distinct acct. number. (At the end, the list would contain a column for: (A) Distinct Acct.#; and, (B) sum or total transaction value for all values for each acct#; and (C) total or sum of interest for each acct#.
Hello Paul,
Thank you very much for your feedback, we're happy to hear you find our site helpful.
It sounds like the Subtotal option in Excel will do what you need:
- Select your records and go to Data tab
- Click on Subtotal and choose the following options:
- At each change in - column with the account number, use function - SUM, add subtotal to - select the columns with the values you want to sum.
- Click OK
I hope this helps.
Yes - absolutely worked. Wonderful. Thank you!!
Hi,
I forgot to select Add this data to the Data Model checkbox.what to do now
How would I count distinct dates in a selection of cells? I tried changing the dates to number format and then using =SUM(IF(ISNUMBER(range),1/COUNTIF(range, range),"")), but I got an incorrect answer with an error saying that the formula "omits adjacent cells".
I too, got an incorrect value when using this formula to count unique dates. No error message, but it gave me a result of less than 1.
Hi, I wish to all af you nice & new challanges, for 2017.
I have my own and asking for your help... My data are as below, in Excel 2013:
-column A: incomming invoice date (can be more identical, as there are more invoices on the same day). Like jan01, jan01, jan02, jan15, jan15, ...
The column is filtered, / Month or whatever.
I need to count the dates where there is only one input (row) in the filtered range (needed result is 1 (jan02) from above example, as that is the day when just one invoise was received.
There is no option to add more columns. Is it possible to get the right result by using the date column only?
Thanks and looking forward to your advice.
Hi I want to count values excluding the duplicates.
For example, I have two materials with different batch numbers for each material which contains 10 containers each belonging to multiple shipping lines. from this 20 containers of two batches, I want to count no. of containers under each shipping lines by using countifs and criterias are material name, batch number and shipping line and type of container (20' or 40'). Each containers weighs 24.75 tons but I have one container 12.375 in first batch and 12.375 in another batch. I don't want this container to be counted twice when I enter the batch number.
Hi I want the formula to count how many clients have completed by one person according to the date.
Client Name Auditor name Date
SFM Realty Corp. Shruthi 10/25/2016
SFM Realty Corp. Shruthi 10/25/2016
SFM Realty Corp. Shruthi 10/25/2016
American Kiosk Shruthi 10/26/2016
American Kiosk Shruthi 10/26/2016
American Kiosk Shruthi 10/26/2016
Here I need the Count for the Auitor name Shruthi Audited clients as 1 on 10/25/2016 & 1 on 10/26/2016.It should take the distinct names in the clients coloumn.
Please its Immediately required
I have Excel 2013 and use Pivot tables often. I tried using the Count Distinct Values feature but it is not listed in the Value Field Settings. Is it possible I need to install an update?
I did not find option Add data modeling during creating pivot table in Excel 2016
Nice Article.........
Thanks ☻.
Hi,
In Excel 2013 I have a table with more fields. The table is sorted on first field. In a new generated field of my table I have to count for each row the number of rows in the table depending on filter of the sorted field and also depending on filters of few other fields. I could solve this problem with COUNTIFS function, but there is another condition. One of the "fileds - filter" must be distinct, the others may be duplicates too.
?
Any help would be much appreciated!
Many thanks,
Ivan
I have Excel 2013, but the checkbox for "add this data to data model" is grayed-out and unchecked. Is there an Excel Add-in required to get this area functional?
Thanks,
Derek
Hi,
I don't see the item "Add this data to the data model" in the Create Pivot Table window. How come? I am using Excel 2010.
Hi Fab,
Regrettably, this option is not available in Excel 2010. It was introduced in Excel 2013, and also exists in Excel 2016. In earlier Excel versions, you can count unique values using formulas as demonstrated in the first parts of this tutorial.
Hello,
We download a report from Just Giving each time we have a donation and the format is not very easy to follow so we would just like to extract the information we need (eventually in a macro).
I would like to lookup all the distinct values in the user ID column, then calculate the total number of donations for each of the distinct ID values. I can then apply this formula to the other fee columns.
Any help would be much appreciated!
Many thanks,
Dani