The COUNTIF function

Welcome back to our regular blog of Excel functions from A to Z. Today we look at the TELL function.

The COUNTIF function

This function counts the number of cells that meet a particular criteria; for instance, to count the number of times a particular city appears in a customer contact list.

The TELL The function uses the following syntax to operate:

The TELL The function has the following arguments:

• encompass: this is necessary and represents the encompass from which you want to count cells that meet the specified criterion
• criterion: the condition that must be met.

It should also be noted that:

• TELL ignore case in text strings. Criterion It does not distinguish between upper and lower case, hence “Red” Y “RED” will match the same cells
• Wildcards are allowed. the characters, the question mark (?) And the asterisk can be used incriterion
• . The question mark matches any character, while an asterisk matches any sequence of characters. If you really want to find a question mark or asterisk, use la tilde (~) in front of the required character TELL it's pedantically accurate. Therefore, make sure your data does not contain bad characters. In particular, when counting text values, make sure the text does not contain leading spaces, excessive or late, inconsistent use of straight quotes / curled or characters not printed. The CLEANSED removes all non-printable characters and PRUNE
• (a useful text manipulation function that removes excess spaces) functions can eradicate most of these problems range names can be used with TELL
• if required encompass can be a range in the same worksheet, a different worksheet or even a range in another workbook. But nevertheless, if it refers to a second book or a later book, these books must be open for TELL to work as planned, on the contrary #VALUE!
• Will be returned wrong value may be returned for long strings. The TELL The function returns incorrect results when you try to use it to match strings longer than 255 characters. But nevertheless, there is a workaround available. You can use the CONCATENARfunction or concatenate (Y ) operator, p.ej
• = CONTAR.SI (A1: A7, “long chain” Y “another long chain”) if no value is returned where one is expected, check if the criterion the argument must be enclosed in quotes, p.ej

“> = 14”.

Please, see my example below: It should also be noted that the TELL

1. The function will not count cells based on cell background or font color. But nevertheless, Excel supports user-defined functions (UDF) Using Microsoft Visual Basic for Applications (VBA). To finish, here is an example how you can count the number of cells with a specific cell color using VBA. Open Microsoft Excel, and then press ALT + F11
2. to display the Visual Basic Editor window

3. end function
4. Close the VBE window and return to Excel Test the UDF with sample data
5. (see example below) In cellD3

• = CountCcolor (data_range, criteria)

6. (note that UDFs are not automatically capitalized like Excel's built-in functions) In the range_data argument, select cells
7. C2: C31 In Criteria argument, select cell
8. F1 PressENTEROKAY . In cell F2

9. the result is 6. It means the number of cells with this color is six You can also try another color. Change the color in the cell F1
10. as necessary If you save this example as a workbook, say it Count cell color (Yes, it's true, It seems that I am defending the American spelling!), save it as a Excel add-in(.xlam
11. ) format
12. Thus, this UDF can be used in other files. Open Excel on the computer you want to install the add-in on. Later, open the ‘Plugins’ dialog’ clicking on ‘Plug-in’ in the 'Developer' tab’

13. In the dialog “Accessories”, Click the button “Examine …” to display the dialog “Examine”.
14. Browse to the file location where the plugin file is saved. Choose the file and then click 'Open’
15. In the dialog “Accessories”, make sure the plugin checkbox is checked. Then click OK

The Count Cell Color UDF has now been installed and is ready to use.

Soon we will continue with our functions from A to Z of Excel. A full page of the feature articles can be found.here