Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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, |
#2
|
|||
|
|||
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, |
#3
|
|||
|
|||
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, |
#4
|
|||
|
|||
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, |
#5
|
|||
|
|||
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, |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Entries in a Cell | Excel Worksheet Functions | |||
Counting unique entries in column A but only if specific values appear in columns B and C | Excel Worksheet Functions | |||
counting non occur entries | Excel Discussion (Misc queries) | |||
counting specified date entries | Excel Worksheet Functions | |||
counting entries between two dates? | Excel Worksheet Functions |