+ Reply to Thread
Results 1 to 7 of 7

using Sumif Problems

  1. #1
    Anat
    Guest

    using Sumif Problems

    I am using the sumif formula and noticed that it doesn't pick up all the
    data. I tried using trim to clear out all spaces in the criteria but still
    no luck. It works when I go and write over the criteria any ideas?

    Thanks,
    Anat

  2. #2
    Nick Hodge
    Guest

    re: using Sumif Problems

    Anat

    I suspect the 'criteria' is seen as text. If these are 'numbers' then
    enter a 1 in a spare cell and copy it.

    Highlight your 'numbers' and take edit>Paste special...>Values + Multiply.
    This should kick Excel into recognising them

    --
    HTH
    Nick Hodge
    Microsoft MVP - Excel
    Southampton, England
    nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


    "Anat" <Anat@discussions.microsoft.com> wrote in message
    news:186D3E4F-BCE9-4949-8476-1E5E5FC4865F@microsoft.com...
    >I am using the sumif formula and noticed that it doesn't pick up all the
    > data. I tried using trim to clear out all spaces in the criteria but
    > still
    > no luck. It works when I go and write over the criteria any ideas?
    >
    > Thanks,
    > Anat




  3. #3
    Anat
    Guest

    re: using Sumif Problems

    Nick,

    The criteria is a text not a number.

    ANat

    "Nick Hodge" wrote:

    > Anat
    >
    > I suspect the 'criteria' is seen as text. If these are 'numbers' then
    > enter a 1 in a spare cell and copy it.
    >
    > Highlight your 'numbers' and take edit>Paste special...>Values + Multiply.
    > This should kick Excel into recognising them
    >
    > --
    > HTH
    > Nick Hodge
    > Microsoft MVP - Excel
    > Southampton, England
    > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
    >
    >
    > "Anat" <Anat@discussions.microsoft.com> wrote in message
    > news:186D3E4F-BCE9-4949-8476-1E5E5FC4865F@microsoft.com...
    > >I am using the sumif formula and noticed that it doesn't pick up all the
    > > data. I tried using trim to clear out all spaces in the criteria but
    > > still
    > > no luck. It works when I go and write over the criteria any ideas?
    > >
    > > Thanks,
    > > Anat

    >
    >
    >


  4. #4
    Harlan Grove
    Guest

    re: using Sumif Problems

    Anat wrote...
    >The criteria is a text not a number.

    ....

    What's your actual formula?


  5. #5
    Anat
    Guest

    re: using Sumif Problems

    =SUMIF('P&L Cat(YTD May) '!$X$10:$X$152,"P",'P&L Cat(YTD May) '!$C$10:$C$152)

    Anat

    "Harlan Grove" wrote:

    > Anat wrote...
    > >The criteria is a text not a number.

    > ....
    >
    > What's your actual formula?
    >
    >


  6. #6
    bj
    Guest

    re: using Sumif Problems

    temporarily add a helper column Y in your P&L... sheet
    enter in Y10
    =if(X10="P",1,"")
    copy down to X152
    look in column X for "P"s which do not have a 1 next to them. You should be
    able to identify what is the difference modify your equation in column Y
    until you get a perfect correlation.
    "Anat" wrote:

    > =SUMIF('P&L Cat(YTD May) '!$X$10:$X$152,"P",'P&L Cat(YTD May) '!$C$10:$C$152)
    >
    > Anat
    >
    > "Harlan Grove" wrote:
    >
    > > Anat wrote...
    > > >The criteria is a text not a number.

    > > ....
    > >
    > > What's your actual formula?
    > >
    > >


  7. #7
    Anat
    Guest

    re: using Sumif Problems

    Thanks I'll try that.

    Anat

    "bj" wrote:

    > temporarily add a helper column Y in your P&L... sheet
    > enter in Y10
    > =if(X10="P",1,"")
    > copy down to X152
    > look in column X for "P"s which do not have a 1 next to them. You should be
    > able to identify what is the difference modify your equation in column Y
    > until you get a perfect correlation.
    > "Anat" wrote:
    >
    > > =SUMIF('P&L Cat(YTD May) '!$X$10:$X$152,"P",'P&L Cat(YTD May) '!$C$10:$C$152)
    > >
    > > Anat
    > >
    > > "Harlan Grove" wrote:
    > >
    > > > Anat wrote...
    > > > >The criteria is a text not a number.
    > > > ....
    > > >
    > > > What's your actual formula?
    > > >
    > > >


+ 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