Compare one year to another, but just YTD

[M.Siler]

Let's see if I can explain this. I have two sheets, the first sheet is
labeled 2005 and the second one is 2006. In A1 thru A12 on both sheets is
Jan, Feb, Mar, etc. On 2005 B1 thru B12 we have monthly values. On 2006 B1
thru B7 (as we are just now going into Aug.) we have monthly values. In B13
on both sheets are totals. The problem is I can't compare on year to the
next as the total on the 2005 sheet is for the full year. I'd like to add
Label in A14 that says 2005 YTD and have a formula in B14 that looks at how
many cells in the range B1 thru B12 on 2006 have values then sum that same
number of cells from the B column on sheet 2005. This way I'd be able to
compare 2006's YTD with the same YTD of 2005.

 

[Bob Phillips]

=SUM(OFFSET('2005'!$B$1,,,COUNT('2006'!$B$1:$B$12),1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[M.Siler]

I had to modify it to
=SUM(OFFSET('2005'!$B$1,,,COUNT('2006'!$B$1:$B$12)+1,1)) as it was off my
one month. I don't totally understand the fomula, but I'm looking at it now.

 

[M.Siler]

I modified the formula to I could copy it to other columns

=SUM(OFFSET('2005'!B$1,,,COUNT('2006'!B$1:B$12)+1,1)) This worked
=SUM(OFFSET('2005'!C$1,,,COUNT('2006'!C$1:C$12)+1,1)) This did not I had to
remove the +1 so it looks like this
=SUM(OFFSET('2005'!C$1,,,COUNT('2006'!C$1:C$12),1)) What am I missing?? I
need to copy this to the right for columns B thru K

 

[M.Siler]

Sorry... nothing wrong with the orginal formula... one of the numbers was
entered wrong so excel treated it as text and that messed up the formula.

Thank you.

 

[Bob Phillips]

I just sat back and sent hints across the ether <bg>

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)