Posted to microsoft.public.excel.misc
|
|
Count column if heading exists
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.
|