+ Reply to Thread
Results 1 to 9 of 9

Sumproduct - #value error

  1. #1
    Registered User
    Join Date
    03-08-2015
    Location
    cleveland, ohio
    MS-Off Ver
    2013
    Posts
    48

    Sumproduct - #value error

    i am currently trying to use sum prouduct in the place of sumifs in a spreadsheet i am working on.I am trying to sum column E if column B contains any of the names in my named range and column a matches the month. However, i am getting a #value error and cannot figure out where i am going wrong. I have checked column E to make sure there is no text and unless it's a problem with my named range, i don't know what else could be causing the issue. Any help or feedback would be greatly appreciated as i am thoroughly stumped.


    Column A = the month - in text format
    Column B = Names
    Column E= hours worked - formatted in numerical
    Named Range - List of Names (names separated out by department)

    Here is my formula
    =sumproduct((B2:b66=Department)*(A2:a66="MAY")*(E2:E66))

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

    Re: Sumproduct - #value error

    If there is any text in the sum range you'll get the #VALUE! error using that particular syntax.

    Try it like this...

    =SUMPRODUCT--(B2:B66=Department),--(A2:A66="MAY"),E2:E66)

    Also suspicious, what does Department refer to?

    You might even be able to use the more efficient SUMIFS function.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

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

    Re: Sumproduct - #value error

    if column B contains any of the names in my named range
    So, I guess that means Department is a named range made up of more than 1 cell?

    If so, then try this...

    =SUMPRODUCT(--ISNUMBER(MATCH(B2:B66,Department,0)),--(A2:A66="MAY"),E2:E66)

  4. #4
    Registered User
    Join Date
    03-08-2015
    Location
    cleveland, ohio
    MS-Off Ver
    2013
    Posts
    48

    Re: Sumproduct - #value error

    Department is a named range that is a list of names that need to match the names in column B. Neither of the previous suggestions have worked.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    35,756

    Re: Sumproduct - #value error

    Can you post your Excel sheet, so that we can see what's going on more clearly?
    Glenn



  6. #6
    Registered User
    Join Date
    03-08-2015
    Location
    cleveland, ohio
    MS-Off Ver
    2013
    Posts
    48

    Re: Sumproduct - #value error

    department hours.xlsx

    Attached is an example of my workbook. Thank you for your help

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    35,756

    Re: Sumproduct - #value error

    Tony's second solution DID work, but what was missing was references to the shhets (now possible since your worksheet has been posted).

    =SUMPRODUCT(--ISNUMBER(MATCH(Data!B2:B18,Department,0)),--(Data!A2:A18="MAY"),Data!E2:E18)

  8. #8
    Registered User
    Join Date
    03-08-2015
    Location
    cleveland, ohio
    MS-Off Ver
    2013
    Posts
    48

    Re: Sumproduct - #value error

    Thank you! It worked, i used the "=" sign after department opposed to a ","!
    Last edited by mamachrissy1028; 05-15-2015 at 03:47 PM.

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

    Re: Sumproduct - #value error

    You're welcome. We appreciate the feedback!

+ 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. SUMPRODUCT Error
    By Killer17 in forum Excel General
    Replies: 11
    Last Post: 10-16-2008, 12:32 AM
  2. Sumproduct-het #VALUE! error.
    By pat55ski in forum Excel General
    Replies: 2
    Last Post: 02-08-2007, 05:13 PM
  3. Error values:DIV/0! error in SumProduct formula with no division
    By Jerry W. Lewis in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 07:05 PM
  4. [SOLVED] SumProduct Error?
    By john in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-08-2005, 08:06 AM
  5. [SOLVED] SUMPRODUCT ERROR
    By Mestrella31 in forum Excel General
    Replies: 1
    Last Post: 01-26-2005, 04:06 PM

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