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
news:[email protected]...
> 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
> news:[email protected]...
> > 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