=SUMPRODUCT( (WEEKNUM(Table13]) *Table13 ) Since SUMPRODUCT function comfortably handles arrays, we can use this array of week numbers to create a summary as shown below. The above function does not return an error but an array of week numbers from the given dates range. It can be forced to accept range by adding zero to a range i.e. It is not entirely true that WEEKNUM does not accept a range argument. Since WEEKNUM does not accept a range argument, then we have to create a helper column so that we can be able to use SUMIF Given the below data, Show totals per week. In this article, I will show you 3 ways to simplify your weekly analysis: So, How do you get week numbers in an array given a range of dates?ĭo you need to always use a helper column to convert dates to week numbers before any analysis is done? =WEEKNUM(K11:K110) just returns #VALUE! error. NB: If the return type is omitted, function defaults to week start on Sunday all the way to Saturday =WEEKNUM( serial_number, ) īeautiful as the function is, it has one major weakness, WEEKNUM doesn’t accept a range argument e.g. This is mostly so when you are in the production section.Įxcel has a beautiful but overlooked function ( WEEKNUM) that returns an integer representing the week in the year (1 to 53).įor anyone not familiar with WEEKNUM function, it contains 2 arguments Serial_number ( the date to return the week number for) and an optional Return_type (an integer that defines when the week starts). Every organization requires weekly summaries to monitor peak and off-peak periods.
0 Comments
Leave a Reply. |