Any ideas for this formula???
 

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

Any ideas for this formula???
 

On Jul 8, 7:20*am, John Sofillas wrote:
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)

Any ideas for this formula???
 

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(P42:DI42),6)=4),P42:DI42)
second formula:
=SUMPRODUCT(--(MOD(COLUMN(P42:DI42),6)=5),P42:DI42)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Any ideas for this formula???
 

On Jul 8, 10:05*am, Claus Busch wrote:
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(P42:DI42),6)=4),P42:DI42)
second formula:
=SUMPRODUCT(--(MOD(COLUMN(P42:DI42),6)=5),P42:DI42)

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2



Thanks Claus!!! Perfect!