Any ideas for this formula???

[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

 

[Don Guillett]

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

Try where you sum one more column than needed

=sum(q42:dJ42)

 

[Claus Busch]

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.

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


Regards
Claus Busch

 

[John Sofillas]

Hi John,

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


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

Regards
Claus Busch

Thanks Claus!!! Perfect!