Calculate the data base on day

[pratheesh1983]

Hello,
Iam preparing a team productivity report, column A, shows days of that month in MM-DD-YY format and column B shows the number of transaction processed on the day which is mentioned in columns A.

I want a formula which will add the vol if the day is Sat with the previous day i.e with Friday volume. Eg. 10-03-2016 (Friday) volume 2200; 11-03-2015 (Saturday) volume 1000; so in column C I need to put a formula that if columns A, date mentioned is coming on saturday then the value in column B should get added to value of Friday. In this case value 3200 should populate in columnc C next to Friday date and in Saturday it should be nill.
Smiliarly, if the day is Sunday then the value needs to get added with value of Monday in Column C next to Mondays date and in Sunday column if should be nil.
And cases where its not Sunday, sat, Friday or Monday then value should be the same as mentioned next to the date.
Could you pls help me to put a formula for the same?

 

[FDibbins]

See if this is what you want...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

Friday, January 01, 2016​

[/td][td]

1​

[/td][td]

6​

[/td][/tr]

[tr][td]

2​

[/td][td]

Friday, January 01, 2016​

[/td][td]

2​

[/td][td]

6​

[/td][/tr]

[tr][td]

3​

[/td][td]

Friday, January 01, 2016​

[/td][td]

3​

[/td][td]

6​

[/td][/tr]

[tr][td]

4​

[/td][td]

Saturday, January 02, 2016​

[/td][td]

4​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

Saturday, January 02, 2016​

[/td][td]

5​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

Sunday, January 03, 2016​

[/td][td]

6​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

Sunday, January 03, 2016​

[/td][td]

7​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

Monday, January 04, 2016​

[/td][td]

8​

[/td][td]

30​

[/td][/tr]

[tr][td]

9​

[/td][td]

Monday, January 04, 2016​

[/td][td]

9​

[/td][td]

30​

[/td][/tr]

[tr][td]

10​

[/td][td]

Tuesday, January 05, 2016​

[/td][td]

10​

[/td][td]

21​

[/td][/tr]

[tr][td]

11​

[/td][td]

Tuesday, January 05, 2016​

[/td][td]

11​

[/td][td]

21​

[/td][/tr]

[tr][td]

12​

[/td][td]

Wednesday, January 06, 2016​

[/td][td]

12​

[/td][td]

25​

[/td][/tr]

[tr][td]

13​

[/td][td]

Wednesday, January 06, 2016​

[/td][td]

13​

[/td][td]

25​

[/td][/tr]

[tr][td]

14​

[/td][td]

Friday, January 08, 2016​

[/td][td]

14​

[/td][td]

29​

[/td][/tr]

[tr][td]

15​

[/td][td]

Friday, January 08, 2016​

[/td][td]

15​

[/td][td]

29​

[/td][/tr]

[tr][td]

16​

[/td][td]

Saturday, January 09, 2016​

[/td][td]

16​

[/td][td][/td][/tr]

[tr][td]

17​

[/td][td]

Saturday, January 09, 2016​

[/td][td]

17​

[/td][td][/td][/tr]

[tr][td]

18​

[/td][td]

Sunday, January 10, 2016​

[/td][td]

18​

[/td][td][/td][/tr]
[/table]
C1=IF(WEEKDAY(A1,2)>5,"",IF(WEEKDAY(A1,2)=6,SUMIFS($B$1:$B$18,$A$1:$A$18,"<="&A1,$A$1:$A$18,">="&A1-1),IF(WEEKDAY(A1,2)=1,SUMIFS($B$1:$B$18,$A$1:$A$18,"<="&A1,$A$1:$A$18,">="&A1-1),SUMIF($A$1:$A$18,A1,$B$1:$B$18))))
copied down

 

[pratheesh1983]

Hi Ford! Thanks for the response!
Saturday vol needs to be added with the pevious day volumr i.e Friday volume like how it happening like for Sunday. Could you please help me with that?

 

[FDibbins]

oops missed that bit, and sorry for the delay. Try this...
=IF(WEEKDAY(A1,2)>5,"",IF(WEEKDAY(A1,2)=5,SUMIFS($B$1:$B$18,$A$1:$A$18,">="&A1,$A$1:$A$18,"<="&A1+1),IF(WEEKDAY(A1,2)=1,SUMIFS($B$1:$B$18,$A$1:$A$18,"<="&A1,$A$1:$A$18,">="&A1-1),SUMIF($A$1:$A$18,A1,$B$1:$B$18))))