Count a condition in a group of cells and flag that result on a cell in another worksheet

Posted on 2011-10-20
Last Modified: 2012-05-12
Hi All, I have a workbook that has summary list of all our branches and the date they were auditted on one sheet. On another sheet in the same work book I have the issues obtained from the audits which I can then pivot. I have a "completed' column that is filled 'Y' or 'N' from a drop list in the data sheet for each audit item.  In Excel 2003 is it possible to flag a cell on the summary sheet i.e. 'Complete' or 'Not Complete' or colour the cell depending on the status of the 'completed' column on the data sheet. The result I am hoping for is that while items are outstanding the branch status will be indicated and this will alter when there are no more outstanding items
Question by:Craig Anderson
    LVL 41

    Accepted Solution

    Yes, you can.

    On the summary sheet, you'll need a formula that compares two data points:

    1.  The count of items in the data sheet that has the hi-level audit item, and
    2.  The count of items in the datasheet at the hi-level autit item where the issue/compleded column has been marked Y

    When you compare those two data points, if they are equal, then your audit item is "Complete".  Otherwise, its "Not Complete".

    Its simple enough to either mark complete, or use conditional formatting, or both.

    See attached simple mockup demonstrating this (using rows 2-5000 on the detail sheet:

    LVL 41

    Expert Comment

    Note, when you have the formulas working for YOUR example (feel free to upload a brief, non-sensitive mockup, if you need help), then you'll probably want to put some error checking in your formulas...


    =IF(ISERROR(the formula),"N/A - or something like Not Found",the formula)


    LVL 27

    Assisted Solution

    by:Glenn Ray
    dlmille's/Dave's example works, but here is another example that I think more-closely follows your description.

    The SUMPRODUCT function is used here also, but in a slightly different manner.  The range
          'Audit Details'!$A$1:$C$69
    could assigned a range name to make formulas more readable.  

    LVL 50

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Third Sunday of the Month 10 50
    Import csv files to MS SQL 5 38
    Excel 2013 Drop down help with data 3 37
    Min Month 11 31
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    745 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

    20 Experts available now in Live!

    Get 1:1 Help Now