What is COUNTIFS function in Excel? COUNTIFS function is one of Statistical functions in Microsoft Excel applies criteria to cells across multiple ranges and counts the number of times all criteria are met. Syntax of COUNTIFS function COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) The COUNTIFS function syntax has the following arguments: criteria_range1 Required. The first range in which to evaluate the associated criteria. criteria1 Required.…
Tag: COUNTIFS function
Basic text sort formula in Excel
To dynamically sort text values in alphabetical order, you can use use a formula based on the COUNTIF function. Formula =COUNTIF(range,”<=”&A1) Explanation In the example shown, the formula in C5 is: =COUNTIF(countries,”<=”&B5) where “countries” is the named range B4:B13 How this formula works This formula uses the “greater than or equal to” operator with text, something you might not have tried before.…
Example of COUNTIFS with variable table column in Excel
To use COUNTIFS with a variable table column, you can use INDEX and MATCH to find and retrieve the column for COUNTIFS. See example below: Formula =COUNTIFS(INDEX(Table,0,MATCH(name,Table[#Headers],0)),criteria)) Explanation In the example shown, the formula in H5 is: =COUNTIFS(INDEX(Table1,0,MATCH(G5,Table1[#Headers],0)),”x”) How this formula works First, for context, it’s important to note that you can use COUNTIFS with a regular structured reference like…
Two ways to sum time over 30 minutes in Excel
To sum the total amount of time over 30 minutes, given a set of times that represent duration, you can use the SUMPRODUCT and TIME functions. Alternatively, use SUMIFS and COUNTIFS functions. Formula =SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0))) Explanation In the example shown, the formula in G5 is: =SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0))) where “times” is the named range C5:C14. How this formula works This formula uses the SUMPRODUCT function to…
Count times in a specific range in Excel
This tutorial shows how to Count times in a specific range in Excel using example below. To count times that occur within a certain range, you can use the COUNTIFs function. Formula =COUNTIFS(range,”>=”&start,range,”<“&end) Explanation In the example shown, the formula in E7 is: =COUNTIFS(B5:B11,”>=”&E5,B5:B11,”<“&E6) How this formula works The COUNTIFS function takes one or more criteria, entered as range/criteria pairs.…
Count dates in current month in Excel
This tutorial shows how to Count dates in current month in Excel using example below. To count dates in the current month, you can use a formula based on the COUNTIFS or SUMPRODUCT function as explained below. Formula =COUNTIFS(range,”>=”&EOMONTH(TODAY(),-1)+1, range,”<“&EOMONTH(TODAY(),0)+1) Explanation In the example shown above, the formula in E7 is: =COUNTIFS(dates,”>=”&EOMONTH(TODAY(), -1)+1,dates,”<“&EOMONTH(TODAY(),0)+1) Where “dates” is the named range B5:B104. How this…
Excel Rank if formula Example
This tutorials shows how to rank items in a list using one or more criteria in Excel. To achieve this you can use the COUNTIFS function. Formula =COUNTIFS(criteria_range,criteria,values,”>”&value)+1 Explanation In the example shown, the formula in E5 is: =COUNTIFS(groups,C5,scores,”>”&D5)+1 where “groups” is the named range C5:C14, and “scores” is the named range D5:D14. The result is a rank for each person in…
Highlight duplicate rows in Excel
This tutorial shows how to Highlight duplicate rows in Excel using the example below; Formula =COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1) Explanation Excel contains a built-in preset for highlighting duplicate values with conditional formatting, but it only works at the cell level. If you want to highlight entire rows that are duplicates you’ll need to use your own formula, as explained below. If you want to…
IF with wildcards in Excel
This tutorial shows how to calculate IF with wildcards in Excel using the example below; Formula =IF(COUNTIF(A1,”??-????-???”),””,”invalid”) Explanation The IF function doesn’t support wildcards, but you can combine IF with COUNTIF or COUNTIF to get basic wildcard functionality. In the example shown, the formula in C5 is: =IF(COUNTIF(B5,”??-????-???”),””,”invalid”) How this formula works Unlike several other frequently used functions, the IF function does not…
Break ties with helper column and COUNTIF in Excel
This tutorial shows how to Break ties with helper column and COUNTIF in Excel using the example below; Formula =A1+(COUNTIF(exp_rng,A1)-1)*adjustment Explanation To break ties, you can use a helper column and the COUNTIF function to adjust values so that they don’t contain duplicates, and therefore won’t result in ties. In the example shown, the formula in D5 is: =C5+(COUNTIF($C$5:C5,C5)-1)*0.01 Context Sometimes, when…
Count cells between two numbers in Excel
This tutorial shows how to Count cells between two numbers in Excel using the example below; Formula =COUNTIFS(range,”>=X”,range,”<=Y”) Explanation To count the number of cells that contain values between two numbers in a range of cells, you can use the COUNTIFS function. In the example above X represents the lower boundary, and Y represents the upper boundary of the numbers you…
Count cells that do not contain many strings in Excel
This tutorial shows how to Count cells that do not contain many strings in Excel using the example below; Formula {=SUM(1-(MMULT(–(ISNUMBER(SEARCH(TRANSPOSE (exclude),data))),ROW(exclude)^0)>0))} Explanation To count cells that do not contain many different strings, you can use a rather complex formula based on the MMULT function. In the example shown, the formula in F5 is: {=SUM(1-(MMULT(–(ISNUMBER(SEARCH(TRANSPOSE (exclude),data))),ROW(exclude)^0)>0))} where “data” is the named…
Count cells that do not contain in Excel
This tutorial shows how to Count cells that do not contain in Excel using the example below; Formula =COUNTIF(range,“<>*text*”) Explanation To count the number of cells that do not contain certain text, you can use the COUNTIF function. In the example above “*” is a wildcard matching any number of characters. In the example, the active cell contains this formula:…
Count cells that contain text in Excel
This tutorial shows how to Count cells that contain text in Excel using the example below; Formula =COUNTIF(range,”*”) Explanation To count the number of cells that contain text (i.e. not numbers, not errors, not blank), use the COUNTIF function and a wildcard. “*” is a wildcard matching any number of characters. Do you want to count cells that contain specific text? See this formula…
Count cells between dates in Excel
This tutorial shows how to Count cells between dates in Excel using the example below; Formula =COUNTIFS(range,”>=”&date1,range,”<=”&date2) Explanation To count the number of cells that contain dates between two dates, you can use the COUNTIFS function. In the example shown, F5 contains this formula: =COUNTIFS(dates,”>=”&DATE(E5,1,1),dates,”<=”&DATE(E5,12,31)) This formula counts birthdays in the year 2000, that appear in the range C5:C11. Note:…
Count cells not equal to many things in Excel
This tutorial shows how to Count cells not equal to many things in Excel using the example below; Formula =SUMPRODUCT(–(ISNA(MATCH(data,exclude,0)))) Explanation To count cells not equal to any of many things, you can use a formula based on the MATCH, ISNA, and SUMPRODUCT functions. In the example shown, the formula in cell F5 is: =SUMPRODUCT(–(ISNA(MATCH(data,exclude,0)))) where “data” is the named range B5:B16…
Count if two criteria match in Excel
This tutorial shows how to Count if two criteria match in Excel using the example below; Formula =COUNTIFS(range1,critera1,range2,critera2) Explanation If you want to count rows where two (or more) criteria match, you can use a formula based on the COUNTIFS function. In the example shown, we want to count the number of orders with a color of “blue” and a…
Count items in list in Excel
This tutorial shows how to Count items in list in Excel using the example below; Formula =COUNTIFS(A:A,A1,B:B,B1) Explanation To create a count of the values that appear in in a list or table, you can use the COUNTIFS function. In the example shown, the formula in D5 is: =COUNTIFS(B:B,B5,C:C,C5) How this formula works The COUNTIFS function takes range/criteria pairs, and…
Count numbers by range with COUNTIFS in Excel
This tutorial shows how to Count numbers by range with COUNTIFS in Excel using the example below; Formula =COUNTIFS(range,”>=low”,range,”<=high”) Explanation To count numeric data by range or grouping, you can build a summary table and use COUNTIFS to count values at each threshold. In the example show, we have a list of names and ages and are using the COUNTIFs function…
Count paired items in listed combinations in Excel
This tutorial shows how to Count paired items in listed combinations in Excel using the example below; Formula =COUNTIFS(range,”*”&$item1&”*”,range,”*”&item2&”*”) Explanation To build a summary table with a count of paired items that appear in a list of existing combinations, you can use a helper column and a formula based on the COUNTIFS function. In the example shown the formula in…
COUNTIFS with multiple criteria and OR logic in Excel
This tutorial shows how to COUNTIFS with multiple criteria and OR logic in Excel using the example below; Formula =SUM(COUNTIFS(range,{“red”,”blue”,”green”})) Explanation To count with multiple criteria and OR logic, you can use the COUNTIFS function with an array constant. In the example shown, the formula in H6 is: =SUM(COUNTIFS(D4:D11,{“complete”,”pending”})) How this formula works By default, the COUNTIFS function applies AND logic.…
Summary count by month with COUNTIFS in Excel
This tutorial shows how to work Summary count by month with COUNTIFS in Excel using the example below; Formula =COUNTIFS(dates,”>=”&A1,dates,”<“&EDATE(A1,1)) Explanation To create a summary count by month, you can use the COUNTIFS function and the EDATE function with two criteria. In the example shown, the formula in G5 is: =COUNTIFS(dates,”>=”&F5,dates,”<“&EDATE(F5,1)) How this formula works In this example, we have a list…
Two-way summary count with COUNTIFS in Excel
This tutorial shows how to work Two-way summary count with COUNTIFS in Excel using the example below; Formula =COUNTIFS(range1,critera1,range2,critera2) Explanation To build a two-way summary count (i.e. summarizing by rows and columns) you can use the COUNTIFS function. In the example shown, the formula in G5 is: =COUNTIFS(dept,$F5,class,G$4) How this formula works The COUNTIFS function is designed to count things based…
Summary count with percentage breakdown in Excel
This tutorial shows Summary count with percentage breakdown in Excel using the example below; Formula =COUNTIF(range,criteria)/COUNTA(range) Explanation To generate a count with a percentage breakdown, you can use the COUNTIF or COUNTIFS function, together with COUNTA. In the example shown the formula in H4 is: =COUNTIF(category,F4)/COUNTA(category) How this formula works COUNTIF is set up to count cells in the named…
Summary count of non-blank categories in Excel
This tutorial shows Summary count of non-blank categories in Excel using the example below; Formula =COUNTIFS(range1,criteria1,range2,”<>”) Explanation To build a summary count of non-blank categories, you can use the COUNTIFS function. In the example show, the formula in G6 is: =COUNTIFS($B$5:$B$11,F6,$C$5:$C$11,”<>”) How this formula works This example relies on core COUNTIFS functionality. To create a summary count by building, we…
Excel Frequency Function Example
The FREQUENCY function in Excel calculates how often values occur within the ranges you specify in a bin table. You can also use the COUNTIFS function to create a frequency distribution. 1. First, enter the bin numbers (upper levels) in the range C4:C8. 2. Select the range D4:D9 (extra cell), enter the FREQUENCY function shown below and finish by pressing CTRL + SHIFT + ENTER.…
Not Equal To ‘<>‘ operator in Excel
In Excel, <> means not equal to. The <> operator in Excel checks if two values are not equal to each other. Let’s take a look at a few examples. 1. The formula in cell C1 below returns TRUE because the text value in cell A1 is not equal to the text value in cell B1. 2. The formula in cell C1…
Count and Sum Functions in Excel
The most used functions in Excel are the functions that count and sum. COUNT function is one of the STATISTICAL functions while SUM function is one of the Math and Trig functions. You can count and sum based on one criteria or multiple criteria. Count To count the number of cells that contain numbers, use the COUNT function. Countif To count cells based on one criteria (for…