Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
tjtjjtjt
 
Posts: n/a
Default Counting groups of exact case numbers w/letters in them.

Do you need the entire Column--from A1 all the way to A65536? Or, do you just
need the formula to always account for the all the entries in Column A?
I might be misremebering, but I don't believe that MATCH can accept an
entire Column as an argument.

If all the Numbers are in a contiguous range (no blanks Rows), then you can
do the following:
1. Click on cell A1 (the first cell in the range)
2. Insert | Name | Define
3. Type MyRange for the Name: (no spaces)
4. Then, in the Refers to line, type this:
=OFFSET(Sheet8!$A$1,0,0,COUNTA(Sheet8!$A:$A))
5. Then, try this formula (a modification of the one Domenic posted:
=SUM(IF(FREQUENCY(IF(myrange<"",MATCH(myrange,myr ange,0)),IF(myrange<"",MATCH(myrange,myrange,0))) 0,1,0))

Make sure you enter it with Ctrl+Shift+Enter.
You may need to copy the formula to a text editor (like Notepad) to make
sure you get it all on one line and don't have any spaces at the end of it.
Then, copy from the text editor to Excel. Double_click on the cell, and then
press Ctrl+Shift+Enter.

If you have blank rows in the data list, this formula may not return the
correct result.


tj
  #2   Report Post  
tjtjjtjt
 
Posts: n/a
Default

Here is a programmatic solution that may work for you. This macro was taken
from:
http://www.exceltip.com/show_tip/Pri...Excel/520.html

If you are new to macros, look at before using the code below:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Function CountUniqueValues(InputRange As Range) As Long
Dim cl As Range, UniqueValues As New Collection
Application.Volatile
On Error Resume Next ' ignore any errors
For Each cl In InputRange
UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item
Next cl
On Error GoTo 0
CountUniqueValues = UniqueValues.Count
End Function

Hope this helps,
tj

  #3   Report Post  
Domenic
 
Posts: n/a
Default


*Jarom* Wrote:
doesn't seem to be working. :(


What do you mean it doesn't work? The formula is correct and works
fine. Are you trying to reference the entire column? If so, try the
second formula I offered instead...

=SUM(IF(A1:A65535<"",1/COUNTIF(A1:A65535,A1:A65535)))

...entered using CONTROL+SHIFT+ENTER.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=320435

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"