where allowed is the named range E5:E9. If the test returns FALSE, the formula returns an empty string (""). For the logical test, we are using the COUNTIF function like this: COUNTIF counts occurrences of the value in B5 in named range allowed (E5:E9). This may seem “backwards” to you, but if you think about it, it makes sense. If the value in B5 is found in the allowed list of values , COUNTIF will return a positive number (in this case 1). If not, COUNTIF will return zero. Excel will evaluate any non-zero number as TRUE, so this works perfectly as the logical test for IF. IF will only return TRUE if the value is found in the allowed list, and, if so, the final result is a check mark (✓). If the value is not found in the allowed list, COUNTIF returns zero, which is evaluated as FALSE. In that case, the final result is an empty string (""), which displays nothing.
With fixed values
The example above shows allowed values in a range of cells, but allowed values can also be hard-coded into the formulas as an array constant like this:
Check mark character (✓)
Inserting a check mark character in Excel can be surprisingly challenging and you will find many articles on the internet explaining various approaches. The easiest way to get the check mark character (✓) used in this formula into Excel is simply to copy and paste it. If you are copying from this web page, paste into the formula bar to avoid dragging in unwanted formatting. You can also copy and paste directly from the attached worksheet. If you have trouble with copy and paste, try this variation. The character itself is Unicode 2713 (U+2713), and can also be entered in Excel with the UNICHAR function like this: So, the original formula can be written like this: Note: the UNICHAR function was introduced in Excel 2013.
Extending the formula
The basic idea in this formula can be extended in many clever ways. For example the screenshot below shows a formula that returns a check mark only when all test scores are at least 65:
The formula in G5 is: The NOT function reverses the result from COUNTIF. If you find this confusing, you can alternately restructure the IF formula like this: In the version of the formula, the logic is more similar to the original formula above. However, we have moved the check mark to the value_if_false argument, so the check mark will appear only if the count from COUNTIF is zero. In other words, the check mark will appear only when no values less than 65 are found. Note: you can also use conditional formatting to highlight valid or invalid input, and data validation to restrict input to allow only valid data.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
