Dynamic Sum Range....Weekly Sum (Saturday to Friday)

[PP3321]

I need to dynamically calculate sum of following 3 range:

1. First day of the month ---> First Friday
2. Saturday---->Friday
3. Saturday---->Last Day of the Month

Could anyone please give me advice...?

 

[PP3321]

I have tried following formulas to get row number.
But I am stuck after this....

1. First Day of Month
=ROW()*(ROW(A2)=ROW(INDEX($A$2:$A$31,1,1)))

2. Friday
=ROW()*(A2="Friday")

3. Saturday
=ROW()*(A2="Saturday")

4. Last Day of Month
=ROW()*(ROW(A2)=ROW(INDEX($A$2:$A$31,COUNTA($A$2:$A$32),1)))

 

[NARAYANK991]

Hi ,

What if the first day of the month is a Friday ?

Narayan

 

[PP3321]

Hi Narayan thank you for your post.
If it is Friday, then we will get the sum of only 1 day only.

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[PP3321]

Thank you so much!!!!!!!!!!!!!I have been spending last 3-4 hours trying to solve this wow I am very embarrassed....!

 

[PP3321]

Hi @NARAYANK991

Sorry can I ask just 1 more question?

What if the number on Column C is not 1,
but is random number...?

Day 1---59
Day 2---60
etc...

I have written following formula.
*Column D contains numbers.

I am struggling to minus the previous Friday to get the correct data.
=IF(A4<7,IF(B4="Friday",SUM($D$4:D4),""),IF(OR(B4="Friday",B5=""),SUM($D$4:D4),""))

If you could give me advice, I would be extremely grateful...

 

[NARAYANK991]

Hi ,

What will this number represent ? Suppose the number is 59 , what does this mean ? Is it the 59th day of the year ?

If so , then we can get the actual date as :

1/1/2016 + 59 - 1

and work out everything from this date. I assume that these numbers will relate to the current year.

Narayan

 

[PP3321]

Column C is actually daily sales data.
I want to calculate weekly sales total up to Friday...

I put 1 to make it easy to check the total.
so it can be any number.

Sorry I did not explain well in the beginning.

 

[NARAYANK991]

Hi ,

See if this helps.

Narayan

 

[PP3321]

Thank you @NARAYANK991 !!!!!

I found this too.
This works as well without a helper column
*You need to include the header row in the calculation...

=IF(OR(B4="Friday", B5=""),SUM(D$3:D4)-SUM(F$3:F3),"")