+ Reply to Thread
Results 1 to 7 of 7

problems with sumif and countif

  1. #1
    Simon Shaw
    Guest

    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

  2. #2
    Anne Troy
    Guest

    Re: problems with sumif and countif

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

    www.OfficeArticles.com


    "Simon Shaw" <simonATsimonstoolsDOTcom> wrote in message
    news:9471AC5F-A9AA-43CB-86B4-B08B088CA3CF@microsoft.com...
    > 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




  3. #3
    Simon Shaw
    Guest

    Re: problems with sumif and countif

    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:9471AC5F-A9AA-43CB-86B4-B08B088CA3CF@microsoft.com...
    > > 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

    >
    >
    >


  4. #4
    Simon Shaw
    Guest

    RE: problems with sumif and countif

    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


  5. #5
    Dave Peterson
    Guest

    Re: problems with sumif and countif

    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

  6. #6
    Simon Shaw
    Guest

    Re: problems with sumif and countif

    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
    >


  7. #7
    Dave Peterson
    Guest

    Re: problems with sumif and countif

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1