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, 2022 in Database by Edureka
• 13,690 points
1,405 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, 2022 by gaurav
• 23,260 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, 2022 in Database by Edureka
• 13,690 points
2,036 views
0 votes
1 answer
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, 2022 in Database by gaurav
• 23,260 points
1,088 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, 2022 in Database by gaurav
• 23,260 points
2,017 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, 2022 in Database by gaurav
• 23,260 points
10,482 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, 2022 in Database by gaurav
• 23,260 points
1,062 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, 2022 in Database by gaurav
• 23,260 points
7,673 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, 2022 in Database by gaurav
• 23,260 points
5,208 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, 2022 in Database by gaurav
• 23,260 points
2,019 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP