+ Reply to Thread
Results 1 to 10 of 10

SUMPRODUCT resulting in 0

  1. #1
    Registered User
    Join Date
    05-27-2014
    Posts
    13

    SUMPRODUCT resulting in 0

    I posted the question a couple of days ago about a SUMPRODUCT formula (excel 2003) for multiple criteria including a range and you guys helped me out with this: =SUMPRODUCT(I9:I1000,(A9:A1000<=9.14)*(A9:A1000>=9.08)*(D9:D1000="Y"))
    which worked perfectly.
    BUT then I applied it to another set of data in the same worksheet :=SUMPRODUCT(U1:U1000,(A9:A1000<=12.21)*(A9:A1000>=12.15)*(Q9:Q1000="Y")) and all I get is "0" for the result. what did I do wrong?

  2. #2
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: SUMPRODUCT resulting in 0

    HI

    Sample WorkBook plz

    Punnam

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT resulting in 0

    It works for me, after a small correction.
    The ranges all must be the same size.
    :=SUMPRODUCT(U1:U1000,(A9:A1000<=12.21)*(A9:A1000>=12.15)*(Q9:Q1000="Y"))
    If I change that 1 to a 9, then it works just fine.

    However, that would have resulted in a #Value! Error, not a 0..

    If it's giving you 0, it means either
    a) there are no rows that meet all 3 criteria
    values in A9:A1000 that are between 12.15 and 12.21
    and values in Q9:Q1000 that = Y (or y)

    or
    b) the numbers in U9:U1000 are NOT really numbers, they are "Numbers stored as text"

  4. #4
    Registered User
    Join Date
    05-27-2014
    Posts
    13

    Re: SUMPRODUCT resulting in 0

    Thanks for catching the typo (U1 instead of U9), but I corrected it and it I still get a 0.

    I checked that there are numbers in the cells that meet all three criteria and there are.
    How do numbers get stored as text?

  5. #5
    Registered User
    Join Date
    05-27-2014
    Posts
    13

    Re: SUMPRODUCT resulting in 0

    Is it a cell formatting issue?

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMPRODUCT resulting in 0

    Sometimes this will convert text numbers into numeric numbers.

    Select the range U9:U1000
    Goto the Data tab (or Data menu)>Text to Columns
    Just click Finish
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    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: SUMPRODUCT resulting in 0

    Quote Originally Posted by DEER30 View Post
    How do numbers get stored as text?
    This can happen in a few different ways.

    1. values/numbers that get pulled in from external programs (ie not excel) sometimes come in as text, especially CSV
    2. any numbers that are created using LEFT/MID/RIGHT are always text that looks like a number

    Another way to convert the text to a value is to see if there is a small green triangle in the top left of the cell. if there is, and you click on the cell, you will see a small yellow diamond. highlight all the "text" numbers, scroll back to the 1st cell. Then click the yellow triangle and select "convert to number"
    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

  8. #8
    Registered User
    Join Date
    05-27-2014
    Posts
    13

    Re: SUMPRODUCT resulting in 0

    I think I figured it out! The number of cells being analyzed for each criteria was too great (9-1000). I restricted the range to only a few hundred and it seems to be working, so far. Is this a limitation of excel? Or do I have a "bad cell" in there somewhere? I really prefer to include the full column (9-1000) so that if future data entry requires the insertion of more rows, I don't have to rewrite each formula to accommodate them.

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT resulting in 0

    No, 1000 rows isn't bad at all (if it IS, you have a really old system with very little ram and processor power).

    Are you sure the formula was actually returning 0 ?
    Is the formula you posted the 'whole' formula, or did you only post a part of a larger formula?

    Can you post a sample workbook?

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  10. #10
    Registered User
    Join Date
    05-27-2014
    Posts
    13

    Re: SUMPRODUCT resulting in 0

    I'm using a system less than a year old with an i7 processor. I don't think it is the issue as I run video editing software with files several GB's in size with no problems. I will try to get a workbook posted as soon as I can.
    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to make the resulting value becomes a NAME
    By Mike Chester in forum Excel General
    Replies: 21
    Last Post: 03-23-2012, 06:23 PM
  2. Conditional sum resulting in #n/a
    By Meatwad in forum Excel General
    Replies: 3
    Last Post: 01-02-2010, 09:07 AM
  3. Vlookup resulting in REF#
    By jj72uk in forum Excel General
    Replies: 8
    Last Post: 10-06-2009, 08:05 AM
  4. [SOLVED] Comparing and resulting
    By Dale Holden in forum Excel General
    Replies: 0
    Last Post: 03-18-2006, 03:10 PM
  5. sumproduct resulting in #N/A
    By schleppy2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-28-2005, 11:05 AM

Tags for this Thread

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