sum last N rows not being a weekend
On Tuesday, 23 August 2016 16:50:25 UTC+4, wrote:
On Tuesday, 23 August 2016 14:54:20 UTC+4, Claus Busch wrote:
Hi,
Am Tue, 23 Aug 2016 03:33:37 -0700 (PDT) schrieb vimal:
I have data in two columns: column A having dates and a corresponding amount in column B.
Now in column C, I would like to have a formula which would sum the last 10 rows in column B which do not have a weekend in column A, i.e., sum only numbers in column B which is correspond to a weekday for the last 10 weekdays.
try:
=SUMPRODUCT((WEEKDAY(OFFSET($A$1,MATCH(MAX($A:$A), A1:A1000,0)-1,,-10),2)<6)*(OFFSET($A$1,MATCH(MAX($A:$A),A1:A1000,0 )-1,1,-10)))
Regards
Claus B.
--
Windows10
Office 2016
Thanks Claus
That gives the sum of the last ten days which were not weekends; I am looking for the sum of the last 10 weekdays.
But your response gave me some ideas ...
{=SUM(([Date]WORKDAY([@Date],-10))*(WEEKDAY([Date],2)<6)*([Date]<=[@Date])*[Amount])}
Formatted data as a table with Column A as [Date] and Column B as [Amount]
|