Computers, Software
"ACCOUNTS" in Excel: examples, description
The "ACCOUNT" function is one of the most frequently used functions in Microsoft's powerful Excel spreadsheet processor. What is it for? This is clear from its name. It will help any user who asks the question "How to count cells in Excel according to the specified criteria?", Get the desired numbers.
Function Arguments
This function takes two required arguments. The first of these is the range in which the number of cells satisfying the condition will be counted.
The second argument is a condition that can be specified in the form of a number or a word, and also in the form of a condition.
Prepare a range of data and on its basis, consider how the specific functions of "ACCOUNT" in Excel look like. Let's say we have a list of vegetables and fruits stored in the warehouse with a list of their purchase price, sales surcharge and the date of purchase. We start working with tables in Excel - we transfer the following data to the worksheet:
A | AT | FROM | D | |
1 | Fruits vegetables | Purchase price (kg) | Extra charge (%) | Purchase date |
2 | Apples Gold | 74.5 | 28 | 05/01/2017 |
3 | Apples of Fuji | 69.8 | 50 | 05/16/2012 |
4 | Bananas | 41 | 75 | 05/04/2017 |
5 | Banana mini | 74.5 | 42 | 05/14/2012 |
6th | Apples glaster | 54.2 | 34 | 05/15/2017 |
7th | Cucumber chinese | 64 | 17th | 05/07/2017 |
8 | Cucumber Pollinated | 110.6 | 28 | 05/16/2012 |
9 | Cucumber neopylyaemy | 132.9 | 28 | 05/01/2017 |
Important addition
The second argument of the function is a condition. If we want to specify the number or address of a cell as an argument, we can write directly. If we want to build a complex condition with comparison signs, search for text or a specific date, the argument must be enclosed in quotation marks. A little later, it will also be considered how to combine various functions in the condition using the ampersand sign.
Simple conditions for the function
The data set introduced in the previous section will help you understand the function "ACCOUNTS" in Excel. The examples discussed in the current section will be fairly simple, but they are fundamental, and it is necessary to consider them before proceeding with more complex ones.
In the tabular form we reduce the formulas, the results of their implementation, as well as write a brief explanatory commentary. All answers are based on the tabular data entered above and are met on the date 16/05/2017.
Example | Answer | A comment |
= COUNTRIES (B2: B9, 74.5) | 2 | Identifies how many goods in the warehouse are purchased at a price of 74.5 |
= COUNTRIES (A2: A9, A2) | 1 | If this formula is "stretched" to the end of the table, we will see whether we have duplicates for the name of goods (if there are no duplicates) |
= COUNTRIES (D2: D9; TODAY ()) | 2 | Number of cells with goods that have arrived to the warehouse today (using the system date of the computer) |
= COUNTRIES (C2: C9; "") | 0 | Counts the number of blank cells in the range. Attention! If there is a space in the cell, it is not considered empty |
= COUNTRIES (B2: B9; "<55") | 2 | The number of cells with a value of less than 55. Similarly, a comparison is made more ">" and is not equal to "<>" |
= COUNTRIES (В2: В9; "<55") + COUNTRIES (В2: В9; "> 100") | 4 | The total number of cells found by a combination of two conditions |
Wildcards
So-called wildcards are widely used with the function "ACCOUNTS" in Excel. Examples of wildcards:
Sign | Description |
* | Text (any number of characters) |
? | Text (the number of characters corresponds to the number of question marks). Works only with text data! If there is not a single letter in the cell, it will always show 0 |
Full work with tables in Excel and analysis of the data stored in them is practically impossible without the use of wildcards.
Conditions for a function with wildcards. Combining functions
Combining with other functions to create the necessary condition occurs by connecting the logical condition ("more", "less", "not equal to") and the desired function by the sign "ampersand" (&).
In the tabular form we will consider more complex examples of using the function "ACCOUNTS".
Example | Answer | A comment |
= COUNTRIES (A2: A9, "apples") | 0 | The number of cells containing only the word "Apples" (insensitive to the register) and all |
= COUNTRIES (A2: A9; "apples *") | 3 | The number of cells starting with the word "Apples" and any ending |
= COUNTRIES (A2: A9; "* D *") | 3 | Number of cells containing the letter "i" in any part of the text. It is often used to search for and further replace "i" with "and" and "e" with "e" |
= COUNTRIES (A2: A9; "??????") | 1 | Goods with a name of only 6 characters in length |
= COUNTRIES (D2: D9; "<" & (TODAY () - 10)) | 3 | Number of cells with purchase date older than ten days |
= COUNTRIES (C2: C9; ">" & AVERAGE (C2: C9)) | 3 | The number of cells with the value of the sales premium is greater than the average |
At this point, we end up looking at the "ACCOUNT" function in Excel.
The examples contained in the article will help you understand the basic principles of constructing and combining the conditions used as an argument to a function and apply them correctly in practice.
Similar articles
Trending Now