Counting Entries

J

Jimbo

I’m trying to count use of paragraphs and in a spreadsheet there is a column
with entries of the form 4.1, 4.2, 4.1.1, 4.10, 4.10.1 a), 5.1, 5.10, 5.10.6
and so on, but it seems like the functions I tried (COUNTIF and SUMPRODUCT,
so far) are not able to differentiate between 4.1 and 4.10. These are all
formatted as text in the spreadsheet. I tried this using a pivot table,
which seems to do the job but I don’t want to use one. Any thoughts on how
this can be done?
Thanks a bunch and best regards,
 
J

Jimbo

I'm counting the number of entries of "4.1", "4.2", "4.10", etc. appear in
the spreadsheet column. When I use COUNTIF it picks-up entries for 4.10 when
I enter "4.1" for example.

Hope this helps and thanks.
 
Z

zackb

Because they are the same - to Excel. You'd need to change your values to
text to count such instances. This is because the only reason you see the 0
on the end is in lieu of the cell formatting, which is basically a mask.
 
H

Harlan Grove

zackb wrote...
Because they are the same - to Excel. You'd need to change your values to
text to count such instances. This is because the only reason you see the 0
on the end is in lieu of the cell formatting, which is basically a
Click to expand...
mask.
....

You've got part of the truth, but the whole truth is UGLY. I enter the
following *EXACTLY* into A1:A3.

'4.1
'4.1
'4.10

So the single quotes denote ad hoc text entry. Then in another cell I
enter the formula

=COUNTIF(A1:A3,A1)

What does Excel return? 3! COUNTIF is FUBAR! When its second argument
is numeric, so either an actual number or a valid text representation
of a number, COUNTIF treats all cells in its first argument as numeric.
No way to change these semantics.
 
Z

zackb

Hmm, interesting. It's funny how the native function (CountIf) will reduce
everything to numerical constants, whereas a SumProduct will test each
occurance as textual with no conversion. Boy that could get complex! LOL!

Thanks for that Harlan. Appreciate it. :)
 
Top