Counting Entries
 

Im 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 dont want to use one. Any thoughts on how
this can be done?
Thanks a bunch and best regards,

Hi!

It's not clear what exactly you want to count.

Maybe COUNTA

Biff

"Jimbo" wrote in message
...
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,

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.

"Biff" wrote:

Hi!

It's not clear what exactly you want to count.

Maybe COUNTA

Biff

"Jimbo" wrote in message
...
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,

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.

--
Regards,
Zack Barresse, aka firefytr

"Jimbo" wrote in message
...
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.

"Biff" wrote:

Hi!

It's not clear what exactly you want to count.

Maybe COUNTA

Biff

"Jimbo" wrote in message
...
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,

Hi!

Sumproduct works for me:

=SUMPRODUCT(--(A1:A10="4.1"))

Biff

"Jimbo" wrote in message
...
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.

"Biff" wrote:

Hi!

It's not clear what exactly you want to count.

Maybe COUNTA

Biff

"Jimbo" wrote in message
...
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,

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
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.

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. :)

--
Regards,
Zack Barresse, aka firefytr

"Harlan Grove" wrote in message
oups.com...
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
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.