+ Reply to Thread
Results 1 to 7 of 7

Sumproduct Range Requirements

  1. #1
    Forum Contributor
    Join Date
    08-05-2009
    Location
    Colorado Springs, CO
    MS-Off Ver
    Office 365
    Posts
    160

    Sumproduct Range Requirements

    Please Login or Register  to view this content.
    =SUMPRODUCT((B1773:B1795=L1772)*(C1773:C1795))
    The above formula works fine as long as the first row in the range is the same as the first row with data. If I replace row 1773 references with row 1772, then the formula fails (#VALUE). I've tried this on another spreadsheet with some made up data, and I could not duplicate the problem.

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Sumproduct Range Requirements

    Hi Atom- I'm guessing you have text in row C1772; Excel can't multiply text. Try using SUMIF instead:
    Please Login or Register  to view this content.
    It's probably a little faster anyway.

    Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

  3. #3
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Sumproduct Range Requirements

    Actually, I just realized there IS a way to use SUMPRODUCT, if you're really "married" to it:
    Please Login or Register  to view this content.
    Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

  4. #4
    Forum Contributor
    Join Date
    08-05-2009
    Location
    Colorado Springs, CO
    MS-Off Ver
    Office 365
    Posts
    160

    Re: Sumproduct Range Requirements

    I use sumif quite a lot, but there are some situations where it doesn't get the job done. However, it does appear to work here. The sumproduct also works if the range specifications are just right. I'd like to understand what's causing that problem. There is no text in the cell you referred to above.

  5. #5
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Sumproduct Range Requirements

    OK... and no formula returning "" or " "? No single ' or space? All of these -including the "empty" string - would read as text and cause the multiplication to fail. If feasible, hold down the CTRL key, select every cell in your range that LOOKS blank, and press Delete.
    Last edited by leelnich; 08-24-2017 at 03:56 AM.

  6. #6
    Forum Contributor
    Join Date
    08-05-2009
    Location
    Colorado Springs, CO
    MS-Off Ver
    Office 365
    Posts
    160

    Re: Sumproduct Range Requirements

    You're right- there was some text. Thanks for helping.

  7. #7
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Sumproduct Range Requirements

    Cool, glad you figured it out! If that answers your question, please go to Thread Tools up top and mark your thread as SOLVED. Thanks -Lee
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

+ 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. Replies: 2
    Last Post: 01-20-2017, 04:27 PM
  2. [SOLVED] Sumproduct using range as criteria to compare another range
    By lukihnio in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-25-2016, 11:07 AM
  3. Replies: 1
    Last Post: 12-17-2015, 11:22 AM
  4. Replies: 8
    Last Post: 05-14-2012, 02:44 PM
  5. SumProduct Formula Count Entries in a range if another range is = 1
    By realniceguy5000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-15-2011, 02:13 PM
  6. Sum if satifies two requirements help
    By aroyale15 in forum Excel General
    Replies: 5
    Last Post: 10-20-2009, 12:24 AM
  7. Name Range with three requirements
    By Carole O in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2005, 04:06 PM

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