Hi,

I have a problem to count number of active defects if column names are dynamic(sometimes column STATE can be in column E, sometimes in column D etc. depends on created query in TFS). To make long story short: I have 2 sheets:

1. "Defect_list" - list of all defects with ID, title, severity, state, area path etc..
2. "Defect_report" - pie charts and summary of defects (divided by severity/state, area path etc.)

If columns in "Defect_list" sheet weren't dynamic, solution in "Defect_report" would be easy:

=COUNTIFS(Defect_list!$C$3:$C$500, "=" &$A11, Defect_list!$E$3:$E$500, "<>Closed")

But as mentioned before, columns might be dynamic, so I created formula in "Defect_report" sheet to find which column represents STATE and SEVERITY:

• B2(state) =IF(Defect_list!$A$2="State","A",IF(Defect_list!$B$2="State","B",IF(Defect_list!$C$2="State","C",IF(Defect_list!$D$2="State","D",IF(Defect_list!$E$2="State","E",IF(Defect_list!$F$2="State","F"))))))
• B3 cell(severity) = =IF(Defect_list!$A$2="Severity","A",IF(Defect_list!$B$2="Severity","B",IF(Defect_list!$C$2="Severity","C",IF(Defect_list!$D$2="Severity","D",IF(Defect_list!$E$2="Severity","E",IF(Defect_list!$F$2="Severity","F"))))))

So now I get value eg "E"(column in Defect_list with State values) and "C"(column in Defect_list with Severity values).

I don't know how to modify COUNTIFS formula. I attached particular excel file for easier understanding

Thank you very much for help in advance! Alex