Summing column and rows

  • Thread starter Thread starter L. Howard Kittle
  • Start date Start date
L

L. Howard Kittle

Hello Excel users and Experts,

=C1+INDIRECT(CHAR(ROW()+67)&1)

With this formula in B1 and pulled down will add C1 & D1, C2 & E1, C3 & F1
etc.

How can I make INDIRECT(CHAR(ROW()+67)&1) reference sheet 2 with C1, C2, C3
on sheet 1?

So it sums C1 of sheet 1 and D1 of sheet 2
C2 of sheet 1 and E1 of sheet 2
and so on.

Thanks for any ideas.

Regards,
Howard
 
=C1+INDIRECT("Sheet2!"&CHAR(ROW()+67)&1)

--

HTH

Bob Phillips

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

=C1+INDIRECT("Sheet2!"&CHAR(ROW()+67)&1)

Or, a non-volatile approach:

=C1+INDEX(Sheet2!D$1:IV$1,ROWS($1:1))

Biff
 
Hey Bob & Biff,

Many thanks for the solutions guy's!!

O'boy, I was a hog sniff'n pretty close to the truffle, but could not get
that "sheet2!" in the right place or the "(" & ")" in the right places.

And Biff, the non volatile formula is another tribute to your many
contributions. Exactly what I was looking for. I had no idea it could be
done that way.

Thanks to both of you.

PS: This is for my wife's project and she thinks I'm an Excel genius. Go
figure, but I know where to get answers!!! She doen't need to know... hee
hee.

Thanks again.

Regards,
Howard
 
You're welcome. Thanks for the feedback!

Biff

L. Howard Kittle said:
Hey Bob & Biff,

Many thanks for the solutions guy's!!

O'boy, I was a hog sniff'n pretty close to the truffle, but could not get
that "sheet2!" in the right place or the "(" & ")" in the right places.

And Biff, the non volatile formula is another tribute to your many
contributions. Exactly what I was looking for. I had no idea it could be
done that way.

Thanks to both of you.

PS: This is for my wife's project and she thinks I'm an Excel genius. Go
figure, but I know where to get answers!!! She doen't need to know... hee
hee.

Thanks again.

Regards,
Howard
Click to expand...
 
Back
Top