=SumProduct Funtion #Value Error...

[Manucas]

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?)

I would appreciate your help, and too much thanks in advance!

Document: Absences Map 2020.xlsm

 

[joeu2004]

The formula in question is:

=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]

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.