sum based on a fluid number

[Emmy Lou]

I have a spreadsheet I am working on that I need a formula for. If I have four colums I am working with how would I write the following. Column A is the date based on only workdays which changes for each month, column B is the day of the week based on the date column, column C is the week number in the year based on the date column and column L is the amounts I want to sum based on what week of the year it is. Because the dates and week numbers change for each month, the week number doesnt stay static. So basically I want it to sum column L based on what week number of the year is as it changes month to month. No matter what formula I think of, it doesnt encompass a variable number. See attached spreadsheet.

[alansidman]

Try using the Sumif function
=SUMIF(L7:L29,C7:C29=19)

Look here for the syntax.
http://www.techonthenet.com/excel/formulas/sumif.php

alan

[Emmy Lou]

Thanks though!

[Emmy Lou]

Thank Alan, that didnt really work. I only want it summing the week on "friday" or the last day of the month which ever comes first. So every Friday there should be a total, then up to 5/31/11 there should be a total. The original formula I wrote =IF(B7="FRIDAY",SUM(L3:L7)," ") worked well until it came to the last couple of days in the month. Then, not so well. Any suggestions?

[shg]

How about changing the formula in M7 to

=IF(MONTH($L$1)<>MONTH(A7), "", IF(DAY(A7)=DAY(EOMONTH($L$1, 0)), SUM(L$7:L7), IF(WEEKDAY(A7) = 6, SUM(L3:L7)," ")))

The EOMONTH function requires the Analysis ToolPak.

[Chance2]

What about an Offset?

=IF(B7="FRIDAY",SUM(OFFSET(L7,0,0,-COUNTIF($C:$C,C7),1))," ")

[Emmy Lou]

Thanks shg, but I dont have the analysis tool pack and would have to get the addition approved by our head office to get it added to my system. I probably need to stay with what I have available. I love the formula though.

[Emmy Lou]

Thanks Chance2, I love the offset and it works great with the exception of the last few days of the month. For example the month of May has the 30 and 31st that do not total because they are Monday and Tuesday. Any way we can have it take the last day of the month in account?

[alansidman]

I think that the analysis pack comes with Excel 2003. I think that you only need to activate it. Go to tools, Add-ins and click on the Analysis Tool Pack.

Alan