Something better than this IF forumla?

[EOAEvan]

Im using the following forumla to add numbers from 3 different sheets. Each sheet has 6 columns that are filled in from right to left at different times. The left most column with a number in it is always considered the most up to date. The test is always if row 34 in each column does not equal 0. If it does it needs to move to the "earlier" column and test that one. There will always be something in the right most column. Im looking for something more efficient and more importantly shorter as I need to further alter this and length of the formula will be an issue for users with ealier versions of excel. I am using 2010 but needs to be compatiable with 2000. Thanks!!

=IF(FEB!$C$34=0,IF(FEB!$D$34=0,IF(FEB!$E$34=0,IF(FEB!$F$34=0,FEB!$G41,FEB!$F41),FEB!$E41),FEB!$D41),FEB!$C41)+IF(MAR!$C$34=0,IF(MAR!$D$34=0,IF(MAR!$E$34=0,IF(MAR!$F$34=0,MAR!$G41,MAR!$F41),MAR!$E41),MAR!$D41),MAR!$C41)+IF(APR!$C$34=0,IF(APR!$D$34=0,IF(APR!$E$34=0,IF(APR!$F$34=0,APR!$G41,APR!$F41),APR!$E41),APR!$D41),APR!$C41)

 

[Aladin Akyurek]

Im using the following forumla to add numbers from 3 different sheets. Each sheet has 6 columns that are filled in from right to left at different times. The left most column with a number in it is always considered the most up to date. The test is always if row 34 in each column does not equal 0. If it does it needs to move to the "earlier" column and test that one. There will always be something in the right most column. Im looking for something more efficient and more importantly shorter as I need to further alter this and length of the formula will be an issue for users with ealier versions of excel. I am using 2010 but needs to be compatiable with 2000. Thanks!!

=IF(FEB!$C$34=0,IF(FEB!$D$34=0,IF(FEB!$E$34=0,IF(FEB!$F$34=0,FEB!$G41,FEB!$F41),FEB!$E41),FEB!$D41),FEB!$C41)+IF(MAR!$C$34=0,IF(MAR!$D$34=0,IF(MAR!$E$34=0,IF(MAR!$F$34=0,MAR!$G41,MAR!$F41),MAR!$E41),MAR!$D41),MAR!$C41)+IF(APR!$C$34=0,IF(APR!$D$34=0,IF(APR!$E$34=0,IF(APR!$F$34=0,APR!$G41,APR!$F41),APR!$E41),APR!$D41),APR!$C41)

Define BigNum by means of Formulas | Name Manager as referring to:

=9.99999999999999E+307

Then invoke...

=SUM(
    LOOKUP(BigNum,FEB!$C$34:$F$34,FEB!$C$41:$F$41),
    LOOKUP(BigNum,MAR!$C$34:$F$34,MAR!$C$41:$F$41),
    LOOKUP(BigNum,APR!$C$34:$F$34,APR!$C$41:$F$41))

 

[EOAEvan]

I am not familiar with the method you are using so im not sure if i did it correctly. I filled in every column on all 3 sheets starting with 100 in column c, 200 in column d...through 600 in colulumn G... So the test result should be it that the left most column on all 3 sheets does not equal zero therfore it adds column c from each sheet. so 100 + 100 + 100=300. The result of what you have shown above is giving me 1200.


I went to Define name:

Name: BigNum
Scope: workbook
Comment: none
Refers to: =9.99999999999999E+307

Then placed your formula in the cell I want the result to show.

Suggestions on why I am not getting the correct result of 300?

 

[Aladin Akyurek]

I am not familiar with the method you are using so im not sure if i did it correctly. I filled in every column on all 3 sheets starting with 100 in column c, 200 in column d...through 600 in colulumn G... So the test result should be it that the left most column on all 3 sheets does not equal zero therfore it adds column c from each sheet. so 100 + 100 + 100=300. The result of what you have shown above is giving me 1200.


I went to Define name:

Name: BigNum
Scope: workbook
Comment: none
Refers to: =9.99999999999999E+307

Then placed your formula in the cell I want the result to show.

Suggestions on why I am not getting the correct result of 300?

Maybe I misunderstood your intend...

Given:

FEB

C34 = 0,
D34 = 12

MAR

C34: 10
D34: 0
E34: 6

What result do we need to produce?

 

[EOAEvan]

My orignal formula tests line 34 then pulls a different line based on the test result but for simplicity we will use just the numbers you have and test line 34 and pull line 34.

Using just the two months you have listed the intended answer would be 22. It would use 12 from Feb as it is the left most column that does not equal 0 and it would use 10 from Mar.


The columns are filled in from right to left G -> C. C will never be filled in before D and D never before E etc.

 

[Aladin Akyurek]

My orignal formula tests line 34 then pulls a different line based on the test result but for simplicity we will use just the numbers you have and test line 34 and pull line 34.

Using just the two months you have listed the intended answer would be 22. It would use 12 from Feb as it is the left most column that does not equal 0 and it would use 10 from Mar.


The columns are filled in from right to left G -> C. C will never be filled in before D and D never before E etc.

Ok. Could you post some typical data from:

FEB!$C$34:$F$34

and from

FEB!$C$41:$F$41 ?

 

[EOAEvan]

I am not able to download and install the HTML maker on this PC so please bear with me. Line 34 is a sum of expense items from other lines above it and line 41 is a driver behind the expense. So if line 34 is zero line 41 will always also be zero.


In the middle of say Feb it would look like:

__Actuals________ROF 2____________ROF 1___________Original Plan____
FEB!$C$34 = 0, FEB!$D$34 = 0, FEB!$E$34 = 126,500, FEB!$F$34 = 125,000
FEB!$C$41 = 0, FEB!$D$41 = 0, FEB!$E$41 = 236,000, FEB!$F$41 = 225,000

MAR!$C$34 = 0, MAR!$D$34 = 0, MAR!$E$34 = 0, MAR!$F$34 = 200,000
MAR!$C$41 = 0, MAR!$D$41 = 0, MAR!$E$41 = 0, MAR!$F$41 = 275,000

The formula would start at FEB!$C$34 and test until it finds something not equal to zero. I say it that way because a negative number is possible.

With the above example the result of the formula would pull 236,000 from Feb + 275,000 from Mar = 511,000

Sorry if this is confusing. I do appreciate your time.

 

[Aladin Akyurek]

I am not able to download and install the HTML maker on this PC so please bear with me. Line 34 is a sum of expense items from other lines above it and line 41 is a driver behind the expense. So if line 34 is zero line 41 will always also be zero.


In the middle of say Feb it would look like:

__Actuals________ROF 2____________ROF 1___________Original Plan____
FEB!$C$34 = 0, FEB!$D$34 = 0, FEB!$E$34 = 126,500, FEB!$F$34 = 125,000
FEB!$C$41 = 0, FEB!$D$41 = 0, FEB!$E$41 = 236,000, FEB!$F$41 = 225,000

MAR!$C$34 = 0, MAR!$D$34 = 0, MAR!$E$34 = 0, MAR!$F$34 = 200,000
MAR!$C$41 = 0, MAR!$D$41 = 0, MAR!$E$41 = 0, MAR!$F$41 = 275,000

The formula would start at FEB!$C$34 and test until it finds something not equal to zero. I say it that way because a negative number is possible.

With the above example the result of the formula would pull 236,000 from Feb + 275,000 from Mar = 511,000

Sorry if this is confusing. I do appreciate your time.

Right. What is your Excel version?

 

[EOAEvan]

mine is 2010 however it needs to be compatible to 2000 for people who it gets sent to.

If the answer only works in 2010 ill take it and just send versions without formulas.

 

[Aladin Akyurek]

mine is 2010 however it needs to be compatible to 2000 for people who it gets sent to.

If the answer only works in 2010 ill take it and just send versions without formulas.

On 2010...

Control+shift+enter, not just enter:

=SUM(IFERROR(INDEX(FEB!C41:G41,MATCH(TRUE,FEB!C34:G34>0,0)),0),IFERROR(INDEX(MAR!C41:G41,MATCH(TRUE,MAR!C34:F34>0,0)),0),IFERROR(INDEX(APR!C41:G41,MATCH(TRUE,APR!C34:G34>0,0)),0))

On all versions, including 2000...

A2, control+shift+enter, not just enter:

=INDEX(FEB!C41:G41,MATCH(TRUE,FEB!C34:G34>0,0))

A3, control+shift+enter, not just enter:

=INDEX(MAR!C41:G41,MATCH(TRUE,MAR!C34:F34>0,0))

A4, control+shift+enter, not just enter:

=INDEX(APR!C41:G41,MATCH(TRUE,APR!C34:G34>0,0))

A5, which is the ultimate result cell:

Either...

=SUMIF(A1:A3,"<"&9.99E+307)

Or...

=SUM(SUMIF(A1:A3,{"<0",">0"}))