Need help with consolidation of data in Excel

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.
Sample.xlsx
LVL 40
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Microsoft MVP ExcelCommented:
Hello,

this will be easiest if you can add a few columns to the yellow table, which will identify the criticality factor.

F1 to H1 will be the labels for Test1 to Test3.
F2 will be a formula

=INDEX(\$B\$14:\$F\$16,MATCH(C\$1,\$A\$14:\$A\$16,0),MATCH(\$A2,\$B\$13:\$F\$13,0))

copy across to H2 and copy down.

Now you have all the data you need to construct the results table. In the green table, cell B21 enter

=COUNTIFS(\$B\$2:\$B\$11,#REF!,F\$2:F\$11,"critical",C\$2:C\$11,"failed")

copy across and down.

See attached.

cheers, teylyn
Sample.xlsx
0

Experts Exchange Solution brought to you by ConnectWise

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
There possibly a better way to do this ... but this is my approach.  Updated sample sheet attached.
I added extra values in the cells H2:J11 ... they can be hidden but I left them visible for you.  They use an if() plus hlookup() to test whether the test failed and was critical ... if so ... count as 1 else 0.
In the green cells I added a sumproduct() to total the 1's from the new cells where the name matches per test.
Sample-Updated.xlsx
0

Author Commented:
Great!... Thanks a lot guys, appreciate it!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.