+ Reply to Thread
Results 1 to 6 of 6

SUMPRODUCT not working

  1. #1
    Registered User
    Join Date
    08-08-2007
    Posts
    80

    SUMPRODUCT not working

    I found this awesome guide online for some dynamic formula's that I want to use:

    https://www.youtube.com/watch?v=NbP-dHau9-I

    His initial setup though requires a SUMPRODUCT formula to breakdown the initial items to. I'm having issues with my specific column area.

    I am trying to pull two different pieces of information from these SUMPRODUCT Items:

    1) Column A says "Y" while Column B is 0

    2) Column A is blank but Column B has a number

    I do not need Column A that say "Y" and column B with a number

    Below is my first crack at

    =SUMPRODUCT('Product 1 Roll-up'!A2:A99="", 'Product 1 Roll-up'!B2:B99>0)

    I do have columns within A2:A99 that are blank and have numbers greater than 99. Its just not coming through, keeps coming in as 0.

    If you need me to build a spreadsheet for reference I have no problem doing so, just hoping maybe its my formatting or something.

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: SUMPRODUCT not working

    yes could you supply a sample?

  3. #3
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: SUMPRODUCT not working

    If you are using sum product

    You need both columns to be numbers.


    So with your formula. Column A will be 1 if the entry is "", possibly it could also return True, if you multiplied by 1 it would definately return 1

    so try

    =SUMPRODUCT(('Product 1 Roll-up'!A2:A99="")*1, 'Product 1 Roll-up'!B2:B99>0)

    Enter the formula using Ctrl Shift Enter.


    or do as zbor says:
    Last edited by mehmetcik; 10-10-2014 at 02:45 PM.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: SUMPRODUCT not working

    mehmetcik,
    it won't work.
    You need to convert all TRUE/FALSE into numbers either by product of ranges

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Either by converting each range separately

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-08-2007
    Posts
    80

    Re: SUMPRODUCT not working

    BAH!~

    Zbor figured it out. Why are the little things always my downfall!!?!?!

    thank you sir.

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMPRODUCT not working

    Quote Originally Posted by Karroog View Post
    I found this awesome guide online for some dynamic formula's that I want to use
    Which version of Excel are you using? If you have Excel 2007 or later then for "multi-conditional counting" (which is what you seem to be doing here) COUNTIFS (with an "S" on the end) is normally a better, more efficient, option, e.g. If you want a count of rows that are blank in column A but a number >0 in column B you can do that with this formula

    =COUNTIFS('Product 1 Roll-up'!A2:A99,"",'Product 1 Roll-up'!B2:B99,">0")
    Audere est facere

+ 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. [SOLVED] Sumproduct not working
    By nicci113 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-02-2014, 08:26 AM
  2. [SOLVED] Sumproduct not working
    By Chetansuri in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-03-2014, 01:32 AM
  3. SUMPRODUCT not working
    By AnnaV in forum Excel General
    Replies: 3
    Last Post: 05-10-2011, 11:01 AM
  4. [SOLVED] Sumproduct Not Working
    By carl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-15-2006, 02:15 PM
  5. SUMPRODUCT Not Working
    By Scott in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-09-2005, 10:50 AM

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