=SumProduct Funtion #Value Error...

Manucas

New Member
Hello community.

I'm sending attached the file that's an adaptation of a free template that i found online.

During my adaptations i came across with a problem that i can't solve:

In the attachment you're allowed to see the formulas apllied to "NJ" and "NK" cells, where in the formula bar the entire sumproduct function appears between { }.

What's a fact is, when i check the formula and click enter, it gives me the #Value Error. However, the formula works in the "default" formulas configuration (and i don't know why, because when i click in it - in the formula bar - they simply dissapear).

In other hand, the changes that i have been trying to make (but without sucess, and i think is just because the #Value error) are:
1. The point is to convert the "1" assumed value at "compensatory 1" and "compensatory 2" option to 0.5, similarly to the pre-assumed "Half Day Leaves";
2. I adjusted the formula and use an "OR" function at the "NJ" and "NK" cells which made more sense for me;

Therefore, i can't understand why i'm keeping to get the "#Value!" error or if i'm doing something wrong (and if so, what could be?)

Document: Absences Map 2020.xlsm

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

joeu2004

Well-known Member
The formula in question is:
Code:
``````=SUMPRODUCT((OFFSET(\$A8,0,31*(\$A\$3-1)+1,1,31)<>"")
*(IF(OR(OFFSET(\$A8,0,31*(\$A\$3-1)+1,1,31)=\$OA\$16,
OFFSET(\$A8,0,31*(\$A\$3-1)+1,1,31)=\$OA\$17,
OFFSET(\$A8,0,31*(\$A\$3-1)+1,1,31)=\$OA\$13,
OFFSET(\$A8,0,31*(\$A\$3-1)+1,1,31)=\$OA\$14),0.5,1)
*(OFFSET(\$A\$4,0,31*(\$A\$3-1)+1,1,31))))``````

The formula is normally-entered. Such formulas must be array-entered: press ctrl+shift+Enter instead of just Enter. And in that case, you might as well use SUM instead of SUMPRODUCT.

That is the cause of the #VALUE error.

However, even though that eliminates the #VALUE error, the formula will not function as intended, because the OR function does not operate column-by-column. Instead, the OR function operates on the entire ranges (arrays), returning a single logical value (TRUE or FALSE).

Manucas

New Member
OMG, Joeu.

You just don't know how many time i've been banging my head on the walls because of this.

And, you just came here, 3 hours after i publish my thread, telling me that all i needed to do is to press "Ctrl+Shift+Enter".

Right now, for me, you're like a God at Mr.Excel's forum. PROBLEM SOLVED (but what a niggling solution...)

I didn't know that. I always thought that excel was enought intelegent to understand that should consider an array (and it never happened to me).

Anyway, concerning the formula and your last comment/help, indeed. It's a fact that didn't work, and makes total sense. I already made the adjustments.

Thanks a lot for your help, Sir.

Replies
5
Views
218
Replies
13
Views
154
Replies
4
Views
375
Replies
1
Views
232
Replies
1
Views
261

1,127,572
Messages
5,625,583
Members
416,119
Latest member
JCLLE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back