Hi, further to this thread from some time ago, I have an additional requirement of a formula based on the one issued by Aladin Akyurek =IF(LEN(A1:A6),SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6)))
I want to know how many times, any values in a range are repeated a certain number of times. I'm going round in circles trying to solve this.
For example, in a column of cells containing say 1,1,1,1,2,2,2,2,3,3,3,3,4,4,5,5,6,6,6 then the formula above will return 6, as there are 6 unique values. What I want to do is know how many any values appear say 4 times (the reply would be 3, as 1,2 and 3 all appear 4 times). I realise a pivot table would be the easiest way of achieving this, but I really would like to accomplish it via formula instead.
Many thanks in advance!
Assuming too much and qualifying too much are two faces of the same problem.
Thanks, that works perfectly.
I wont even try to pretend I understood most of that formula, but it works!
Although it does not provide explanations how the relevant formulas work, the following link might interest you:
http://www.mrexcel.com/forum/showthread.php?t=292473
Assuming too much and qualifying too much are two faces of the same problem.
Hi,
Is there a way to get this to work on non-contiguous cells (ie cell ranges that are not next to each other?)
Thanks!
Bolo
If any cell in A1:A6 is empty or house a formula blank:
SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6))
SUM(1/COUNTIF(A1:A6,A1:A6)) [ Original formula; Requires control+shift+enter ]
both would yield a #DIV/0!
To circumvent that issue and not to count an empty cell or a blank as a distinct item, you'd need...
With the SumProduct version:
SUMPRODUCT((A1:A6<>"")/COUNTIF(A1:A6,A1:A6&""))
With the Sum Version:
SUM(IF(LEN(A1:A6),1/COUNTIF(A1:A6,A1:A6))) [ Requires control+shift+enter ]
of which the shorter version is:
SUM(IF(A1:A6<>"",1/COUNTIF(A1:A6,A1:A6)))
Here are some links with explanations...
http://www.mrexcel.com/forum/showthread.php?t=36118
http://www.mrexcel.com/forum/showthread.php?t=16682
http://www.mrexcel.com/forum/showthread.php?t=70835
Also, regarding efficiency issues...
http://www.mrexcel.com/forum/showthread.php?t=292473
Assuming too much and qualifying too much are two faces of the same problem.
so the original formula was missing the outside sum!!! that is what confused me!
Thanks for the explanations Aladin.
Like this thread? Share it with others