How to count distinct values in Excel

0 votes

Input

enter image description here

I need Summary like below

enter image description here

I am looking for distinct no of account here instead of duplicate.

Mar 10 in Database by Edureka
• 9,700 points
40 views

1 answer to this question.

0 votes

Use functions to count the number of distinct values.
To complete this problem, combine the functions IF, SUM, FREQUENCY, MATCH, and LEN:

The IF function should be used to assign a value of 1 to each true condition.

By utilizing the SUM function, you may add the total.

Use the FREQUENCY function to count the number of distinct values. Text and zeros are ignored by the FREQUENCY function. This function returns a number equal to the number of occurrences of a specific value for the first time that value is encountered. This function returns a zero for each subsequent occurrence of that same value.

Using the MATCH function, you may find out where a text value is in a range. After that, the value returned is used as.

The MATCH function returns the position of a text value in a range. This value is subsequently passed as an argument to the FREQUENCY function, which evaluates the matching text values.

The LEN function can be used to find blank cells. The length of blank cells is 0.

Using a filter, count how many distinct values there are.


You can paste the unique values from a column of data into a new location using the Advanced Filter dialogue box. The number of items in the new range can then be counted using the ROWS function.

Make sure the active cell is in a table, or select a range of cells.

Make sure there's a column header in the range of cells.

Click Advanced from the Data tab's Sort & Filter group.

A dialogue window for the Advanced Filter appears.

Copy to a new location by clicking the Copy button.

Fill in a cell reference in the Copy to box.

Alternatively, you can temporarily conceal the dialogue box by clicking the Collapse Dialog Button picture, then selecting a cell on the worksheet and pressing the Expand Dialog Button image.

Click OK after checking the box that says "Unique records only."

Beginning with the cell you indicated in the Copy to box, the unique values from the selected range are copied to the new place.

Enter the ROWS function in the blank cell just below the range's last cell. As the parameter, paste the range of unique values you just copied, without the column heading. If the range of unique values is B2:B45, for example, you would type =ROWS (B2:B45).

answered Mar 15 by Edureka
• 8,820 points

Related Questions In Database

0 votes
0 answers

How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

 need the row/column combinations marked with an ...READ MORE

Feb 24 in Database by Edureka
• 9,700 points
31 views
0 votes
1 answer
0 votes
1 answer

How to Calculate DATEDIF formula in Excel?

Simply divide one date by the other. ...READ MORE

answered Mar 25 in Database by Edureka
• 8,820 points
43 views
0 votes
1 answer

how to Convert EST time to IST time in excel?

Use the following calculation since Indian Standard ...READ MORE

answered Mar 25 in Database by Edureka
• 8,820 points
42 views
0 votes
1 answer

how to Convert EST time to IST time in excel?

Use the following calculation since Indian Standard ...READ MORE

answered Mar 30 in Database by Edureka
• 8,820 points
59 views
0 votes
1 answer

Count unique values in a column in Excel

Excel may be used to count unique ...READ MORE

answered Mar 30 in Database by Edureka
• 8,820 points
29 views
0 votes
1 answer

How to download excel in response from api react.js

Create ReactJS project import React, { Component } ...READ MORE

answered Mar 31 in Database by Edureka
• 8,820 points
144 views
0 votes
1 answer

How to sort dates from Oldest to Newest in Excel?

Drag down the column to select the ...READ MORE

answered Feb 23 in Database by Edureka
• 8,820 points
36 views
0 votes
1 answer

[Excel][VBA] How to draw a line in a graph?

Sub MakeChart() Dim x(20) ...READ MORE

answered Mar 24 in Database by Edureka
• 8,820 points
30 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP