SumIf and a Range of Cells

[Erika]

Need to build a formula that will total up cells - if the information for the
current year is entered, see example. This spreadsheet has totals for 2006
and we like to compare the totals for 2006 with the totals for 2007.
JAN Feb March .........
Total
2006 20 8 18

2007 15 10
25

In the total column for 2006 I would only like to add up Jan and Feb because
currently there is not a total for March 2007 - when there is I would like to
formula to automatically adjust seeing that there is now a total in the March
2007 cell.

Is this possible?

 

[JE McGimpsey]

One way, assuming 2006 data in row 2, 2007 in row 3:


=SUM(OFFSET(B2,0,0,1,COUNT(B3:M3))

 

[Erika]

I think it is going to work, one problem I am having now. The totals for
entries for 2007 are actually a paste link from a different sheet - so there
is a total in those coulmn of a zero - they are not actually blank so it is
totally all of 2006.

Is there a way to modify this formala to say greater then zero?

 

[JE McGimpsey]

One way:

=SUM(OFFSET(B2,0,0,1,COUNTIF(B3:M3, ">0"))

 

[Erika]

It looks like this is close however it is picking up one cell that is blank.
Would you be able to break down the what the formula is doing and I can see
if I can fix it?

 

[JE McGimpsey]

It's counting the number of values in the range B3:M3 (presumably 12
months) that are >0, indicating that a "real" value is coming from the
source.

OFFSET() then creates an range, starting at B2, that's 1 row tall by the
number of columns created above.

SUM() then sums that range.