COUNTIFS function

Excel » Functions » Statistical » COUNTIFS function »

Highlight unique distinct records

The image above demonstrates a Conditional Formatting formula that highlights unique distinct records. This means that the first instance of […]

Extract unique distinct records from two data sets

The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell […]

Highlight cells based on coordinates

The picture above shows Conditional Formatting highlighting cells in cell range F3:Y22 based on row and column values in column B […]

SMALL function – multiple conditions

This article demonstrates how to sort numbers from small to large using a condition or criteria, I will show how […]

Match two columns

This article demonstrates formulas that match two conditions in a column each and return another value on the same row […]

IF function with AND function – multiple conditions

The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. […]

Count unique distinct values based on a condition

This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

Extract a list of alphabetically sorted duplicates based on a condition

The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]

Find empty dates in a set of date ranges

The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

Search for a sequence of cells based on wildcard search

This article demonstrates array formulas that perform a wildcard search based on a sequence of values. The formulas return the […]

Search for a sequence of values

This article demonstrates array formulas that identify two search values in a row or in a sequence. The image above […]

How to group items by quarter using formulas

This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items […]

Compare two lists and filter unique values where the sum in one column doesn’t match the other column

How would you figure out an unique list where the sum of in one column doesn't match the other column? […]

Merge tables based on a condition

This article demonstrates techniques on how to merge or combine two data sets using a condition. The top left data […]

Prevent duplicate records in a worksheet

This article demonstrates how to set up Data Validation in order to control what the Excel user is allowed to […]

Assign records unique random text strings

This article demonstrates a formula that distributes given text strings randomly across records in any given day meaning they may […]

How to use the COUNTIFS function

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

Lookup using multiple conditions

Debraj Roy asks: Hi Oscar, I failed to search for "Ask" Section.. so submitting my query here.. Apologize for hijacking […]

Working with three relational tables

I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers […]

Extract unique distinct values from a relational table

In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

Lookup multiple values across columns and return a single value

This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]

Unique distinct records sorted based on count or frequency

This article demonstrates how to sort a table based on count meaning the formula counts each record and returns a […]

Auto populate a worksheet

Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]

Working with Conditional Formatting formulas

In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]

Watch schedule that populates vacation time

This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]

Label groups of duplicate records

Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]

Filter unique distinct records using criteria

This blog post describes how to filter unique distinct records that meet a given condition in an Excel defined Table. This article […]

Count entries based on date and time

Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]

Remove common records between two data sets

This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]

How to compare two data sets

This article demonstrates how to quickly compare two data sets in Excel using a formula and Excel defined Tables. The […]

Filter unique distinct records with a condition

Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]

How to highlight differences in price lists

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

Filter duplicate records

This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.

Filter overlapping date ranges

This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]

Compare tables: Filter records occurring only in one table

In this example we are going to use two lists with identical columns, shown in the image above. It is […]

Filter shared records from two tables

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

Highlight duplicate columns

This article describes how to highlight duplicate records arranged into a column each, if you are looking for records entered […]

Extract duplicate records

This article describes how to filter duplicate rows with the use of a formula.  It is, in fact, an array […]

Highlight duplicate records

This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]

Create numbers based on numerical ranges

The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]

Extract dates from overlapping date ranges

The array formula in cell B3 extracts overlapping dates based on the date ranges in column D and E. Array […]

Convert date ranges into dates

The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

List dates outside specified date ranges

The Excel defined table contains start and end dates for each date range in cell range A3:B10. Cell B13 is […]

Filter duplicates within same date, week or month

The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]

Remove duplicates based on date

Question: Column B has dates Column C as data B5 : 1/1/2010 : 5000 B6 : 2/1/2010 : 4000 B7 […]

Match two criteria and return multiple records

This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

VLOOKUP of three columns to pull a single record

Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Answer: Array formula in […]

Lookup two index columns

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

Create a list of duplicates where adjacent cell value meets a condition

Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]

Calculate machine utilization

Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]

Extract unique distinct records based on a criterion

In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]

Filter unique distinct records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

Find closest value

Table of Contents Find closest value How to enter an array formula Find closest value - Excel 365 Find closest […]