An Excel question.
I got a table with a column of dates and a calculated column with value's. In a summary cell I do have
=SOMPRODUCT( (MONTH(Tijd[Datum(s)])=MONTH(TODAY()) )* (Tijd[Uren gewerkt in decimalen]) )
of course works perfect. However with the same columns and calculating the weekly based summary I get error's
=SOMPRODUCT( (WEEKNUMBER(Tijd[Datum(s)])=WEEKNUMBER(TODAY()) ) * (Tijd[Uren gewerkt in decimalen]) )
This gives me a #Value err. If I use WeekNumber(AnEmptyCell) as an test, it just returns a zero, no err value.
F9 key results -
Tijd[Datum(s)] - all the dates and zero's when no date.
(WEEKNUMMER(Tijd[Datum(s)]) - #Value error
all the other parts are also okay.
All the fields in the Datums Column are date fields.
What do I wrong and how to solve this?
There are two types of Excel formula functions: those that can be used with arrays and those that cannot. MONTH is of the first type, WEEKNUM is of the second. To test it, put two arbitrary dates into A1:A2, and enter =MONTH(A1:A2) into B1:B2 (Select B1:B2, and press Ctrl + Shift + Enter), and =WEEKNUM(A1:A2) into C1:C2. The first will show valid values but the second will be two #VALUE! errors.
If you use the formula in the above setup, I would recommend, to examine if the date is after the Monday and before the Sunday of the given week.
I don't have access to an Excel with Dutch (?) locale, so I added the English name of the functions to insert in your formula:
=SOMPRODUCT( ( TODAY()-WEEKDAY(TODAY(),2)+1 <= Tijd[Datum(s)] ) * ( Tijd[Datum(s)] < TODAY()-WEEKDAY(TODAY(),2)+7 ) * (Tijd[Uren gewerkt in decimalen]) )