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

Data Analysis

  • Excel Frequency Function Example
  • Excel Bar Chart
  • How to create a Histogram in Excel
  • Use Data Form to input, edit and delete records in Excel
  • How To Create Frequency Distribution in Excel

References

  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • How to create dynamic named range with OFFSET in Excel
  • Last row number in range
  • How to get last row in mixed data with blanks in Excel
  • Count rows that contain specific values in Excel

Data Validations

  • Excel Data validation allow weekday only
  • Excel Data validation date in next 30 days
  • Excel Data validation unique values only
  • Prevent invalid data entering in specific cells
  • Excel Data validation date in specific year

Tag: COUNTIFS function

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Not Equal To ‘<>‘ operator in Excel
  • SWITCH function example in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel

Date Time

  • Add workdays no weekends in Excel
  • Convert text to date in Excel
  • How to calculate next day of week in Excel
  • How to calculate percent of year complete in Excel
  • Convert date to Julian format in Excel

Grouping

  • Group numbers at uneven intervals in Excel
  • Group times into 3 hour buckets in Excel
  • Map text to numbers in Excel
  • Map inputs to arbitrary values in Excel
  • Categorize text with keywords in Excel

General

  • Automatically fill series of cells in Excel using AutoFill
  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • How to fill cell ranges with random number from fixed set of options in Excel
  • How to calculate percentage discount in Excel
  • How to get Excel workbook path only
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning