Finding the range of non numeric cells

C

clane

Hello

my problem is that i have a group of cells that contain 1 2 and
letter codes and i need to count them. I want to just use the counti
function but first a need the range of all the codes and i cant seem t
figure out how to get it? any help would be apreciated

Thanks
 
M

Max

Maybe try Advanced Filter to get a "uniques" list ?

Assume your data is in col A
Type a col label in A1
Select col A
Click Data > Filter > Advanced Filter
[Click OK to the Excel prompt to use the first row as labels]

In the Advanced Filter dialog box:
----------------------------------------
Check "Copy to another location"
Ensure that the selected range correctly appears in the "List range:" box
Put for "Copy to:" : B1
Check "Unique records only"
Click OK

The unique list of codes in col A
will be extracted in col B
 
D

Don Guillett

See if this helps to find the number of the last row with text.

=MATCH("*",B:B,-1)

now place inside an indirect formula and then the countif formula.

=COUNTIF(INDIRECT("b1:b"&MATCH("*",B:B,-1)),"e")
 
C

clane

thanks for the help unfortunatly that doesnt seem to work

maybe this will help

if i type in a cell =range(the range of cells) and hit the fx button o
the tooobar to bring up the formula help box it shows part of the rang
next to the formula but it is unable to define the results is ther
anyway to get that range to display as the formula results?

thanks again

chuc
 
C

clane

i dont think the range function is actully part of excel

but if you type in the cell =range(a1:a500) then hit the fx butto
(insert formula) next to the formula bar it will show up in the windo
that pops up an next to it there wil be the first few values in quotes
but it says there is no formula result and that its undefine
 
D

Dave Peterson

If you're counting the number of times each unique entry shows up, then add a
helper formula (after doing the data|filter|advanced filter stuff in Max's
post):

=countif(a2:a9999,b2)

and drag down to match column B's entries.

====
Another option is to use a pivottable.

Select your column (only the last headerrow), then

data|pivottable.
follow the wizard until you hit the dialog with a "Layout" button on it.
click that Layout button.

Drag the header to the row field
Drag the header to the Data field (again)
If it doesn't say "Count of", then double click on it and change it to "count
of".

Finish up the wizard.
 
M

Max

Dave Peterson said:
.. =countif(a2:a9999,b2) ..
Click to expand...

Dave, thanks for the follow-on ..

.. =countif($a$2:$a$9999,b2) ..
Click to expand...

Must admit I disregarded the OP's subject line
and focused on this part of the original post only ..:
.. I want to just use the countif
function but first a need the range of all the codes ..
Click to expand...

which gave me the impression that he knew how to apply countif,
but was asking for a way to extract a list of uniques first
from the data (before the countif could be applied)
 
D

Dave Peterson

Yeah, I play fast and loose with those $ signs.

Thanks for the correction.

(and it needs to be absolute for dragging down--not for any particular formula)
 
Top