Any ideas for this formula???

  • Thread starter Thread starter John Sofillas
  • Start date Start date
J

John Sofillas

I am looking for the sum of several numbers (with the intent that as I
delete the columns of the #'s out of the look up, the formula doesn't
result in an error). To make things even tougher, there will be 2
formulas needed, where each are looking for their own respective
answers coming from the same row. Confused? lol..... Below are
examples of both.....

The 1st formula would go into cell D45. I need it to return the sum of
P42, V42, AB42, and so on all the way out to DH42 (17 total cells).
The 2nd formula would go into cell D46. I need it to return the sum of
Q42, W42, AC42 and so on all the way out to DI42 (17 total cells).
Keep in mind that as I delete columns DH and DI, and DB and DC and so
on all the way back, that the formulas in D45 and D46 still continue
to work.

I hope I explained this clear enough to you all. Any questions, let me
know. Thanks again in advance for your help.

John
 
I am looking for the sum of several numbers (with the intent that as I
delete the columns of the #'s out of the look up, the formula doesn't
result in an error). To make things even tougher, there will be 2
formulas needed, where each are looking for their own respective
answers coming from the same row. Confused? lol..... Below are
examples of both.....

The 1st formula would go into cell D45. I need it to return the sum of
P42, V42, AB42, and so on all the way out to DH42 (17 total cells).
The 2nd formula would go into cell D46. I need it to return the sum of
Q42, W42, AC42 and so on all the way out to DI42 (17 total cells).
Keep in mind that as I delete columns DH and DI, and DB and DC and so
on all the way back, that the formulas in D45 and D46 still continue
to work.

I hope I explained this clear enough to you all. Any questions, let me
know. Thanks again in advance for your help.

John
Click to expand...

Try where you sum one more column than needed

=sum(q42:dJ42)
 
Hi John,

Am Fri, 8 Jul 2011 05:20:49 -0700 (PDT) schrieb John Sofillas:
The 1st formula would go into cell D45. I need it to return the sum of
P42, V42, AB42, and so on all the way out to DH42 (17 total cells).
The 2nd formula would go into cell D46. I need it to return the sum of
Q42, W42, AC42 and so on all the way out to DI42 (17 total cells).
Keep in mind that as I delete columns DH and DI, and DB and DC and so
on all the way back, that the formulas in D45 and D46 still continue
to work.
Click to expand...

first formula:
=SUMPRODUCT(--(MOD(COLUMN(P42:DI42),6)=4),P42:DI42)
second formula:
=SUMPRODUCT(--(MOD(COLUMN(P42:DI42),6)=5),P42:DI42)


Regards
Claus Busch
 
Hi John,

Am Fri, 8 Jul 2011 05:20:49 -0700 (PDT) schrieb John Sofillas:


first formula:
=SUMPRODUCT(--(MOD(COLUMN(P42:DI42),6)=4),P42:DI42)
second formula:
=SUMPRODUCT(--(MOD(COLUMN(P42:DI42),6)=5),P42:DI42)

Regards
Claus Busch
Click to expand...



Thanks Claus!!! Perfect!
 
Back
Top