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.
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.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

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


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


copy across and down.

See attached.

cheers, teylyn

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

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

Start your 7-day free trial
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.
SubsunAuthor Commented:
Great!... Thanks a lot guys, appreciate it!
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.

All Courses

From novice to tech pro — start learning today.