Conditional Formatting, or how to flag in Spreadsheets?

[LJR]

I am new to both this forum and to Excel in general. I know Excel is very formula driven and the key to success with Excel is success with formulas.


Right now I am working on a side project and it involves creating a spreadsheet to track info on. I need to be able to create a forumla that will flag all cells when a certain set of criteria are met. An example below.


Lets say I have 5 Devices, named DVC1, DVC2, DVC3, DVC4, and DVC5. Everytime one of these devices needs to be serviced, I enter the information for this service into an Excel spreadsheet; much like a service ticket but in an Excel spreadsheet.


I notice that DVC2 and DVC5 have alot of issues and most of the issues are recurring. How do I set a formula up that will flag/highlight if DVC2 and/or DVC5 has exceeded let's say....7 times that they need repaired, or 7 times they were entered into the spreadsheet?


I tried figuring out the COUNTIF formulas but I can't figure them out for some reason. I add them to the spreadsheet to my desired row but it doesn't highlight anything when I know the criteria is met.


I tried this: =countif(D3:D50, DVC2 DVC5)>7 but nothing happens. DVC's are entered in the D column.


What am I doing wrong? I am entering the formula in the Conditional Formatting.

 

[Luke M]

First, you need to use dollar signs to indicate absolute references (versus relative references). Otherwise, when you copy the formula down, XL will shift the range reference to D4:D51, then D5:G52, etc.


Next, the criteria needs to just be a single value, and you need to use quotation marks to indicate that you are inputting a text string and not a cell reference.


Overall, the conditional format formula should probably be:

=COUNTIF($D$3:$D$50,"DVC2")+COUNTIF($D$3:$D$50,"DVC5")>7

 

[LJR]

Awesome, thanks for the quick reply Luke M!


So the absolutes ($) ensure it does not move the cells it is looking at when I apply the formatting to the row/columns? So basically that is saying that formula will always refer to just D3:D50?


Also the '+' between them allows me to do this for more than one text string? So in theory I could put down as many behind the '=' as long as it is connected by a '+' and at the very end I have my criteria?


Sorry, I have no formal training in Excel, learning as I go. Once I understand it I'll have no issues.


EDIT: Ran a test with that and it is highlighting every cell, not just the ones with either DVC2 or DVC5.

 

[Luke M]

LJR,


Correct, the dollar sign tells XL to not change the reference. Note that you can apply this to the column and/or row.

Learn more: http://www.mrexcel.com/articles/relative-and-absolute-formulas.php


The + was simply to indicate that we are taking the count of DVC2 and adding it to the count of DVC5, since the question was to highlight if the total count of both values exceeded 7.


Yep, I just realized that there is nothing in the formula that relates to the cell currently being formatted. oops.


A better formula would probably be:

=COUNTIF($D$3:$D$50,D3)>7

Note that this formatting should be applied to cell D3. What this does, is the formula looks in D3 (the current cell) and uses that value (lets assume its DVC2). It then does a count in the specified range looking for DVC2's, and if the value is greater than 7, it will highlight the cell.


Now, if you really want to check the total sum of both DVC2 and DVC5, we'll need to take a slightly different route. We'll use the original formula, but we need to add an additional check to see if the current cell is one we're interested in.

Let's set the format rule for D3 to be this:

=AND(OR(D3="DVC2",D3="DVC5"),COUNTIF($D$3:$D$50,"DVC2")+COUNTIF($D$3:$D$50,"DVC5")>7)


Now, the formula checks to see that 2 conditions MUST be met (because of the AND). The count of DVC2 + DVC5 must be greater than 7, and the current cell (D3) must be equal to DVC2 OR DVC5.


Hope that helps explain things better.