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.