The function can be used as an array formula if you need to calculate the number of cells with data that satisfies several criteria at once.If the criterion is passed as an argument to a reference to an empty cell or an empty string "", the result of the calculation for any range of cells will be the numeric value 0 (zero).For example, the =COUNTIF(A1:A2,"Joni") function returns the value 2 if the cells "joni" and "Joni" are written in cells A1 and A2, respectively. If a text string is specified as a criterion, it should be noted that the case of characters does not matter.For calculations, we use the expression =COUNTIF(B3:B200,"*table*")+COUNTIF(B3:B200,"*chair*"). For example, in the table "Furniture" you need to find the number of tables and chairs. When counting the number of occurrences in a range in accordance with two different conditions, a range of cells can be considered as a set containing two or more disjoint subsets.The condition can be a boolean expression, a numeric value, a text string, a Date value, a cell reference. Criteria - the condition according to which the calculation of the number of matches in the considered range is performed.Range - a required argument that accepts a link to one or several cells in which the number of matches with the specified criterion is required to be determined.The function has the following syntax entry: To determine the number of pages viewed by the users default and user_1, we write the formula:Īs a result of the calculation we get: Features of using the COUNTIF function in Excel The total sum of such values, calculated by the SUM function, corresponds to the number of unique occurrences, that is, the number of users on the site. The expression 1 / COUNTIF(A3:A20,A3:A20) returns an array of fractional numbers 1 / number_notes, for example, for a user with the nickname sam, this value is 0.25 (4 occurrences). Since each user has his own unique identifier in the database (Id), let's calculate the number of site users per day using the following array formula and to calculate it, press the key combination Ctrl + Shift + Enter: Determine the number of users of the site per day, as well as how many times a day users came to the site with default and user_1 logins. The Excel spreadsheet stores data on page views of the site by the day. D2:D5 is the range of cells containing the criteria for counting the number of matches.Īs a result, we get the table: Statistical analysis of attendance using the account function in ExcelĮxample 3.According to the results of the exams, it is necessary to create a table containing data on the number of students who passed the subject for 5, 4, 3 points, respectively, as well as those who did not pass the subject.įirst select the cells E2:E5, enter the formula below: “ERASERS”) will match the same cells and return the same value.Counting the number of a particular cell value in Excel providedĮxample 2. “erasers”) and an upper case string (e.g. Criteria that include a lower case string (e.g. One behavior of COUNTIF functions to be aware of is that it disregards upper and lower case strings.
COUNTIF EXCEL HOW TO
RELATED: How to Use the FREQUENCY Function in Excel =COUNTIF(A2:A5,"long string"&"another long string")
COUNTIF EXCEL FULL
You can avoid typing out the full function by simply using an ampersand (&), as demonstrated below. To fix this, use the CONCATENATE function to match strings longer than 255 characters. If your COUNTIF formula uses criteria matched to a string longer than 255 characters, it will return an error. Note, this formula uses COUNTIF twice since there are multiple criteria being used, with one criterion per expression. This counts the number of erasers and pens. the total of pens and erasers in our inventory chart), you may use the following formula. To count the number of multiple values (e.g. RELATED: How to Count Colored Cells in Microsoft Excel How to Count the Number of Multiple Values Or you can use operators with numbers inside of quotes to determine results, like "<100" to get a count of all numbers less than 100. You can also count the number of times a specific number appears by putting the number in the criteria argument without quotes. So in our example, we want to count the number of times “Pens” appears in our inventory, which includes the range G9:G15.