Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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]
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Weekend Dates Tracey Charts and Charting in Excel 1 August 13th 08 11:03 AM
Weekend The Rook[_2_] Excel Discussion (Misc queries) 12 June 11th 08 06:11 PM
Weekend Pass Codes Excel Worksheet Functions 2 June 4th 07 09:02 PM
Last weekend of month Jeremy Excel Worksheet Functions 5 April 24th 05 03:41 AM
Weekend dates bresam Excel Programming 2 June 11th 04 11:39 AM


All times are GMT +1. The time now is 09:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"