Home OS Windows How to count unique values in column in Excel

How to count unique values in column in Excel

Sorting out the variety of distinctive and distinct values from a listing throughout a column in an Excel sheet may very well be helpful for a lot of functions. You may must calculate the variety of gamers who gained a single occasion (or a number of) or the variety of distinctive gadgets bought to a buyer (or distinct). In this example, such sorting could be very useful.

How to rely distinctive values in column in Excel

Excel-Logo

Count the variety of distinctive values from a listing of a column utilizing the array system

Count the number of unique values from a list of a column using the array formula

The syntax for counting the variety of distinctive values from a listing of a column utilizing the array system is as follows:

=SUM(IF(COUNTIF(<first cell from which you rely the variety of distinctive values>:<final cell until which you rely the variety of distinctive values>,<first cell from which you rely the variety of distinctive values>:<final cell until which you rely the variety of distinctive values>)=1,1,0))

Where,

  • <first cell from which you rely the variety of distinctive values> is the primary cell within the column from which you start the rely.
  • <final cell until which you rely the variety of distinctive values> is the final cell within the column until which you rely.

Eg. Let us say we’ve got a listing of sports activities and the gamers who gained at every sport. The successful gamers are listed from cell B3 to B12. The system for distinctive values of the gamers would turn out to be:

=SUM(IF(COUNTIF(B3:B12,B3:B12)=1,1,0))

Place this system within the cell the place you want the variety of distinctive values (say cell D3) and press CTRL+SHIFT+ENTER. This will allow the array system. Now click on wherever exterior the cell and you’re going to get the required variety of distinctive values.

Count the variety of distinct values from a listing of a column utilizing the array system

How to count unique values in column in Excel

The syntax for counting the variety of distinct values from a listing of a column utilizing the array system is as follows:

=SUM(IF(<first cell from which you rely the variety of distinct values>:<final cell until which you rely the variety of distinct values><>"",1/COUNTIF(<first cell from which you rely the variety of distinct values>:<final cell until which you rely the variety of distinct values>,<first cell from which you rely the variety of distinct values>:<final cell until which you rely the variety of distinct values>), 0))

Where,

  • <first cell from which you rely the variety of distinct values> is the primary cell within the column from which you start the rely.
  • <final cell until which you rely the variety of distinct values> is the final cell within the column until which you rely.

Eg. Assume a case the place we’ve got a listing of sports activities and the gamers who gained at every sport. The successful gamers are listed from cell B3 to B12. The system for distinct values of the gamers would turn out to be:

=SUM(IF(B3:B12<>"",1/COUNTIF(B3:B12, B3:B12), 0))

Place this system within the cell the place you want the variety of distinct values (say cell E3) and press CTRL+SHIFT+ENTER. This will allow the array system.

Now click on wherever exterior the cell and you’re going to get the required variety of distinct values.

Hope this helps!

Count the number of unique values from a list of a column using the array formula

Most Popular

Amazon launches Care Hub for family to monitor senior citizens

Amazon today introduced Care Hub, a way for people to monitor the activity of older adults or people who wish to live independent lives...

Mugshots from U.S. Customers and Border Protection database leaked onto the dark web

Last June, a U.S. Customs and Border Protection (CBP) subcontractor breach exposed over 184,000 photos of people collected as part of the Vehicle Face...

Apple and Facebook spin antitrust claims: ‘We’re misunderstood’

In late July, a Congressional subcommittee successfully compelled four major tech company CEOs to formally answer questions over allegedly monopolistic business practices — a...

Nuvia raises $240 million to make Arm-based server CPUs

Forward-looking: While the spotlight is on Nvidia's acquisition of Arm, a new...

Recent Comments