Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count column if heading exists
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count data in one column if certain criteria exists in another. | Excel Worksheet Functions | |||
Determining if a value already exists in column(s) | Excel Discussion (Misc queries) | |||
In a table produce an value by column heading and row heading | Excel Worksheet Functions | |||
Determine if Value in column A exists in Column B | Excel Discussion (Misc queries) | |||
Find if value in column A exists in Column B | Excel Worksheet Functions |