Counting Excel 97 cells 2

[jimoblak]

I'm trying to count the number of cells in column A that have data. Some cells are blank. Is 'COUNTIF' ideal? How would you count filled cells of varying data?

 

[SkipVought]

Hi,

How about COUNTA?

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[Lilliabeth]

counta

 

[jimoblak]

Perhaps I asked too easy of a question. (thanks)

Can I complicate it with a further condition?

How would I count distinct cells?

example:
apple
orange
apple

pear
apple

...would equal 3

 

[Lilliabeth]

=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

Do not just hit Enter to enter, hold down Shift and Ctrl and hit enter.

 

[Lilliabeth]

Or...

Count the number of unique values by using a filter
You can use the Advanced Filter to extract the unique values from a column of data and paste them to a new location. Then you can use the ROWS function to count the number of items in the new range.

Ensure that the first row in the column has a column header.
On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Copy to another location.
If the range that you are counting is not already selected, delete any information in the List range box and then click the column (or select the range) that contains your data.
In the Copy to box, delete any information in the box or click in the box, and then click a blank column where you want to copy the unique values.
Select the Unique records only check box, and click OK.
The unique values from the selected range are copied to the new column.

In the blank cell below the last cell in the range, enter the ROWS function. Use the range of unique values that you just copied as the argument. For example, if the range of unique values is B1:B45, then enter:
=ROWS(B1:B45)

 

[bluedragon2]

=COUNTIF(A1:A10,"apple")

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Lilliabeth]

Oh, good point, Blue. I took him to ask how to determine the number of unique entries, but maybe he meant how many of each unique. In his sample, either way = 3

 

[jimoblak]

Perhaps I should not have 3 apples.

Lilliabeth's mind-boggling formula worked well.

Thanks all.