Finding Friday

[Guest]

Okay, I have a spreadsheet. Cell A1 contains the month, Column B contains
all the weekdays of that month. Daily data is stored is Column C. I want to
do weekly totals of the data in Column C as well. However, if the user
inputs a different month in A1, the weekdays will all change, shift...etc.

Therefore, to total week 1, I need to locate the first instance of Friday in
Column B, and then add that to everything above it. Week 2 needs to find the
2nd instance of Friday and add everything from that Friday to the first
Friday...and so on.

How does one do this?

Thank you very much....

Arlen

 

[Myrna Larson]

I assume that column B contains actual dates, not just the numbers 1-31 (you
can use the custom format "d" if you want to see only the day of the month).

You can put this formula in D2 and copy it down.


=IF(WEEKDAY(B2)<>6,"",SUMIF(B$2:B2,"<="&B2,C$2:C2)-SUMIF(B$2:B2,"<="&B2-7,C$2:C2))

You'll see a total in the rows where the date is a Friday, otherwise empty
text.

Note that when the week begins on a date other than a Monday, the first total
is for less than 5 days.

I don't know how you plan to enter the weekdays for the given month in column
A. Assuming you have the Analysis Tool Pack installed is this formula in B2

=WORKDAY(DATEVALUE($A$1&" 1, 2005")-1,1)

and in B3 through B32

=IF(B2="","",IF(MONTH(WORKDAY(B2,1))=MONTH(B$2),WORKDAY(B2,1),""))