Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to create running total in an Excel Table
  • How to Create Thermometer Chart in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • Excel Pie Chart
  • Conditional Formatting Data bars Examples in Excel

References

  • How to get last column number in range in Excel
  • How to get first row number in range in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • Vlookup Examples in Excel
  • Left Lookup in Excel

Data Validations

  • Excel Data validation exists in list
  • Excel Data validation with conditional list
  • Excel Data validation only dates between
  • Excel Data validation date in next 30 days
  • Excel Data validation require unique number

Tag: COUNTIFS function

Two ways to sum time over 30 minutes in Excel

by

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 …

Continue Reading

Count dates in current month in Excel

by

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” …

Continue Reading

Count times in a specific range in Excel

by

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 …

Continue Reading

Excel Rank if formula Example

by

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 …

Continue Reading

Highlight duplicate rows in Excel

by

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 …

Continue Reading

IF with wildcards in Excel

by

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, …

Continue Reading

Not Equal To ‘<>‘ operator in Excel

by

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. …

Continue Reading

Excel Frequency Function Example

by

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 …

Continue Reading

Summary count by month with COUNTIFS in Excel

by

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 …

Continue Reading

Break ties with helper column and COUNTIF in Excel

by

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 …

Continue Reading

Two-way summary count with COUNTIFS in Excel

by

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 …

Continue Reading

Summary count with percentage breakdown in Excel

by

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 …

Continue Reading

Summary count of non-blank categories in Excel

by

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 …

Continue Reading

COUNTIFS with multiple criteria and OR logic in Excel

by

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 …

Continue Reading

Count paired items in listed combinations in Excel

by

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 …

Continue Reading

Count numbers by range with COUNTIFS in Excel

by

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 …

Continue Reading

Count items in list in Excel

by

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 …

Continue Reading

Count if two criteria match in Excel

by

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 …

Continue Reading

Count cells that do not contain many strings in Excel

by

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 …

Continue Reading

Count cells that do not contain in Excel

by

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 …

Continue Reading

Count cells that contain text in Excel

by

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 …

Continue Reading

Count cells not equal to many things in Excel

by

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)))) …

Continue Reading

Count cells between two numbers in Excel

by

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 …

Continue Reading

Count cells between dates in Excel

by

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 …

Continue Reading

Count and Sum Functions in Excel

by

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 …

Continue Reading

COUNTIFS function: Description, Usage, Syntax, Examples and Explanation

by

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 …

Continue Reading

Example of COUNTIFS with variable table column in Excel

by

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 …

Continue Reading

Basic text sort formula in Excel

by

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 …

Continue Reading

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Invoice status with nested if in Excel
  • OR function Examples in Excel
  • SWITCH function example in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • OR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • How to get year from date in Excel
  • Display Date is same month in Excel
  • Assign points based on late time in Excel
  • Get date from day number in Excel
  • Convert decimal hours to Excel time

Grouping

  • How to randomly assign data to groups in Excel
  • Group times into unequal buckets in Excel
  • Group numbers with VLOOKUP in Excel
  • Group arbitrary text values in Excel
  • Running count group by n size in Excel

General

  • Transpose: Switch ‘Rows to Columns’ or ‘Columns to Rows’ in Excel
  • List worksheet index numbers in Excel
  • AutoRecover file that was never saved in Excel
  • How to calculate percent change in Excel
  • Spell Check in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning