Solved

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

Posted on 2011-10-20
204 Views
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
0
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:

AuditWorkbook-r1.xls
0

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

E.g.,

Cheers,

Dave
0

LVL 27

Assisted Solution

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.

Branch-Audit-Example.xls
0

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

## Featured Post

### Suggested Solutions

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.