Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF( {local cells}, {number from other sheet})
We have a group at work that is working with a CSV file that has been saved
with a .XLS extention. They are working this worksheet in Excel 2007, and are having problems with COUNTIF. The quandry is that they using the COUNTIF variables from two separate sheets, and trying to get acurate counts. The situation is as such: Sheet 1 Sheet 2 Formula A1 12450903093004001 12450903093004001 COUNTIF([Sheet 1]$A$1:$A8, A1) A2 12450903093004001 12450903093004002 COUNTIF([Sheet 1]$A$1:$A8, A2) A3 12450903093004001 A4 12450903093004001 A5 12450903093004002 A6 12450903093004002 A7 12450903093004002 A8 12450903093004002 The Results say there are 8 of each number. Not 4 of each as there really are. I was thinking it was because it was being saved as a CSV with all the numbers are "numbers as text", but I tested with others sheets and it should be a working formula. Has anyone else seen this problem and able to lend a hint? Thanks, ShadoShryke |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF( {local cells}, {number from other sheet})
"ShadoShryke" wrote...
.... Sheet 1 Sheet 2 Formula A1 *12450903093004001 12450903093004001 =COUNTIF([Sheet 1]$A$1:$A8,A1) A2 12450903093004001 12450903093004002 =COUNTIF([Sheet 1]$A$1:$A8,A2) A3 12450903093004001 A4 *12450903093004001 A5 *12450903093004002 A6 *12450903093004002 A7 *12450903093004002 A8 *12450903093004002 .... Welcome to the wonderful world of COUNTIF's (and SUMIF's) criterion 2nd argument. While online Help doesn't say so explicitly, using different criteria demonstrates empirically that COUNTIF's 2nd arguments are ALWAYS treated as TEXT STRINGS during function evaluation, BUT those text strings can be criteria matching numbers. This is the underlying cause of your users' trouble with it. The values in Sheet1 above are 17 decimal digits long. Excel can only represent these as TEXT STRINGS since Excel can handle numbers with no more than 15 decimal digits of precision. For example, had the Sheet1! A5 value above been typed in rather than loaded from a CSV file, Excel would have converted it to 12450903093004000. So I'm going to take your data above at face value, meaning I'm stipulating that your Sheet1 and Sheet2 data are actually text. The problem with COUNTIF is that it treats the text criteria 12450903093004001 as the NUMERIC comparison criterion =12450903093004000 That is, it converts the text value 12450903093004001 to a numeric value, which automatically causes Excel to lose the final digit (by which I mean it's changed to zero). This is specifically a problem with your COUNTIF criteria. The next problem with COUNTIF is that it then interprets such criteria as matching BOTH number values AND text strings which could be converted into number values AS NUMBERS. That is, it converts the values in its 1st argument's range TO NUMBERS before doing the comparison. That loses the rightmost 2 decimal digits in the Sheet1 values. What you wind up with is a count of all values in Sheet1 converted to numeric 12450903093004000 against the criterion also converted to numeric 12450903093004000, and that's why both of your original formulas return 8 rather than 4. You could confirm COUNTIF's perverse functionality using the following simpler test. Enter the following in cells in a blank worksheet. A1: 0 A2: '0 [that's a single quote first, then zero] A3: =0 A4: ="0" The formulas =COUNTIF(A1:A4,0) and =COUNTIF(A1:A4,"0") BOTH return 4. The only way to force COUNTIF to do TEXT comparisons only is to include wildcard characters in the 2nd arguments. Given your sample data above, the formulas =COUNTIF([Sheet 1]$A$1:$A8,A1&"*") =COUNTIF([Sheet 1]$A$1:$A8,A2&"*") would both have returned 4. However, if the Sheet1 range had included strings of 18 or more decimal numerals beginning with 12450903093004001 or 12450903093004002, these would be included in the revised COUNTIF formulas' results. If your users want EXACT equality counts and COULD HAVE text values of varying lengths, then they CAN'T use COUNTIF because COUNTIF simply isn't reliable when working with long strings which could be converted into numbers. One alternative that would work is =SUMPRODUCT(--(Sheet1!$A$1:$A$8=A1)) which NEVER converts values between text and numeric and NEVER considers text and numeric values to be equal. With your text data, this SUMPRODUCT formula would perform ONLY text comparisons, so it wouldn't suffer from numeric conversion with loss of precision. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF( {local cells}, {number from other sheet})
The problem is that COUNTIF evaluates TEXT numbers and NUMERIC numbers as
being equal. Since Excel only evaluates numbers to 15 digits of precision what's happening is the digits after the 15th are being converted to 0s in the calculation and when that happens they're all the same so you get result of 8. 12450903093004001 evaluates as 12450903093004000 12450903093004002 evaluates as 12450903093004000 Use SUMPRODUCT instead: =SUMPRODUCT(--('Sheet 1'!$A$1:$A8, A1) -- Biff Microsoft Excel MVP "ShadoShryke" wrote in message ... We have a group at work that is working with a CSV file that has been saved with a .XLS extention. They are working this worksheet in Excel 2007, and are having problems with COUNTIF. The quandry is that they using the COUNTIF variables from two separate sheets, and trying to get acurate counts. The situation is as such: Sheet 1 Sheet 2 Formula A1 12450903093004001 12450903093004001 COUNTIF([Sheet 1]$A$1:$A8, A1) A2 12450903093004001 12450903093004002 COUNTIF([Sheet 1]$A$1:$A8, A2) A3 12450903093004001 A4 12450903093004001 A5 12450903093004002 A6 12450903093004002 A7 12450903093004002 A8 12450903093004002 The Results say there are 8 of each number. Not 4 of each as there really are. I was thinking it was because it was being saved as a CSV with all the numbers are "numbers as text", but I tested with others sheets and it should be a working formula. Has anyone else seen this problem and able to lend a hint? Thanks, ShadoShryke |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF( {local cells}, {number from other sheet})
"T. Valko" wrote...
.... Use SUMPRODUCT instead: =SUMPRODUCT(--('Sheet 1'!$A$1:$A8, A1) .... Comma? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF( {local cells}, {number from other sheet})
Typo! That's what I get for copying!
Use SUMPRODUCT instead: =SUMPRODUCT(--('Sheet 1'!$A$1:$A8, A1) The correct formula should be: =SUMPRODUCT(--('Sheet 1'!$A$1:$A8=A1) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... The problem is that COUNTIF evaluates TEXT numbers and NUMERIC numbers as being equal. Since Excel only evaluates numbers to 15 digits of precision what's happening is the digits after the 15th are being converted to 0s in the calculation and when that happens they're all the same so you get result of 8. 12450903093004001 evaluates as 12450903093004000 12450903093004002 evaluates as 12450903093004000 Use SUMPRODUCT instead: =SUMPRODUCT(--('Sheet 1'!$A$1:$A8, A1) -- Biff Microsoft Excel MVP "ShadoShryke" wrote in message ... We have a group at work that is working with a CSV file that has been saved with a .XLS extention. They are working this worksheet in Excel 2007, and are having problems with COUNTIF. The quandry is that they using the COUNTIF variables from two separate sheets, and trying to get acurate counts. The situation is as such: Sheet 1 Sheet 2 Formula A1 12450903093004001 12450903093004001 COUNTIF([Sheet 1]$A$1:$A8, A1) A2 12450903093004001 12450903093004002 COUNTIF([Sheet 1]$A$1:$A8, A2) A3 12450903093004001 A4 12450903093004001 A5 12450903093004002 A6 12450903093004002 A7 12450903093004002 A8 12450903093004002 The Results say there are 8 of each number. Not 4 of each as there really are. I was thinking it was because it was being saved as a CSV with all the numbers are "numbers as text", but I tested with others sheets and it should be a working formula. Has anyone else seen this problem and able to lend a hint? Thanks, ShadoShryke |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF( {local cells}, {number from other sheet})
Thanks!! I gave them to script and showed them how to work it for their
purpose and it does what was needed. ShadoShryke (aka James Walker) __________________________________________________ ___ "T. Valko" wrote in message ... Typo! That's what I get for copying! Use SUMPRODUCT instead: =SUMPRODUCT(--('Sheet 1'!$A$1:$A8, A1) The correct formula should be: =SUMPRODUCT(--('Sheet 1'!$A$1:$A8=A1) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... The problem is that COUNTIF evaluates TEXT numbers and NUMERIC numbers as being equal. Since Excel only evaluates numbers to 15 digits of precision what's happening is the digits after the 15th are being converted to 0s in the calculation and when that happens they're all the same so you get result of 8. 12450903093004001 evaluates as 12450903093004000 12450903093004002 evaluates as 12450903093004000 Use SUMPRODUCT instead: =SUMPRODUCT(--('Sheet 1'!$A$1:$A8, A1) -- Biff Microsoft Excel MVP "ShadoShryke" wrote in message ... We have a group at work that is working with a CSV file that has been saved with a .XLS extention. They are working this worksheet in Excel 2007, and are having problems with COUNTIF. The quandry is that they using the COUNTIF variables from two separate sheets, and trying to get acurate counts. The situation is as such: Sheet 1 Sheet 2 Formula A1 12450903093004001 12450903093004001 COUNTIF([Sheet 1]$A$1:$A8, A1) A2 12450903093004001 12450903093004002 COUNTIF([Sheet 1]$A$1:$A8, A2) A3 12450903093004001 A4 12450903093004001 A5 12450903093004002 A6 12450903093004002 A7 12450903093004002 A8 12450903093004002 The Results say there are 8 of each number. Not 4 of each as there really are. I was thinking it was because it was being saved as a CSV with all the numbers are "numbers as text", but I tested with others sheets and it should be a working formula. Has anyone else seen this problem and able to lend a hint? Thanks, ShadoShryke |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF( {local cells}, {number from other sheet})
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "ShadoShryke" wrote in message ... Thanks!! I gave them to script and showed them how to work it for their purpose and it does what was needed. ShadoShryke (aka James Walker) __________________________________________________ ___ "T. Valko" wrote in message ... Typo! That's what I get for copying! Use SUMPRODUCT instead: =SUMPRODUCT(--('Sheet 1'!$A$1:$A8, A1) The correct formula should be: =SUMPRODUCT(--('Sheet 1'!$A$1:$A8=A1) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... The problem is that COUNTIF evaluates TEXT numbers and NUMERIC numbers as being equal. Since Excel only evaluates numbers to 15 digits of precision what's happening is the digits after the 15th are being converted to 0s in the calculation and when that happens they're all the same so you get result of 8. 12450903093004001 evaluates as 12450903093004000 12450903093004002 evaluates as 12450903093004000 Use SUMPRODUCT instead: =SUMPRODUCT(--('Sheet 1'!$A$1:$A8, A1) -- Biff Microsoft Excel MVP "ShadoShryke" wrote in message ... We have a group at work that is working with a CSV file that has been saved with a .XLS extention. They are working this worksheet in Excel 2007, and are having problems with COUNTIF. The quandry is that they using the COUNTIF variables from two separate sheets, and trying to get acurate counts. The situation is as such: Sheet 1 Sheet 2 Formula A1 12450903093004001 12450903093004001 COUNTIF([Sheet 1]$A$1:$A8, A1) A2 12450903093004001 12450903093004002 COUNTIF([Sheet 1]$A$1:$A8, A2) A3 12450903093004001 A4 12450903093004001 A5 12450903093004002 A6 12450903093004002 A7 12450903093004002 A8 12450903093004002 The Results say there are 8 of each number. Not 4 of each as there really are. I was thinking it was because it was being saved as a CSV with all the numbers are "numbers as text", but I tested with others sheets and it should be a working formula. Has anyone else seen this problem and able to lend a hint? Thanks, ShadoShryke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif with two conditions and cells one a number and other text | Excel Discussion (Misc queries) | |||
Select cells according to row number in different sheet | Excel Discussion (Misc queries) | |||
Finding Number of cells in a column of other sheet having a specific word in them | New Users to Excel | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
In COUNTIF function, how do I count the number of cells which con. | Excel Worksheet Functions |