+ Reply to Thread
Results 1 to 8 of 8

SUMIF returning 0

  1. 04-25-2014, 11:00 AM #1
    TPDave
    TPDave is offline
    Forum Contributor
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    191

    SUMIF returning 0

    Hi All,

    SUMIF is returning "0" and I can't figure out why, my data isn't corrupt, the numbers are numbers, the ranges are the same size etc. and I don't know what I'm doing wrong!

    The sheet is a bit sensitive and I don't want to edit it too much as I'm worried I might lose whatever the problem is, can someone volunteer to have the sheet PM'd to them?

    Thanks
    Last edited by TPDave; 04-25-2014 at 11:35 AM.
    Reply With QuoteRegister To Reply
  2. 04-25-2014, 11:12 AM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,308

    Re: SUMIF returning 0

    It's either your formula or your data, or both. Unless you are prepared to share something with us, it is going to be nigh on impossible to help you.

    I don't think you can attach files to PMs. But the thread should be carried out on the board, not behind the scens.

    You don't have to provide much detail, just some sample desensitized data.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 04-25-2014, 11:13 AM #3
    satabp
    satabp is offline
    Forum Contributor
    Join Date
    12-09-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    171

    Re: SUMIF returning 0

    Hi,

    If the criteria is not found in our range then you will get "0" as output.

    Please check.

    Thanks,
    Bhanu
    Like my answer, then click * below
    Reply With QuoteRegister To Reply
  4. 04-25-2014, 11:14 AM #4
    satabp
    satabp is offline
    Forum Contributor
    Join Date
    12-09-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    171

    Re: SUMIF returning 0

    Hi,

    If the criteria is not found in the range then you will get "0" as output.

    Please check.

    Thanks,
    Bhanu
    Reply With QuoteRegister To Reply
  5. 04-25-2014, 11:16 AM #5
    TPDave
    TPDave is offline
    Forum Contributor
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: SUMIF returning 0

    I've had a bit of a hack away at it and have something more suitable attached!

    The SUMIF is in cell B3 on sheet 3 and is referencing two named ranges on sheet 2

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 04-25-2014, 11:32 AM #6
    TPDave
    TPDave is offline
    Forum Contributor
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: SUMIF returning 0

    So I've been playing a bit and it looks like although the numbers in the range to be added can be multiplied etc they were not stored as numbers. I copied them out of the array and multiplied by 1 and now the formula works, I guess the question is how to I make the formula work if I want the data to be "live" and not copied out of the array in sheet 2?

    Thanks
    Reply With QuoteRegister To Reply
  7. 04-25-2014, 11:34 AM #7
    TPDave
    TPDave is offline
    Forum Contributor
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: SUMIF returning 0

    Go it! I just had to put a multiplication in the array formula and it became a number capable of being summed.
    Reply With QuoteRegister To Reply
  8. 04-25-2014, 11:38 AM #8
    satabp
    satabp is offline
    Forum Contributor
    Join Date
    12-09-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    171

    Re: [SOLVED] SUMIF returning 0

    Yeah...

    like this on sheet2 column I

    =IF(A2="","",(LEFT(A2,6)*1))
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] SUMIF formula returning a 0 value
    By cf7046 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-21-2013, 01:08 AM
  2. Sumif function not returning text
    By slkelly13 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-02-2013, 06:01 PM
  3. SUMIF or IF without returning to false or zero value
    By tmkkoservo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-03-2012, 09:23 PM
  4. SUMIF - not returning expected result
    By Paul Sheppard in forum Excel General
    Replies: 4
    Last Post: 08-16-2011, 11:43 AM
  5. Need function help on returning a value from lookup or Sumif
    By spanky27 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2008, 09:20 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1