Hi, I have a report that calculates passed, failed, and N/A results. These
are also the titles of the columns. If one of these columns does not exist
then the calculations fail. I would like to know if there is a way to only
calculate these columns if they exist.
My current formula for the completed amount (adds failed, passed and N/A
totals) is

=(SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("Failed",$A$3:$H$3,0)))+SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("N/A",$A$3:$H$3,0)))+SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("Passed",$A$3:$H$3,0))))/(SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("<total",$A$3:$H$3,0))))

This formula needs all three columns to exist otherwise it returns a result
of #N/A
Is there a way to calculate only the columns that exist?

This one works
Test Failed Passed N/A <total
no 1 3 3 1 7
no 2 12 19 0 31
no 3 2 29 5 36


This one will fail because there is no results marked as Failed yet.
Test Passed N/A <total
no 1 3 1 4
no 2 19 0 19
no 3 29 5 34

Any help appreciated.

I'm a bit confused but something like this?
=if(match("failed",a3:h3),formula,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rusty" wrote in message
...
Hi, I have a report that calculates passed, failed, and N/A results. These
are also the titles of the columns. If one of these columns does not exist
then the calculations fail. I would like to know if there is a way to
only
calculate these columns if they exist.
My current formula for the completed amount (adds failed, passed and N/A
totals) is

=(SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("Failed",$A$3:$H$3,0)))+SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("N/A",$A$3:$H$3,0)))+SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("Passed",$A$3:$H$3,0))))/(SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("<total",$A$3:$H$3,0))))

This formula needs all three columns to exist otherwise it returns a
result
of #N/A
Is there a way to calculate only the columns that exist?

This one works
Test Failed Passed N/A <total
no 1 3 3 1 7
no 2 12 19 0 31
no 3 2 29 5 36


This one will fail because there is no results marked as Failed yet.
Test Passed N/A <total
no 1 3 1 4
no 2 19 0 19
no 3 29 5 34

Any help appreciated.

Hi Don,
If the "failed" column does not exist then this formula returns N/A.
This then causes the remainder of the formula to return #N/A

=if(match("failed",a3:h3),formula,0)

Is there a way to make it ignore a portion of the formula if the result is
#N/A?

Thanks for your help.

"Don Guillett" wrote:

I'm a bit confused but something like this?
=if(match("failed",a3:h3),formula,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rusty" wrote in message
...
Hi, I have a report that calculates passed, failed, and N/A results. These
are also the titles of the columns. If one of these columns does not exist
then the calculations fail. I would like to know if there is a way to
only
calculate these columns if they exist.
My current formula for the completed amount (adds failed, passed and N/A
totals) is

=(SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("Failed",$A$3:$H$3,0)))+SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("N/A",$A$3:$H$3,0)))+SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("Passed",$A$3:$H$3,0))))/(SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("<total",$A$3:$H$3,0))))

This formula needs all three columns to exist otherwise it returns a
result
of #N/A
Is there a way to calculate only the columns that exist?

This one works
Test Failed Passed N/A <total
no 1 3 3 1 7
no 2 12 19 0 31
no 3 2 29 5 36


This one will fail because there is no results marked as Failed yet.
Test Passed N/A <total
no 1 3 1 4
no 2 19 0 19
no 3 29 5 34

Any help appreciated.

If "Failed","Passed","N/A" are in columns B to D row 3 (but needn't be
present) then try:

=SUMPRODUCT(($A$4:$A$40="<total")*(($B$3={"Failed ","Passed","N/A"})*($B$4:$B$40)+($C$3={"Failed","Passed","N/A"})*($C$4:$C$40)+($D$3={"Failed","Passed","N/A"})*($D$4:$D$40)))

"Rusty" wrote:

Hi Don,
If the "failed" column does not exist then this formula returns N/A.
This then causes the remainder of the formula to return #N/A

=if(match("failed",a3:h3),formula,0)

Is there a way to make it ignore a portion of the formula if the result is
#N/A?

Thanks for your help.

"Don Guillett" wrote:

I'm a bit confused but something like this?
=if(match("failed",a3:h3),formula,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rusty" wrote in message
...
Hi, I have a report that calculates passed, failed, and N/A results. These
are also the titles of the columns. If one of these columns does not exist
then the calculations fail. I would like to know if there is a way to
only
calculate these columns if they exist.
My current formula for the completed amount (adds failed, passed and N/A
totals) is

=(SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("Failed",$A$3:$H$3,0)))+SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("N/A",$A$3:$H$3,0)))+SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("Passed",$A$3:$H$3,0))))/(SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("<total",$A$3:$H$3,0))))

This formula needs all three columns to exist otherwise it returns a
result
of #N/A
Is there a way to calculate only the columns that exist?

This one works
Test Failed Passed N/A <total
no 1 3 3 1 7
no 2 12 19 0 31
no 3 2 29 5 36


This one will fail because there is no results marked as Failed yet.
Test Passed N/A <total
no 1 3 1 4
no 2 19 0 19
no 3 29 5 34

Any help appreciated.

Thank you very much Toppers. That works great.
Thanks Don for your help as well.


"Toppers" wrote:

If "Failed","Passed","N/A" are in columns B to D row 3 (but needn't be
present) then try:

=SUMPRODUCT(($A$4:$A$40="<total")*(($B$3={"Failed ","Passed","N/A"})*($B$4:$B$40)+($C$3={"Failed","Passed","N/A"})*($C$4:$C$40)+($D$3={"Failed","Passed","N/A"})*($D$4:$D$40)))

"Rusty" wrote:

Hi Don,
If the "failed" column does not exist then this formula returns N/A.
This then causes the remainder of the formula to return #N/A

=if(match("failed",a3:h3),formula,0)

Is there a way to make it ignore a portion of the formula if the result is
#N/A?

Thanks for your help.

"Don Guillett" wrote:

I'm a bit confused but something like this?
=if(match("failed",a3:h3),formula,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rusty" wrote in message
...
Hi, I have a report that calculates passed, failed, and N/A results. These
are also the titles of the columns. If one of these columns does not exist
then the calculations fail. I would like to know if there is a way to
only
calculate these columns if they exist.
My current formula for the completed amount (adds failed, passed and N/A
totals) is

=(SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("Failed",$A$3:$H$3,0)))+SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("N/A",$A$3:$H$3,0)))+SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("Passed",$A$3:$H$3,0))))/(SUMPRODUCT(--(ISNUMBER(SEARCH("<total",$A$3:$A$40))),INDEX($A$ 3:$H$40,,MATCH("<total",$A$3:$H$3,0))))

This formula needs all three columns to exist otherwise it returns a
result
of #N/A
Is there a way to calculate only the columns that exist?

This one works
Test Failed Passed N/A <total
no 1 3 3 1 7
no 2 12 19 0 31
no 3 2 29 5 36


This one will fail because there is no results marked as Failed yet.
Test Passed N/A <total
no 1 3 1 4
no 2 19 0 19
no 3 29 5 34

Any help appreciated.