Need help with consolidation of data in Excel

Posted on 2012-08-26
Last Modified: 2012-08-26
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.
Question by:Subsun
    LVL 50

    Accepted Solution


    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
    LVL 25

    Assisted Solution

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

    Author Closing Comment

    Great!... Thanks a lot guys, appreciate it!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    VBA filters 2 27
    Simple Calculation for Value of Availablity 5 51
    Excel 2010 question 3 26
    Excel - text to column problem 2 20
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now