Running Monthly Totals on Multiple Sheets

Originally Posted by Dragunov

Hey everyone,

I'm creating a monthly report for my company. I have a maximum of five weeks in this reporting period. Each week is a separate sheet, with a sixth sheet crunching all of the numbers and putting the weekly results into a monthly format.

Some of these numbers are percentages. I've tried doing a simple SUM function, but that simply added up all of the percentages (predictably) into a large sum, instead of giving me the true percentage. Next I tried taking all of the information in sheets 1-5 added it up, and then divided it by 5 (for 5 weeks). Here is the formula I used:

=SUM(Week1:Week5!Q9)/5))

It worked beautifully, but then I realized that some months will only have four weeks. So I did this:

=IF(Week5!Q9="",(SUM(Week1:Week4!Q9)/4),(SUM(Week1:Week5!Q9)/5))

Finally, it dawned on me that my superiors are going to want to know progress reports in the middle of the month. Does anybody know anything that could do this for me? Remember, I'm using percentages. Isn't there a multiple if statement like X, Y, Z = "", then [DO NOT ADD] X,Y,Z?

Thanks in advance,

Try this:

=SUM(Week1:Week5!Q9)/COUNTA(Week1:Week5!Q9)