problems with sumif and countif
 

Hi,

i have a column that joins a series of other columns to creat a unque key
for a table data I have in a sheet. when I then use sumif or countif against
the concatenated column it says there is more than one the same and the sumif
produces the same total for a number of lines. visually looking at the unique
key column, I can clearly see they are different.

ideas?

Simon Shaw

Are you sure the formula is updating?
*******************
~Anne Troy

www.OfficeArticles.com


"Simon Shaw" <simonATsimonstoolsDOTcom wrote in message
...
Hi,

i have a column that joins a series of other columns to creat a unque key
for a table data I have in a sheet. when I then use sumif or countif
against
the concatenated column it says there is more than one the same and the
sumif
produces the same total for a number of lines. visually looking at the
unique
key column, I can clearly see they are different.

ideas?

Simon Shaw

yes the calculation is updating... I hit F9 to be sure.

I also have used the advanced filter for unque values and the two lists are
the same length indicating it is a true unique key.

"Anne Troy" wrote:

Are you sure the formula is updating?
*******************
~Anne Troy

www.OfficeArticles.com


"Simon Shaw" <simonATsimonstoolsDOTcom wrote in message
...
Hi,

i have a column that joins a series of other columns to creat a unque key
for a table data I have in a sheet. when I then use sumif or countif
against
the concatenated column it says there is more than one the same and the
sumif
produces the same total for a number of lines. visually looking at the
unique
key column, I can clearly see they are different.

ideas?

Simon Shaw

The concatenated column is 16 characters long, it appears to only be using 14
characters... is this possible?

"Simon Shaw" wrote:

Hi,

i have a column that joins a series of other columns to creat a unque key
for a table data I have in a sheet. when I then use sumif or countif against
the concatenated column it says there is more than one the same and the sumif
produces the same total for a number of lines. visually looking at the unique
key column, I can clearly see they are different.

ideas?

Simon Shaw

If I put this in A1:A5,

simon*
simon2
simon3
simon4
simon5

And then this in A1:
=countif(a1:a5,a1)

I get 5.

Does your data contain wildcards (asterisk/question mark, */?)?

(If I understood the question???)

Simon Shaw wrote:

The concatenated column is 16 characters long, it appears to only be using 14
characters... is this possible?

"Simon Shaw" wrote:

Hi,

i have a column that joins a series of other columns to creat a unque key
for a table data I have in a sheet. when I then use sumif or countif against
the concatenated column it says there is more than one the same and the sumif
produces the same total for a number of lines. visually looking at the unique
key column, I can clearly see they are different.

ideas?

Simon Shaw

--

Dave Peterson

no, no special characters, just numbers.

when I reduced the unique key to 13 characters it worked for both countif
and sumif... is there a limit to the length of the match criteria?

simon


"Dave Peterson" wrote:

If I put this in A1:A5,

simon*
simon2
simon3
simon4
simon5

And then this in A1:
=countif(a1:a5,a1)

I get 5.

Does your data contain wildcards (asterisk/question mark, */?)?

(If I understood the question???)

Simon Shaw wrote:

The concatenated column is 16 characters long, it appears to only be using 14
characters... is this possible?

"Simon Shaw" wrote:

Hi,

i have a column that joins a series of other columns to creat a unque key
for a table data I have in a sheet. when I then use sumif or countif against
the concatenated column it says there is more than one the same and the sumif
produces the same total for a number of lines. visually looking at the unique
key column, I can clearly see they are different.

ideas?

Simon Shaw

--

Dave Peterson

You may want to post your formula and a snippet of data that makes the formula
look incorrect.

Simon Shaw wrote:

no, no special characters, just numbers.

when I reduced the unique key to 13 characters it worked for both countif
and sumif... is there a limit to the length of the match criteria?

simon

"Dave Peterson" wrote:

If I put this in A1:A5,

simon*
simon2
simon3
simon4
simon5

And then this in A1:
=countif(a1:a5,a1)

I get 5.

Does your data contain wildcards (asterisk/question mark, */?)?

(If I understood the question???)

Simon Shaw wrote:

The concatenated column is 16 characters long, it appears to only be using 14
characters... is this possible?

"Simon Shaw" wrote:

Hi,

i have a column that joins a series of other columns to creat a unque key
for a table data I have in a sheet. when I then use sumif or countif against
the concatenated column it says there is more than one the same and the sumif
produces the same total for a number of lines. visually looking at the unique
key column, I can clearly see they are different.

ideas?

Simon Shaw

--

Dave Peterson


--

Dave Peterson