+ Reply to Thread
Results 1 to 6 of 6

SUMPRODUCT returning VALUE error

  1. #1
    Registered User
    Join Date
    08-22-2013
    Location
    Wellsville, PA
    MS-Off Ver
    Office 365
    Posts
    3

    Angry SUMPRODUCT returning VALUE error

    Okay, this is driving me crazy and I know the solution is a simple one, I'm just not seeing it no matter what I do. I have two workbooks (for now, it is technically more, but right now I'm just working with two). BanksTest and SupervisorTest.

    BanksTest is where an employee will log in what work they have done.
    Column B is the date.
    Column F is the time spent (calculated from Columns D and E, and will be hidden from view, the employees really don't need to see any calculations)
    Column J is the Activity Code (type of work done)

    What the supervisor wants to see is how much time per month was spent on each Activity Code. I know SUMIFS will not work unless BanksTest is open. I don't want the supervisor to have to remember to have each employee's sheet open when he's looking at his own spreadsheet (nice guy, not computer savvy... I had to make a video on how to create a desktop shortcut). So I'm looking to SUMPRODUCT for the calculation, but I still cannot get it to work.

    So for July (the beginning of our fiscal year), Activity Code 01
    =SUMPRODUCT(--([BanksTest.xlsx]Log!$J$13:$J$18=1),--([BanksTest.xlsx]Log!$B$13:$B$18>=$U6),--([BanksTest.xlsx]Log!$B$13:$B$18<=$V6),--([BanksTest.xlsx]Log!$F$13:$F$18))

    And the blasted thing STILL gives me !VALUE#
    What am I not catching....

    (At this point I'm not even worried about the quarterly/yearly stuff, if I can get the monthly to add up, I can extrapolate that info)
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: SUMPRODUCT returning VALUE error

    Banks


    B6=SUMPRODUCT(([BanksTest.xlsx]Log!$J$13:$J$18=B$3)*(MONTH([BanksTest.xlsx]Log!$B$13:$B$18)=MONTH($A6)),[BanksTest.xlsx]Log!$F$13:$F$18)


    copy across and down
    Last edited by CARACALLA; 07-01-2020 at 01:27 PM.

  3. #3
    Registered User
    Join Date
    08-22-2013
    Location
    Wellsville, PA
    MS-Off Ver
    Office 365
    Posts
    3

    Re: SUMPRODUCT returning VALUE error

    Okay, school me, I'm an idiot. Where did I screw up? *laugh* Because that's beautiful. Thank you
    Last edited by tonkasmum; 07-01-2020 at 01:29 PM.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: SUMPRODUCT returning VALUE error

    SUMPRODUCT with -- doesn't like blank cells. At the moment F18 in BanksTest evaluates as "". Change the formula to:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and obviously the same in F13:F17

    If you don't want zeros to show then change the zeros display parameter for that sheet in the application options.
    Last edited by Richard Buttrey; 07-01-2020 at 01:39 PM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: SUMPRODUCT returning VALUE error

    English is not my mother tongue

    Does the formula work or not ?

  6. #6
    Registered User
    Join Date
    08-22-2013
    Location
    Wellsville, PA
    MS-Off Ver
    Office 365
    Posts
    3

    Re: SUMPRODUCT returning VALUE error

    CARACALLA: It worked perfectly. Thank you so much! (And your English is fine! I envy you being bilingual)

+ 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] Sumproduct function returning error.
    By [email protected] in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-14-2019, 02:54 PM
  2. [SOLVED] SUMPRODUCT/IF/COUNTIFS formula returning a #DIV/0 error
    By Nate_from_Australia in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-28-2018, 02:04 AM
  3. Sumproduct formula returning #Value! error
    By brizmol1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-13-2014, 05:16 PM
  4. Sumproduct - returning error #Div/0! - Please help
    By wvnimal in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2013, 09:41 AM
  5. [SOLVED] Sumproduct returning #Error
    By Knocknaboula in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-06-2013, 03:24 PM
  6. [SOLVED] Sumproduct formula is returning #value error
    By ensmith in forum Excel General
    Replies: 2
    Last Post: 06-27-2012, 09:56 PM
  7. [SOLVED] SUMPRODUCT formula returning #VALUE! error
    By Valerie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-28-2006, 09:40 AM

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