I need to consolidate data from the cells marked in Yellow to the data in Green (Result). I want to consolidate how many critical tests failed by agent. Critical or Non-Critical is identified from table blue.
I know it’s confusing, I will try to explain.. :-)
Test1+Pri1 is critical as per blue table so if there a condition in data where the agent gets a ‘Failed’ for this condition then It should be added to result.
Test3+Pri1 is Non-Critical as per blue table so if there a condition in data where the agent gets a ‘Failed’ for this condition then It should NOT be added to result.
Result should be dynamic, means any values from Yellow table or blue table changes then the result should change accordingly.
I have attached sample sheet, please check and let me know how I can resolve this query. Many thanks in advance.