+ Reply to Thread
Results 1 to 4 of 4

Sumproduct or Sumifs

  1. #1
    Registered User
    Join Date
    09-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Sumproduct or Sumifs

    Hi,

    Refer attached spreadsheet, cell H148.

    Sumproduct formula. This formula works if I replace the range name Job_No with the actual range ie C9:C141.
    However if I replace the range with the range name I get #NA error.
    What is wrong?

    I am also happy to use a SUMIF or SUMIFS formula for multiple criteria but I get #VALUE errors.

    Hoping someone can help...

    Regards,

    Chris.
    Attached Files Attached Files
    Last edited by adamsc57; 01-15-2015 at 09:13 PM.

  2. #2
    Registered User
    Join Date
    07-26-2006
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Sumproduct or Sumifs

    I just took a quick look. It may be that your Job No goes down to row 141, whereas your other ranges only go to 137.

  3. #3
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: Sumproduct or Sumifs

    I can tell you it is because your Named references are not of equal area - Open up Name Manager DATA>Name Manager and fix your reference areas. Also delete out the old "Duplicate names" that have REF errors because they were deleted and recreated (Copy pasted likely) over.

    Careful when adding/removing rows if using named references - Once you make the range equal 9:140 in your three references a sumifs will have no problems.

    EDIT - Both SumProduct and SUMIFS should be fine after correcting
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Cheers
    Last edited by ELeGault; 01-15-2015 at 08:39 PM.
    -If you think you are done, Start over - ELeGault

  4. #4
    Registered User
    Join Date
    09-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Sumproduct or Sumifs

    Problem solved.

    Thank you both Cardan and ELeGault.

    Easy when you know how.

    Thanks for your effort in looking into this.

    Regards,

    Chris.

+ 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] SUMIFS or SUMPRODUCT???
    By PERE in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-25-2013, 11:52 AM
  2. [SOLVED] SUMIFS to SUMPRODUCT
    By plsm5882 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-31-2013, 05:30 PM
  3. [SOLVED] SUMIFS to SUMPRODUCT
    By JungleJme in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-13-2012, 12:14 PM
  4. [SOLVED] Sumifs ,or SUMPRODUCT
    By jamilm in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-11-2012, 06:20 PM
  5. Using SUMIFS or SUMPRODUCT
    By Ovenmittenburn in forum Excel General
    Replies: 0
    Last Post: 08-03-2011, 01:55 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