counting distinct entries in a range in excel

[amberH]

Hi all,

I found a formula on a web page for counting distinct entries in a range of cells in Excel. It works on one of my worksheets but not on another. I'm baffled.

The following formula I use in one sheet and it works great:

=SUM(IF(COUNTIF(C11:C1000,C11:C1000)=0,"",1/COUNTIF(C11:C1000,C11:C1000)))

it counts all unique values in the column.
Now I'm trying to do the same for a range as follows:

=SUM(IF(COUNTIF(AV21:BA29,AV21:BA29)=0,"",1/COUNTIF(AV21:BA29, AV21:BA29)))

and I get 'error #VALUE'.
The example I found on the web page (

http://j-walk.com/ss/excel/usertips/tip061.htm)

uses a similar range in it's example:

=SUM(IF(COUNTIF(A1100,A1100)=0,"",1/COUNTIF(A1100,A1100)))

Does anyone have any idea why my 2nd formula won't work??
I'm baffled!
Cheers,
amber

 

[Hasit]

Use an array formula to perform the calculation. If i understand array formulas correctly, becaue you are covering a range, you will need to do a CTRL-SHIFT-ENTER (I have tested the formula and it works in array form).

This is the same even if the range was defined.

Hope that makes sense.

 

[Hasit]

I was thinking about your question and as long as there are no gaps (spaces etc), this formula as an array will work too:

=SUM(1/COUNTIF(AV21:BA29,AV21:BA29))

Note, do a CTRL-SHIFT-ENTER.