Expanding Sum Formula Needed

[Zakkaroo]

I have two columns of data, one contains data from 2010, the 2nd contains data from 2011 (being updated constantly)

At the bottom of each column is a TOTAL for 2010 and a total for 2011. These totals are compared to see if there has been an increase or decrease.

However, because 2010 has figures for the whole year and 2011 doesn't. The comparison isn't accurate.

What I need to do, is Sum the 2010 figures UP TO the point where the 2011 figures end. I realise this can be done manually, but i'd rather not leave it like that as I won't be the one filling the report in and I don't think they'll remember to change the formula each week.

i.e.

<TABLE style="WIDTH: 138pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=184 x:str><COLGROUP><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" span=2 width=68><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 36pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 height=17 width=48>Week</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 width=68>2010 Figs.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 width=68>2011 Figs.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl28 x:num>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl28 x:num>1794</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl23 height=17 x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>1140</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl23 height=17 x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>1844</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl23 height=17 x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>1662</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl23 height=17 x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>1201</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl23 height=17 x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>1655</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl23 height=17 x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>2567</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl23 height=17 x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>2706</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl23 height=17 x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>2710</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl23 height=17 x:num>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl23 height=17 x:num>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl23 height=17 x:num>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 x:num>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=18></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 13.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=18>Total</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl26 x:num x:fmla="=SUM(B2:B13)">1200</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl26 x:num x:fmla="=SUM(C2:C13)">17279</TD></TR></TBODY></TABLE>

So, in the example above, in Week 9, I want 2010's total to be 900 not 1200

Any help, much appreciated.

In the Actual sheet in question it is laid out as follows:

Column A = the week numbers
A5 = "1"
A6 = "2"
etc

Column C = 2010's figures

C5 = Week 1's figures
C6 = Week 2's figures
C58 = 2010's total

Column E = 2011's figures

E5 = Week 1's figures
E6 = Week 2's figures
E58 = 2011's total

 

[njimack]

=sumif(c2:c13,"<>"&"",b2:b13)

 

[Richard Schollar]

Hi

Say the 2011 figures are in C2:C13 then you could use:

=SUM(B2:INDEX(B2:B13,MAX(1,COUNT(C2:C13))))

 

[Zakkaroo]

=sumif(c2:c13,"<>"&"",b2:b13)

Thanks to both of you for the extra-speedy responses, I was still busy editing to make it clearer and you'd already answered.

I have used the above formula (edited slightly for specific circumstances) and it works perfectly.

Thanks both for your time.

 

[Weaver]

Good to hear you have solved your issue, just thought I'd point out the

&"" isn't necessary

=SUMIF(C2:C13,"<>",B2:B13)

works just as well.

 

[njimack]

Glad it worked.

Good to hear you have solved your issue, just thought I'd point out the

&"" isn't necessary

=SUMIF(C2:C13,"<>",B2:B13)

works just as well.

Oh yeah - force of habit, formed by comparing the range to a value or cell reference.