sum last N rows not being a weekend
 

Hello

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.

Any help would be appreciated.

sum last N rows not being a weekend
 

Hi,

Am Tue, 23 Aug 2016 03:33:37 -0700 (PDT) schrieb :

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

sum last N rows not being a weekend
 

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.

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]