Hello everybody
How an array function calculates? for ex
{sum(1/(countif(range, criteria)} returns the number of non-repeats
I tried to analyse it in the following way, but of no use
CIF ARRY-CIF 1/CIF ARRAY-1/CIF SUM-CIF ARAY-SUMCIF SUM-1/CIF ARAY-1/SUMCIF
Cow 2 2.00 0.50 0.50 2.00 17.00 0.50 3.00
cat 2 2.00 0.50 0.50 2.00 17.00 0.50 3.00
mat 3 2.00 0.33 0.50 3.00 17.00 0.33 3.00
cow 2 2.00 0.50 0.50 2.00 17.00 0.50 3.00
cat 2 2.00 0.50 0.50 2.00 17.00 0.50 3.00
mat 3 2.00 0.33 0.50 3.00 17.00 0.33 3.00
mat 3 2.00 0.33 0.50 3.00 17.00 0.33 3.00

CIF COUNTIF($A$2:$A$8,$A$2:$A$8)
SUM-CIF SUM(COUNTIF($A$2:$A$8,$A$2:$A$8))
Thanks to all
regards
Sridhar

"yshridhar" wrote in message
...
Hello everybody
How an array function calculates? for ex
{sum(1/(countif(range, criteria)} returns the number of non-repeats
I tried to analyse it in the following way, but of no use
CIF ARRY-CIF 1/CIF ARRAY-1/CIF SUM-CIF ARAY-SUMCIF SUM-1/CIF ARAY-1/SUMCIF
Cow 2 2.00 0.50 0.50 2.00 17.00 0.50 3.00
cat 2 2.00 0.50 0.50 2.00 17.00 0.50 3.00
mat 3 2.00 0.33 0.50 3.00 17.00 0.33 3.00
cow 2 2.00 0.50 0.50 2.00 17.00 0.50 3.00
cat 2 2.00 0.50 0.50 2.00 17.00 0.50 3.00
mat 3 2.00 0.33 0.50 3.00 17.00 0.33 3.00
mat 3 2.00 0.33 0.50 3.00 17.00 0.33 3.00

CIF COUNTIF($A$2:$A$8,$A$2:$A$8)
SUM-CIF SUM(COUNTIF($A$2:$A$8,$A$2:$A$8))
Thanks to all
regards
Sridhar

It's often a good idea to work with a small array and simple data to get the
idea, for example
=SUM(1/COUNTIF(A1:A5,A1:A5))
(array-entered)
with the data dog,cat,cat,dog,dog in A1:A5.

In any formula, you can highlight part of it in the formula bar and press F9
to show the result of that part of the formula.
For example, highlight COUNTIF(A1:A5,A1:A5) and press F9 and you will see
the resulting array {3;2;2;3;3} highlighted in the overall expression
=SUM(1/{3;2;2;3;3}).
(Press ESC to get out of this mode. Pressing ENTER would replace the
function by the result in the formula!)

Now it's easy to see what this is doing. It will sum 1/3, 1/2, 1/2, 1/3, 1/3
to give the result of 2.
Each data value that occurs 3 times adds 1/3 into the result; each data
value that occurs 2 times adds 1/2 into the result.
In general, each data value that occurs n times adds 1/n into the result.
Hence a total of 1 is added into the result for each different data value,
giving a count of the number of unique data values.

Thank you Stephen. It is nice to follow the calculation of a function by
highligting and F9. I don't know about this option. I will try to the other
functions also.
Thanks alot
Regards
Sridhar

"Stephen" wrote:

"yshridhar" wrote in message
...
Hello everybody
How an array function calculates? for ex
{sum(1/(countif(range, criteria)} returns the number of non-repeats
I tried to analyse it in the following way, but of no use
CIF ARRY-CIF 1/CIF ARRAY-1/CIF SUM-CIF ARAY-SUMCIF SUM-1/CIF ARAY-1/SUMCIF
Cow 2 2.00 0.50 0.50 2.00 17.00 0.50 3.00
cat 2 2.00 0.50 0.50 2.00 17.00 0.50 3.00
mat 3 2.00 0.33 0.50 3.00 17.00 0.33 3.00
cow 2 2.00 0.50 0.50 2.00 17.00 0.50 3.00
cat 2 2.00 0.50 0.50 2.00 17.00 0.50 3.00
mat 3 2.00 0.33 0.50 3.00 17.00 0.33 3.00
mat 3 2.00 0.33 0.50 3.00 17.00 0.33 3.00

CIF COUNTIF($A$2:$A$8,$A$2:$A$8)
SUM-CIF SUM(COUNTIF($A$2:$A$8,$A$2:$A$8))
Thanks to all
regards
Sridhar

It's often a good idea to work with a small array and simple data to get the
idea, for example
=SUM(1/COUNTIF(A1:A5,A1:A5))
(array-entered)
with the data dog,cat,cat,dog,dog in A1:A5.

In any formula, you can highlight part of it in the formula bar and press F9
to show the result of that part of the formula.
For example, highlight COUNTIF(A1:A5,A1:A5) and press F9 and you will see
the resulting array {3;2;2;3;3} highlighted in the overall expression
=SUM(1/{3;2;2;3;3}).
(Press ESC to get out of this mode. Pressing ENTER would replace the
function by the result in the formula!)

Now it's easy to see what this is doing. It will sum 1/3, 1/2, 1/2, 1/3, 1/3
to give the result of 2.
Each data value that occurs 3 times adds 1/3 into the result; each data
value that occurs 2 times adds 1/2 into the result.
In general, each data value that occurs n times adds 1/n into the result.
Hence a total of 1 is added into the result for each different data value,
giving a count of the number of unique data values.