+ 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 Sambo kid'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,172

    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)

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