Jak używać funkcji COUNTIFS w Excelu
| Polskie | Angielski |
|---|---|
| LICZ | COUNTIFS |
The COUNTIFS function in both Google Sheets and Microsoft Excel enables users to count the number of cells that meet multiple criteria. This is particularly useful for complex queries where it is essential to count cells based on various conditions.
Syntax Description
The COUNTIFS function uses the following syntax:
COUNTIFS(range1, criteria1, [range2, criteria2, ...])
Where:
- range1, range2, … – are the ranges to be checked.
- criteria1, criteria2, … – are the criteria that the ranges must meet. Textual criteria should be enclosed in quotes, for example, „>=10”.
It is important to note that all specified ranges must have the same number of rows and columns.
Usage Examples
Consider the following example table:
| Name | Age | Salary |
|---|---|---|
| Alex | 28 | 50000 |
| Maria | 32 | 54000 |
| Nikolay | 25 | 48000 |
Here’s how you would use COUNTIFS to count the number of people aged between 26 to 35 years with a salary over 50000:
=COUNTIFS(B2:B4, ">=26", B2:B4, "50000")
Practical Examples
Example 1: Counting the Number of Students by Criteria
Suppose we have a list of students with their scores in different subjects as shown in the table. We need to determine how many students scored a 3 or higher in Mathematics and a 4 or higher in Literature.
| Student | Mathematics | Literature |
|---|---|---|
| Ivan | 4 | 3 |
| Sofia | 2 | 5 |
| Dmitry | 3 | 4 |
=COUNTIFS(B2:B4, ">=3", C2:C4, ">=4")
In this case, the function returns 1 because only Dmitry meets both conditions.
Example 2: Counting the Number of Employees in Departments with a Specified Salary
If we have a table with data on employees, their departments, and salaries, and we want to find out how many employees in the sales department have a salary over 3000 euros, we can calculate it as follows:
| Employee | Department | Salary |
|---|---|---|
| Anna | Sales | 3200 |
| Boris | Marketing | 2900 |
| Olga | Sales | 3500 |
=COUNTIFS(B2:B4, "Sales", C2:C4, ">3000")
The result of this function is 2, indicating that two employees from the sales department earn more than 3000 euros.
These examples illustrate how effectively the COUNTIFS function can be used for data analysis in tables. Accurate criteria specification and counting according to these help solve complex analytical tasks efficiently.
Więcej informacji: https://support.microsoft.com/pl-pl/office/licz-warunki-funkcja-dda3dc6e-f74e-4aee-88bc-aa8c2a866842