How to count unique values in Excel

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.

When working with a large dataset in Excel, you may often need to know how many duplicate and unique values are there. And sometimes, you may want to count only the distinct (different) values.

If you have been visiting this blog on a regular basic, you already know the Excel formula to count duplicates. And today, we are going to explore different ways to count unique values in Excel. But for the sake of clarity, let's define the terms first.

  • Unique values - these are the values that appear in the list only once.
  • Distinct values - these are all different values in the list, i.e. unique values plus 1st occurrences of duplicate values.

The following screenshot demonstrates the difference:
Unique and distinct values

And now, let's see how you can count unique and distinct values in Excel using formulas and PivotTable features.

How to count unique values in Excel

Here's a common task that all Excel users have to perform once in a while. You have a list of data and you need to find out the number of unique values in that list. How do you do that? Easier than you may think :) Below you will find a few formulas to count unique values of different types.

Count unique values in a column

Supposing you have a column of names in your Excel worksheet, and you need to count unique names in that column. The solution is to use the SUM function in combination with IF and COUNTIF:

=SUM(IF(COUNTIF(range, range)=1,1,0))

Note. This is an array formula, so be sure to press Ctrl + Shift + Enter to complete it. Once you do this, Excel will automatically enclose the formula in {curly braces} like in the screenshot below. In no case should you type the curly braces manually, that won't work.

In this example, we are counting unique names in range A2:A10, so our formula takes the following shape:

=SUM(IF(COUNTIF(A2:A10,A2:A10)=1,1,0))
Counting unique values in Excel

Further on in this tutorial, we are going to discuss a handful of other formulas to count unique values of different types. And because all those formulas are variations of the basic Excel unique values formula, it makes sense to break down the above formula, so you can fully understand how it works and tweak it for your data. If someone is not interested in technicalities, you can skip right to the next formula example.

How the Excel count unique values formula works

As you see, 3 different functions are used in our unique values formula - SUM, IF and COUNTIF. Looking from the inside out, here's what each function does:

  • The COUNTIF function counts how many times each individual value appears in the specified range.

    In this example, COUNTIF(A2:A10,A2:A10) returns the array {1;2;2;1;2;2;2;1;2}.

  • The IF function evaluates each value in the array returned by COUNTIF, keeps all 1's (unique values), and replaces all other values with zeros.

    So, the function IF(COUNTIF(A2:A10,A2:A10)=1,1,0) becomes IF(1;2;2;1;2;2;2;1;2) = 1,1,0, which turns into the array {1;0;0;1;0;0;0;1;0} where 1 is a unique value and 0 is a duplicate value.

  • Finally, the SUM function adds up the values in the array returned by IF and outputs the total number of unique values, which is exactly what we wanted.

Tip. To see what a specific part of your Excel unique values formula evaluates to, select that part in the formula bar and press the F9 key.

Count unique text values in Excel

If your Excel list contains both numerical and text values, and you want to count only unique text values, add the ISTEXT function to the array formula discussed above:

=SUM(IF(ISTEXT(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))

As you know, the Excel ISTEXT function returns TRUE if an evaluated value is text, FALSE otherwise. Since the asterisk (*) works as the AND operator in array formulas, the IF function returns 1 only if a value is both text and unique, 0 otherwise. And after the SUM function adds up all 1's, you will get a count of unique text values in the specified range.

Don't forget to press Ctrl + Shift + Enter to correctly enter the array formula, and you will get a result similar to this:
Counting unique text values in Excel

As you can see in the screenshot above, the formula returns the total number of unique text values, excluding blank cells, numbers, logical values of TRUE and FALSE, and errors.

Count unique numeric values in Excel

To count unique numbers in a list of data, utilize an array formula like we've just used for counting unique text values, with the only difference that you embed ISNUMBER instead of ISTEXT in your unique values formula:

=SUM(IF(ISNUMBER(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))
Counting unique numeric values in Excel

Note. Since Microsoft Excel stores dates and times as serial numbers, they are also counted.

Count case-sensitive unique values in Excel

If your table contains case-sensitive data, the easiest way to count unique values would be creating a helper column with the following array formula to identify duplicate and unique items:

=IF(SUM((--EXACT($A$2:$A$10,A2)))=1,"Unique","Dupe")

And then, use a simple COUNTIF function to count unique values:

=COUNTIF(B2:B10, "unique")
Counting case-sensitive unique values in Excel

Count distinct values in Excel (unique and 1st duplicate occurrences)

To get a count of distinct values in a list, use the following formula:

=SUM(1/COUNTIF(range, range))

Remember, it's an array formula, and therefore you should press the Ctrl + Shift + Enter shortcut instead of the usual Enter keystroke.

Alternatively, you can use the SUMPRODUCT function and complete the formula in the usual way by pressing the Enter key:

=SUMPRODUCT(1/COUNTIF(range, range))

For example, to count the distinct values in range A2:A10, you can go with either:

=SUM(1/COUNTIF(A2:A10,A2:A10))

Or

=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))
Counting distinct values in Excel

How the Excel distinct formula works

As you already know, we use the COUNTIF function to find out how many times each individual value appears in the specified range. In the above example, the result of the COUNTIF function is the following array: {2;2;3;1;2;2;3;1;3}.

After that, a number of division operations are performed, where each value of the array is used as a divisor with 1 as the dividend. This turns all duplicates values into fractional numbers corresponding to the number of duplicate occurrences. For example, if a value appears 2 times in the list, it generates 2 items in the array with a value of 0.5 (1/2=0.5). And if a value appears 3 times, it produces 3 items in the array with a value of 0.3(3). In our example, the result of 1/COUNTIF(A2:A10,A2:A10)) is the array {0.5;0.5;0.3(3);1;0.5;0.5;0.3(3);1;0.3(3)}.

Doesn't make much sense so far? That's because we haven't applied the SUM / SUMPRODUCT function yet. When one of these functions adds up the values in the array, the sum of all fractional numbers for each individual item always yields 1, no matter how many occurrences of that item exist in the list. And because all unique values appear in the array as 1's (1/1=1), the final result returned by the formula is the total number of all different values in the list.

Formulas to count distinct values of different types

As is the case with counting unique values in Excel, you can use variations of the basic Excel count distinct formula to handle specific value types such as numbers, text, and case-sensitive values.

Please remember that all of the below formulas are array formulas and require pressing Ctrl + Shift + Enter.

Count distinct values ignoring empty cells

If a column where you want to count distinct values might contain blank cells, you should add an IF function that will check the specified range for blanks (the basic Excel distinct formula discussed above would return the #DIV/0 error in this case):

=SUM(IF(range<>"",1/COUNTIF(range, range), 0))

For example, to count distinct values in range A2:A10, use the following array formula:

=SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10, A2:A10), 0))
The formula to count distinct values ignoring empty cells

Formula to count distinct text values

To count distinct text values in a column, we'll be using the same approach that we've just used to exclude empty cells.

As you can easily guess, we will simply embed the ISTEXT function into our Excel count distinct formula:

=SUM(IF(ISTEXT(range),1/COUNTIF(range, range),""))

And here's a real-life formula example:

=SUM(IF(ISTEXT(A2:A10),1/COUNTIF(A2:A10, A2:A10),""))

Formula to count distinct numbers

To count distinct numeric values (numbers, dates and times), use the ISNUMBER function:

=SUM(IF(ISNUMBER(range),1/COUNTIF(range, range),""))

For example, to count all different numbers in range A2:A10, use the following formula:

=SUM(IF(ISNUMBER(A2:A10),1/COUNTIF(A2:A10, A2:A10),""))

Count case-sensitive distinct values in Excel

Similarly to counting case-sensitive unique values, the easiest way to count case-sensitive distinct values is to add a helper column with the array formula that identifies unique values including first duplicate occurrences. The formula is basically the same as the one we used to count case-sensitive unique values, with one small change in a cell reference that makes a great difference:

=IF(SUM((--EXACT($A$2:$A2,$A2)))=1,"Distinct","")

As you remember, all array formulas in Excel require pressing Ctrl + Shift + Enter.

After the above formula is finished, you can count "distinct" values with a usual COUNTIF formula like this:

=COUNTIF(B2:B10, "distinct")
Counting case-sensitive distinct values in Excel

If there is no way you can add a helper column to your worksheet, you can use the following complex array formula to count case-sensitive distinct values without creating an additional column:

=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))

Count unique and distinct rows in Excel

Counting unique / distinct rows in Excel is akin to counting unique and distinct values, with the only difference that you use the COUNTIFS function instead of COUNTIF, which lets you specify several columns to check for unique values.

For example, to count unique or distinct names based on the values in columns A (First Name) and B (Last Name), use one of the following formulas:

Formula to count unique rows:

=SUM(IF(COUNTIFS(A2:A10,A2:A10, B2:B10,B2:B10)=1,1,0))

Formula to count distinct rows:

=SUM(1/COUNTIFS(A2:A10,A2:A10,B2:B10,B2:B10))
Counting unique and distinct rows in Excel

Naturally, you are not limited to counting unique rows based only on two columns, the Excel COUNTIFS function can process up to 127 range/criteria pairs.

Count distinct values in Excel using a PivotTable

The latest versions of Excel 2013 and Excel 2016 have a special feature that allows counting distinct values automatically in a pivot table. The following screenshot gives an idea of how the Excel Distinct Count looks like:
Distinct count in a pivot table

To create a pivot table with the distinct count for a certain column, perform the following steps.

  1. Select the data to be included in a pivot table, switch to the Insert tab, Tables group, and click the PivotTable button.
  2. In the Create PivotTable dialog, choose whether to place your pivot table in a new or existing worksheet, and be sure to select the Add this data to the Data Model checkbox.
    Select the 'Add this data to the Data Model' checkbox.
  3. When your pivot table opens, arrange the Rows, Columns and Values areas the way you want. If you don't have much experience with Excel pivot tables, the following detailed guidelines may prove helpful: Creating a PivotTable in Excel.
  4. Move the field whose distinct count you want to calculate (Item field in this example) to the Values area, click on it, and select Field Value Settings… from the drop-down menu:
    Move the field whose distinct count you want to calculate to the Values area, and select Field Value Settings…
  5. The Value Field Settings dialog window will open, you scroll down to Distinct Count, which is the very last option in the list, select it and click OK.

You can also give a custom name to your Distinct Count if you want to.
Scroll down to the very last option and select Distinct Count.

Done! The newly created pivot table will display the distinct count like shown in the very first screenshot in this section.

Tip. After updating your source data, remember to update the PivotTable to bring the distinct count up to date. To refresh a pivot table, just click the Refresh button on the Analyze tab, in the Data group.

This is how you count distinct and unique values in Excel. If someone wants to have a closer look at the formulas discussed in this tutorial, you are welcome to download the sample Excel Count Unique workbook.

I thank you for reading and hope to see you again next week. In the next article, we are going to discuss various ways to find, filter, extract and highlights unique values in Excel. Please stay tuned!

Table of contents

236 comments

  1. I'm sorry that the formula was in my own language - here it is in the English version:

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET($D$9;ROW($D$9:$D$2000)-ROW($D$9);;1));IF($D$9:$D$2000"";MATCH($D$9:$D$2000;$D$9:$D$2000&"";0)));ROW($D$9:$D$2000)-ROW($D$9)+1);1))

  2. I have an autofiltered column with different week numbers from 1 to 52 and from this I want to calculate the distinct number of filtered week numbers. Let's say I have excluded all week numbers higher than 5 and have the followning rows shown:

    1
    1
    2
    3
    3
    3
    5

    I have tried to calculate the number of distinct weeks (4) with use of this formula which was entered with Ctrl-Shift-Enter:

    =SUM(HVIS(FREKVENS(HVIS(SUBTOTAL(3;FORSKYDNING($D$9;RÆKKE($D$9:$D$2000)-RÆKKE($D$9);;1));HVIS($D$9:$D$2000"";SAMMENLIGN($D$9:$D$2000;$D$9:$D$2000&"";0)));RÆKKE($D$9:$D$2000)-RÆKKE($D$9)+1);1))

    Unfortunately, it seems like this formula only work with text values and not with week number even I have formatted the cells as text.

    Therefore, I will appreciate to hear from you what I can do.

    • Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      To count distinct values you can use this array formula:

      =SUM(IF(A1:A20<>"",1/COUNTIF(A1:A20, A1:A20), 0))

      • Thank you for the tip.

        I have tried the formula and it works fine. But when I use it in a column with autofilter, all rows are included in the calculation - even the rows that are not included in the filter.
        Is it possible to adjust the formula so that only the filtered rows are included in the calculation, as it e.g. done in Subtotal (9; range)?

        In my example, the formula should show 3 if week 5 is not included in the filter.

        • Hello!
          Unfortunately, the SUBTOTAL and AGGREGATE functions do not work with arrays. They only work with cell ranges. Therefore, you can write on a separate column the array formula (for example, in cell L1)

          =IF(SUM((EXACT($A1:$A$1,$A1)*($A1<>"")))=1,1,"")

          This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter. After that, you can copy this formula down along the column.
          To count distinct values that are not hidden by the filter, use the formula

          =AGGREGATE(9,5,L1:L50)

          I hope it’ll be helpful.

  3. Hello,

    Below is sample data for a limo service company. My goal is to write a formula to count the number of distinct drivers that drove on a given day with certain criteria (see example criteria below)

    Example Criteria: Confirmed: Yes, Paid: Yes, Size purchased: Sedan

    We can pretend my data is A2 to E13 below:
    Date Confirmed? Paid? Size purchased Driver
    1/1/2022 Yes Yes Sedan Bob
    1/1/2022 Yes Yes SUV Joe
    1/1/2022 No No SUV Joe
    1/1/2022 Yes Yes Sedan Mike
    1/1/2022 Yes Yes Sedan Matt
    1/1/2022 Yes Yes SUV Joe
    1/1/2022 Yes No Sedan Bob
    1/2/2022 No No Sedan Bob
    1/2/2022 Yes Yes Sedan Bob
    1/2/2022 Yes Yes SUV Joe
    1/2/2022 Yes No SUV Steve
    1/3/2022 Yes No Sedan Bob
    1/3/2022 Yes Yes Sedan Bob

    The answer based on the below would be 3 for 1/1/2022 (Bob, Joe and Matt). Bob drove two different customers on 1/1/2022 and Joe and Matt each drove 1, but since I'm looking for distinct count, I only want Bob's name once. I thought I had this solved in a pivot table, but it's not producing the correct answers and a formula would be much easier for the particular report I am producing. Thank you in advance!

    • Correction: the correct names would be Bob, Mike and Matt (not Joe since Joe's in a SUV). Apologies

    • Hello!
      Your data example does not match the description. You can try the formula

      =COUNTIFS(B2:B14,"Yes",C2:C14,"Yes",D2:D14,"Sedan",A2:A14,"="&"01.01.22")

      It gives the result - 3.
      If you want to count the number of unique values in column E, use this formula

      =COUNTA(UNIQUE(FILTER(E2:E14,(B2:B14="Yes")* (C2:C14="Yes")*(D2:D14="Sedan")* (A2:A14=DATEVALUE("01.01.22")))))

      You can find detailed instructions and examples in this article: How to count unique values in Excel with criteria.
      I hope it’ll be helpful.

      • Is it possible to achieve this in excel 2016?

        I have this data and need to do a distinct count of company name where column B is NOT blank

        Company Criteria
        Company A Yes
        Company B Yes
        Company C
        Company D Yes
        Company A
        Company C Yes
        Company A Yes
        Company B

  4. Good morning,

    I'm working on the EEO-4 data report and trying to pull the data without having to manually count how many employees fit the criteria I'm being asked to report on. I've tried using a pivot table and the countifs function but cant seem to quit get it to work. I need to look at 1. Job category, ethnicity, gender and salary range and list how many employees fall under that category.

    Ex: How many male technicians make between $55 - $69,999?

    The report has a salary band that is making it difficult for me to group the salaries by lets say $5k, $10K, etc. Range I need to use is below.
    $0.1 - $15.9
    $16.0 - $19.9
    $20.0 - $24.9
    $25.0 - $32.9
    $33.0 - $42.9
    $43.0 - $54.9
    $55.0 - $69.9
    $70.0 PLUS

    How can I use a pivot table or formula/both to quickly fill in this chart? Do I need to change the format of my spreadsheet in a way that is easier to use a formula? There are multiple job categories, multiple salary ranges, and multiple ethnicities. My excel spreadsheet has the exact job category, salary and ethnicity for each employee.

  5. Hi. How to count the number of dates in the cell for a specific name in another cell?
    e.g:

    SALESPERSON DATE TO CUSTOMER TOTAL NO. OF DATES THE SPECIFIC SALESPERSON SEND OUT
    cindy 11/11/2021 =(TOTAL NO. OF DATES CINDY SENT OUT)
    cindy 15/11/2021
    cindy 18/11/2021
    cindy 21/11/2021
    alan 1/11/2021 =(TOTAL NO. OF DATES ALAN SENT OUT)
    alan 2/11/2021
    alan 3/11/2021
    alan 4/11/2021
    shane 17/11/2021 =(TOTAL NO. OF DATES SHANE SENT OUT)
    shane 18/11/2021

  6. Could you pls advise how to calculate the following with easiest method.

    A B C D E F G H I
    1 X IX III II V I V VII II
    2 IX VIII I VI IV X III II V
    3 IX VIII I VI IV X III II V
    4 V I X IX I II V V VI
    5 VII II X II III IV V VIII I
    6 VIII III IX IX I II VII V VII
    Actually I want to calculate how many period a teacher is taking in each class in a week.
    Hope your reply soon.

  7. I am still struggling with a concise way to count unique values in a column based on the values in another column. For example, let's say I want to count the unique occurrences of a name in Column A based on Column B containing the keyword "count". Say that this is my data in A1:B6:

    Robert count
    Mitch
    David
    Jeremy count
    Robert count
    David count

    The desired output is 3, because there are 3 unique names marked "count". However, the array formula ={SUM(IF(B1:B6 = "count", 1/COUNTIF(A1:A6, A1:A6), 0))} returns 2.5, because the unmarked David is included in the COUNTIF denominator. What would be the right formula to do what I'm trying to do?

    • Hello!
      You can learn more about counting unique values with criteria in Excel in this article on our blog.

      =IFERROR(ROWS(UNIQUE(FILTER(A2:A10,B2:B10="count"))), 0)

      This should solve your task.

  8. Is there any excel formula where the distinct count and count if formula numbers should match..If count if formula used its splits the decimals based on same items , i dont want to splt that.

    I have a key combination (ID+Dept)

    Can some help

  9. Hi
    The formula you give
    =IF(A1 < TODAY(),"", A1-TODAY())
    is not showing the minus number of day or days
    and this below
    =IF(ISBLANK(A1),"",A1-TODAY())
    is working but I want the result cell will show with the text Day or Days including the Numbers. For example, 2 Days, 1 Day, -1Day, -3 Days & I want Zero Day will not show, or the cell (Zero Day) will blank.

    Please also suggest how to share my excel file with you.

    My second question is the subtraction of two dates comes zero (A1-A2=0) I want the cell will show the text "Same Day" and if A1 & A2 is blank result will show also blank, hope you understand, thanks in advance.

    Regards
    Sazedul Munna

      • Hi
        I tried many ways but I can't get my expected result. I can't get the number including text Day or Days, For example, 2 Days, 1 Day, -1Day, -3 Days & I want Zero Day will not show, or the cell (Zero Day) will blank. I would like to share my excel file with you to solve these.

        Thanks
        Sazedul Munna

  10. Hi
    This is Sazedul from Bangladesh, I face some problems of my own sheet when I'm working on it if I get support from you will develop the sheet of my own style. Problems are as follows -

    A) How can I count a number which is the double number, I have these numbers (1,2,3,4,5,11,23) and my problem is, the double number is counted separately. Instead of 7 it counted 9. Here I use this formula
    =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,2,3,4,5,6,7,8,9,0},)))

    B)
    FABRIC REC DATE FABRIC FOLD DATE DURATION
    06-Mar-21 12-Apr-21 37 Days
    15-Mar-21 15-Mar-21 Same Day
    08-Mar-21 12-Mar-21 04 Days
    #VALUE!
    15-Apr-21 19-May-21 34 Days
    #VALUE!
    #VALUE!
    #VALUE!
    Here I want a DURATION of FABRIC FOLD DATE & FABRIC REC DATE
    as you see in the above. But result in these blank cell shows #VALUE!
    In DURATION Column I use this formula
    =IF([@[FABRIC FOLD DATE]]-[@[FABRIC REC DATE]]=0,"Same Day",[@[FABRIC FOLD DATE]]-[@[FABRIC REC DATE]])

    How do I solve this?

    C)
    REMAIN DAYS MONTH STATUS GD RECEIVED ALLOTED GREIGE DATE
    4 Days May-21 In Progress 06-May-2021 15 Days 21-May-2021
    10 Days May-21 Complete 12-May-2021 15 Days 27-May-2021
    5 Days Apr-21 Delayed 12-Apr-2021 40 Days 22-May-2021
    -6 Days Apr-21 Not Started 16-Apr-2021 25 Days 11-May-2021
    15 Days May-21 Delayed 17-May-2021 15 Days 01-Jun-2021
    #VALUE! Complete
    #VALUE! Delayed
    #VALUE! Not Started
    Here I Want if GREIGE DATE column is blank REMAIN DAYS will blank but I can’t make it.
    In REMAIN DAYS Column I use this formula
    =Reoprt[@[GREIGE DATE]]-TODAY() {Here Report is the Table name}

    Please help me to resolve these & will be grateful to you.

    Best Regards
    Sazedul Munna

    • Hello!
      If your numbers in the text are separated by commas, use the formula

      =LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

      You will count how many numbers are in the text.
      Question B is not clear to me.
      To avoid subtracting the current date from an empty cell, use the formula

      =IF(A1 < TODAY(),"", A1-TODAY())
      or
      =IF(ISBLANK(A1),"",A1-TODAY())

      I hope it’ll be helpful.

      • Thanks for your reply, but it's not working

          • Hi
            Extremely sorry actually it's my mistake, your formula is working....
            =LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

            In the excel table there shows 1 if the formula is put my question is how can I keep the cell blank, thanks in advance.

            I have another question suppose in a cell I want subtraction of two dates and result comes Zero Day or 1 Day or 2 Days or Minus 1 Day or Minus 2 Days. How do the cells show Day or Days including the Numbers as mentioned? For example, 2 Days, 1 Day, -1Day, -3 Days & I want Zero Day will not show or the cell (Zero Day) will blank. How can I do this?

            Regards
            sazedul Munna

  11. Hello!

    Suppose if in column A text is given i want duplicate will give same Previous No and unique text will be sum of highest no

    eg.
    C-A C-B
    XYZ - 1
    ABC - 2
    CDE - 3
    XYZ - 1
    DEF - 4
    ABC - 2
    DEF - 4

    *Column-A is text & Column B i want
    Can you pls help me t

    • Hello!
      Your list should start in cell A2. Suppose your data are in column A, please try to enter the following formula in cell B2 and then copy it down along the column:

      =IF(COUNTIF($A$1:A2,A2)=1, MAX($B$1:B1)+1, INDEX($B$1:B1,MATCH(A2,$A$1:A2,0)))

      This should solve your task.

  12. Hi,

    I am giving below the sample data for understanding. I want the course wise unique count of the student. Can you pls help me to get it through count unique formula?

    Sample Data:
    Student Name Course
    Ajay NID
    Vijay NIFT
    Nilesh NID + NIFT
    Mahesh NIFT
    Ajay NID + NIFT
    Vidya ARCH
    Sudha NID + NIFT
    Amit NID
    Siddhesh ARCH

    Thanks.
    Narendra

  13. Per your response, below is a sample of data and desired query.

    DATA
    | A | B | C | D | E |
    1 | NAME | CREDIT | REF DATE | ADM DATE | SHARED R/AA |
    2 | N1 | .05 | 01/02/21 | 01/03/21 | Y |
    3 | N2 | .05 | 01/02/21 | 01/03/21 | YY |
    4 | N3 | 1 | 01/02/21 | 01/03/21 | |
    5 | N1 | .05 | 01/02/21 | | Y |
    6 | N1 | .05 | 01/02/21 | | Y |

    (LINE 1 A-E are the headers)
    (LINES 2-6 A-E is the data)

    QUERY RESULTS NEEDED (based on data above)

    REFERRALS BY NAME (results)
    FORMULA attempted for NAME N1:
    =countifs(A2:A6,"N1", B2:B6 "sum", E2:E6, "Y" or "empty") result should be 1.5

    FORMULA attempted for NAME N2:
    =countifs(A2:A6,"N2", B2:B6 "sum", E2:E6, "Y" or "empty") result should be 0.5

    FORMULA attempted for NAME N3:
    =countifs(A2:A6,"N3", B2:B6 "sum", E2:E6, "Y" or "empty") result should be 1

    ADMITS BY NAME (results)
    FORMULA attempted for NAME N1:
    =countifs(A2:A6,"N1", B2:B6 "sum", E2:E6, "YY" or "empty") result should be 0.5

    FORMULA attempted for NAME N2:
    =countifs(A2:A6,"N2", B2:B6 "sum", E2:E6, "YY" or "empty") result should be 0.5

    FORMULA attempted for NAME N3:
    =countifs(A2:A6,"N3", B2:B6 "sum", E2:E6, "YY" or "empty") result should be 1

    Obviously, my logic is wrong. I hope this clarifies my desired results.
    With thanks,
    Bob

    • Hi,
      The COUNTIFS function counts the number of values. Therefore, the result of its calculations can only be an integer. I am assuming that you want to calculate the amount. So use the SUMIFS function.
      Perhaps this formula will work for you.

      =SUMIFS(B2:B6,A2:A6,"N1",E2:E6,"Y") + SUMIFS(B2:B6,A2:A6,"N1",E2:E6,"")

      However, I don't understand how you want the result 1.5 if the sum of all the numbers is 1.2.
      You can learn more about SUMIFS in Excel in this article on our blog.
      You can learn more about COUNTIFS in this guide.

  14. Greetings
    I'm in need of guidance on the following:
    I need to get a count of two columns of data:

    COLUMN A is the credit for a referral, that referral has a value of 0 or .5 or 1
    COLUMN B is the date of the referral and if shared (e.g: the .5 from COLUMN A) will show twice.

    I have attempted COUNTIFS but just get a total of COLUMN B or an ERROR when criteria for COLUMN A is included.

    Hoping someone can help.
    Bob

    • Hello!
      Your task is not completely clear to me. Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

  15. Hello,
    I want unique count of patient by location. I know how to do this using Pivot, but i don't know how to do this with excel formulas.

  16. Hi,
    I have one row of data containing some items, and another row of data containing those items again. I need to see how many of them are repeated in the second row, excluding duplicates. There may be blanks also but they should not be part of the output.
    e.g. row 1: {a,b,c,c,d,e,f,g,g}
    row 2: {a,c,d,d,e,g,h}
    Output should be 5 (i.e. a,c,d,e,g are repeated in row 2).

    Thanks a lot for helping.

  17. Hi friend..

    Need your support in excel
    Column D contain n' number of cities names
    Column E contain some numeric values (numbers contain only in few cells of column E but not all)
    Can you please help me in framing a excel formula to count unique values in column D (count cities) where numeric value in column E is greater than ZERO
    One city may repeat in column D and have values in column E but the city would be counted as one only
    This is my request

  18. Thank You!

  19. I've a requirement which i'm unable to figure out.
    There are dates in a column, names in another column, product IDs in another column.
    Names & product ID's would repeat.
    How to create a crosstab report with names as colum header, month as row header & count of distinct product IDs as data fulfilling the headers.
    Thanks in advance.. :)

  20. I have a simple problem, but I cannot get my head round how to do it, and not found an answer on the web.

    I have a column of multiple entries of 1000 mixed up products. Each product has a column with a quantity.

    So OK its easy to sort products, using inbuilt 'sort' on a column.

    The next bit I fail on. I have tried a lot of variants of countif.

    A - 2
    A - 4
    B - 5
    B - 6

    I want a result as follows

    A - 6
    B - 11

    Any ideas please.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)