How to count number of different values

[Clutter66]

New versions of Excel may have the UNIQUE function but not so in my 2016 version.

=SOMPRODUCT(1/COUNTIF(A1:A8;A1:A8)) seems to do the trick (for nonzero en non empty cells), but I have no clue how that works. Excel evaluate formula als does not realle help.

Anybody who can explain his matrix formula ?

 

[Phuoc]

Try this:

=SUMPRODUCT(1/COUNTIF(A1:A8,A1:A8&"")) - (COUNTBLANK(A1:A8)>0 )

 

[Peter_SSs]

my 2016 version.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Anybody who can explain his matrix formula ?

If there were 3 of the same values in A1:A8 then the formula divides 1 by 3 for each of them so those rows would become 1/3, 1/3 and 1/3. When you add those values (with SUMPRODUCT) you get 1, meaning those three rows represent one unique value.

If there is a value in A1:A8 that only occurs once then for that row the formula would produce 1/1 = 1, counting that value as one unique value.
etc

 

[Clutter66]

Thanks. I have updated my profile as suggested. The explanation is understood, at least the 1/ part. However, it is not yet clear hoe Excel interprets the criteria, in this case a cell range reference, in this formula.
The Microsoft explanation does not really help "=COUNTIF(Where do you want to look?, What do you want to look for?)". What would be the matrix result of "Looking in 1 2 2 3 for 1 2 2 3"

 

[felixstraube]

COUNTIF, counts based on the criteria in the second argument.
So for example if you have

Book1
	A	B	C	D
1	1	1		2
2	2
3	3
4	1
5	2
6	3
7	2
8	5
Sheet1

Cell Formulas
Range	Formula
D1	D1	=COUNTIF(A1:A8, B1)

It counts all the cells in A1:A8 that are equal to the value in B1 (that is 1). Answer is one value and it counts it 2 times.
Now if you give the criteria argument an array, lets say:

Book1
	A	B	C	D
1	1	1		2
2	2	2		3
3	3
4	1
5	2
6	3
7	2
8	5
Sheet1

Cell Formulas
Range	Formula
D1:D2	D1	=COUNTIF(A1:A8, B1:B2)
Dynamic array formulas.

It does the same, first counts the 1's, and then it counts the 2's., and the result is an array of length 2, with the count of 1's (counts 2) and the count of 2's (counts 3).

What the formula you provided does, is it counts the total number of appearances of every value in the array being the criteria array the same as the data array.

Book1
	A	B	C	D
1	1			2
2	2			3
3	3			2
4	1			2
5	2			3
6	3			2
7	2			3
8	5			1
Sheet1

Cell Formulas
Range	Formula
D1:D8	D1	=COUNTIF(A1:A8, A1:A8)
Dynamic array formulas.

If you analice the result of the result array of COUNTIF it is:

				1	2	3	5
1			2	2
2			3		3
3			2			2
1			2	2
2			3		3
3			2			2
2			3		3
5			1				1

For the criteria "1" you get 2 counts of 2, for the criteria "2", you get 3 counts of 3, for criteria "3" you get 2 counts of 2, and finally for criteria "5" you get 1 count of 1.

Then your formula divides 1 in that numbers.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1					1	2	3	5		1	2	3	5
2	1			2	2					0.5
3	2			3		3					0.333333
4	3			2			2					0.5
5	1			2	2					0.5
6	2			3		3					0.333333
7	3			2			2					0.5
8	2			3		3					0.333333
9	5			1				1					1
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=COUNTIF(A2:A9, A2:A9)
J2:M9	J2	=IFERROR(1/E2, "")
Dynamic array formulas.

if you add the those numbers up, you add 1 for the "1" criteria, 1 for the "2" criteria, and so one. So you add a total of 4.

Hope this helps

 

[Clutter66]

So basically the formula normalizes each value present to a contribution of 1, (hence, non present becomes 0) by multiplying its frequency by its frequency reciproque, which basically is a count of the unique different values. How very clever.

Thanks so much for the detailed explanation. Is there away to output the result of such a matrix, or is just the sumproduct which makes uses of this Excel internal results?

 

[felixstraube]

You are very welcome.
If you have excel 2021 or 365 (you could try the free web app) you could just enter this formula hit enter and you will get the returned array:

Book5.xlsx
	A	B
1
2	1	0.5
3	2	0.333333
4	3	0.5
5	1	0.5
6	2	0.333333
7	3	0.5
8	2	0.333333
9	5	1
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=1/COUNTIF(A2:A9, A2:A9)
Dynamic array formulas.

With excel 2016 you will have to select the rang B2:B9, enter the formula and hit Ctrl+Shift+Enter. You should get the same result. I can't try it because i dont have 2016 anymore, but i'm quite sure it should work.

Or you can edit the formula, select the part you are interested in to see the result, for example:

You should see the result of the selected part after evaluation above. Or in that moment you can hit F9 and you will get the result in the formula textbox, like so:

Just make sure to hit ESC and not ENTER, because if you do the formula is replaced by the value.

But I'm not quite sure if this feature is available in excel 2016.

Try it and let me know.

 

[Clutter66]

Wauw, after 45 years of daily Excel use, I had not expected to learn these tricks (matrix formula and edit/evaluate the results. Very insightful. Thanks