+ Reply to Thread
Results 1 to 3 of 3

Sumproduct formula returns #VALUE!

  1. #1
    Registered User
    Join Date
    11-19-2019
    Location
    San Francisco, CA
    MS-Off Ver
    Professional Plus 2013
    Posts
    2

    Sumproduct formula returns #VALUE!

    Good day. I am trying to create a sumproduct formula that uses 2 criteria to determine value.

    Example: In Form A, formula (highlighted in green) in E9 is =SUMPRODUCT('IS YTD'!B92:CZ2000*('IS YTD'!A92:A2000=C9)*('IS YTD'!B90:CZ90=E5)) where

    B92:CZ2000 is data range
    A92:A2000 is list of organizations (row criteria)
    B90:CZ90 is Account Names (column criteria)

    C9 is "Revenue" located in C9
    E9 is "China Total" located in E5

    For the life of me, I can't get this darn formula to not give me a #VALUE! I think it might have something to do with the formatting on the other sheet that comes out of Oracle. My brain would love some help.
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,825

    Re: Sumproduct formula returns #VALUE!

    change it from this...
    =SUMPRODUCT('IS YTD'!B92:CZ2000*('IS YTD'!A92:A2000=C9)*('IS YTD'!B90:CZ90=E5))
    to this...
    =SUMPRODUCT('IS YTD'!B92:CZ2000,('IS YTD'!A92:A2000=C9)*('IS YTD'!B90:CZ90=E5))
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    11-19-2019
    Location
    San Francisco, CA
    MS-Off Ver
    Professional Plus 2013
    Posts
    2

    Re: Sumproduct formula returns #VALUE!

    Thank you so much!! It finally worked.

+ 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/sumproduct formula returns error or zero
    By adsako in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-26-2018, 03:06 PM
  2. [SOLVED] SUMPRODUCT Returns #VALUE
    By MatthewIJClark in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-19-2018, 05:24 AM
  3. [SOLVED] sumifs/sumproduct formula returns VALUE error
    By adsako in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-01-2018, 08:17 AM
  4. Sumproduct formula returns #ref when linked data file is closed.
    By shandrak in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-23-2014, 08:54 PM
  5. Sumproduct formula returns #VALUE error
    By Freddobonanza in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-30-2012, 01:36 AM
  6. SumProduct formula returns #VALUE Error
    By Ra21V12eN in forum Excel General
    Replies: 7
    Last Post: 02-12-2009, 04:48 PM
  7. [SOLVED] One Sumproduct Formula works - while other returns #VALUE!?
    By Correna in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-04-2006, 09:10 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