COUNTIF( {local cells}, {number from other sheet})

S

ShadoShryke

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
 
H

Harlan Grove

ShadoShryke said:
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
Click to expand...
....

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

T. Valko

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)
 
T

T. Valko

Typo! That's what I get for copying!
Use SUMPRODUCT instead:
=SUMPRODUCT(--('Sheet 1'!$A$1:$A8, A1)
Click to expand...

The correct formula should be:

=SUMPRODUCT(--('Sheet 1'!$A$1:$A8=A1)


--
Biff
Microsoft Excel MVP


T. Valko said:
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 said:
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
Click to expand...
Click to expand...
 
S

ShadoShryke

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 said:
Typo! That's what I get for copying!
Use SUMPRODUCT instead:
=SUMPRODUCT(--('Sheet 1'!$A$1:$A8, A1)
Click to expand...

The correct formula should be:

=SUMPRODUCT(--('Sheet 1'!$A$1:$A8=A1)


--
Biff
Microsoft Excel MVP


T. Valko said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
T

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


ShadoShryke said:
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 said:
Typo! That's what I get for copying!
Use SUMPRODUCT instead:
=SUMPRODUCT(--('Sheet 1'!$A$1:$A8, A1)
Click to expand...

The correct formula should be:

=SUMPRODUCT(--('Sheet 1'!$A$1:$A8=A1)


--
Biff
Microsoft Excel MVP


T. Valko said:
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


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMIF with criteria "<>" & "=" 4
Vlookup and return sheet name also 2
Finding the weighted average of text that have been given values 1
SUMPRODUCT COUNTIF Problem 7
Excel Countif Function - Calculating Between Two Sets of Values 3
Need some help with a COUNTIF function 4
trouble with (complicated) vlookup searching another workbook 2
COUNTIF and Data Validation 0

Top