COUNTIFS with dynamic coluumns in other sheet
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:
- "Defect_list" - list of all defects with ID, title, severity, state, area path etc..
- "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")
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"))))))
Attachment | Size |
---|---|
UAT-Defect_report_test.xlsx | 31.84 KB |
Recent comments
5 years 34 weeks ago
6 years 20 weeks ago
6 years 32 weeks ago
6 years 35 weeks ago
6 years 36 weeks ago
6 years 42 weeks ago
6 years 50 weeks ago
6 years 50 weeks ago
6 years 50 weeks ago
6 years 50 weeks ago