# SUMPRODUCT returning VALUE error

1. ## 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)

2. ## 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

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

4. ## 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:
`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.

5. ## Re: SUMPRODUCT returning VALUE error

English is not my mother tongue

Does the formula work or not ?

6. ## Re: SUMPRODUCT returning VALUE error

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

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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