+ Reply to Thread
Results 1 to 10 of 10

SUMIF formula returning a 0 value

  1. #1
    Registered User
    Join Date
    03-05-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2003
    Posts
    27

    SUMIF formula returning a 0 value

    Hello:
    I have a question about using the SUMIF formula.

    I have a column that I would like to have data summed based on the criteria selected by the user. The column that has that data, has formulas calculating a total to get the dollar amount. I'm not sure if the SUMIF function is not working correctly based on other formulas in the cells.

    Heres what I have - shortened version:
    Cell B9 Name Cell L8 Cost
    Cell B10 Jim Cell L9 $1500
    Cell B11 Joe Cell L10 $2000
    Cell B12 Jim Cell L11 $2500

    In Cell B1000, I have the name Jim.
    In Cell C1000, I would like the sum of all costs from column L associated with Jim to be totaled.
    The formula I have is =SUMIF(B10:B12,"B1000",L9:L12)
    It is returning a 0. I'm not sure if this is due to calculation formulas in column L.

    How can I fix this to make it work? Any help would be appreciated.

    Thanks!
    Last edited by cf7046; 04-21-2013 at 01:17 AM. Reason: Solved

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: SUMIF formula returning a 0 value

    excel doesnt see formulas, it only sees the answer, so having formulas in the range wont affect anything. It sounds more like your "values" are not actually values/numbers, but text that looks like a number.

    Test with =isnumber(cell_ref) FALSE indicates text.

    Check this 1st and then let me know?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-05-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: SUMIF formula returning a 0 value

    Quote Originally Posted by FDibbins View Post
    excel doesnt see formulas, it only sees the answer, so having formulas in the range wont affect anything. It sounds more like your "values" are not actually values/numbers, but text that looks like a number.

    Test with =isnumber(cell_ref) FALSE indicates text.

    Check this 1st and then let me know?
    Thank you for your response. It did come up as FALSE.

  4. #4
    Registered User
    Join Date
    03-05-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: SUMIF formula returning a 0 value

    Quote Originally Posted by cf7046 View Post
    Thank you for your response. It did come up as FALSE.
    Here is a corrected table.
    Cell B8 Name Cell L8 Cost
    Cell B9 Jim Cell L9 $1500
    Cell B10 Joe Cell L10 $2000
    Cell B11 Jim Cell L11 $2500


    Sorry for the mistake. I am using the formula =SUMIF(B9:B11,B1000,L9:L11)
    The formula is showing a 0 value.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: SUMIF formula returning a 0 value

    It doesnt matter what you show here, it only matters what you have in your workbook. If the isnumber() is showing FALSE, that means your "number" is text, and will not be treated a number than can be used in your calc - you 1st need to convert it back to a number.

    What is the formula that you are using that returns the "number"?

    It may be easier to help if I can see what you are working with. If the file does not contain sensitive info, can you up,load it here please?
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  6. #6
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: SUMIF formula returning a 0 value

    Hello,

    As FDibbins suggested, your value is probably text instead of number, something like ="$1500" that looks no different than $1500
    In any case, you can try this formula instead
    Please Login or Register  to view this content.
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  7. #7
    Registered User
    Join Date
    03-05-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: SUMIF formula returning a 0 value

    Quote Originally Posted by Lemice View Post
    Hello,

    As FDibbins suggested, your value is probably text instead of number, something like ="$1500" that looks no different than $1500
    In any case, you can try this formula instead
    Please Login or Register  to view this content.
    Thank you for your response. I did double check and the column is formatted as currency. I tried your suggestion, and it still returned a 0 value.

  8. #8
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: SUMIF formula returning a 0 value

    Did you check if B1000 is "Jim" and you can manually Ctrl-F that "Jim" through the entire worksheet?
    Here is a sample.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-05-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: SUMIF formula returning a 0 value

    Quote Originally Posted by cf7046 View Post
    Thank you for your response. I did double check and the column is formatted as currency. I tried your suggestion, and it still returned a 0 value.
    Thank you for your help. I was able to figure out a way to make it work by using this formula. =SUMPRODUCT((TRIM(B9:B11)=B1000)*L9:L11)

  10. #10
    Registered User
    Join Date
    03-05-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: SUMIF formula returning a 0 value

    Quote Originally Posted by FDibbins View Post
    It doesnt matter what you show here, it only matters what you have in your workbook. If the isnumber() is showing FALSE, that means your "number" is text, and will not be treated a number than can be used in your calc - you 1st need to convert it back to a number.

    What is the formula that you are using that returns the "number"?

    It may be easier to help if I can see what you are working with. If the file does not contain sensitive info, can you up,load it here please?
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Thank you for your help. I was able to figure out a way to make it work by using this formula. =SUMPRODUCT((TRIM(B9:B11)=B1000)*L9:L11)

+ 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