## 'SUMPRODUCT function' category

Date and Time Functions – N to Z

Table of Contents How to use the NETWORKDAYS function How to use the NETWORKDAYS.INTL function How to use the NOW […]

Table of Contents How to use the NETWORKDAYS function How to use the NETWORKDAYS.INTL function How to use the NOW […]

How to do tiered calculations in one formula

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]

Count cells with text

Table of Contents Count cells with text Count cells with text excluding cells containing a space character Count text values […]

Table of Contents Count cells with text Count cells with text excluding cells containing a space character Count text values […]

Count rows containing data

Table of Contents Count rows with data Count non-empty rows Count cells between two values Count cells based on a […]

Table of Contents Count rows with data Count non-empty rows Count cells between two values Count cells based on a […]

Count cells containing text from list

Table of Contents Count cells containing text from list Count entries based on date and time 1. Count cells containing […]

Table of Contents Count cells containing text from list Count entries based on date and time 1. Count cells containing […]

If cell contains multiple values

This article demonstrates formulas that perform a partial match for a given cell using multiple strings specified in cells F2 […]

This article demonstrates formulas that perform a partial match for a given cell using multiple strings specified in cells F2 […]

AVERAGE ignore blanks

Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]

Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]

SUMPRODUCT and nested IF functions

I have demonstrated in a previous postÂ how to simplify nested IF functions, in this article I will show you how […]

I have demonstrated in a previous postÂ how to simplify nested IF functions, in this article I will show you how […]

SUMPRODUCT if not blank

This article describes ways to work with the SUMPRODUCT function and blanks, error values, N/A# errors. Table of Contents SUMPRODUCT […]

This article describes ways to work with the SUMPRODUCT function and blanks, error values, N/A# errors. Table of Contents SUMPRODUCT […]

SUMPRODUCT and IF function

You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. […]

You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. […]

SUMPRODUCT – multiple criteria

This article demonstrates ways to sum values based on criteria. Table of Contents SUMPRODUCT - based on a list of […]

This article demonstrates ways to sum values based on criteria. Table of Contents SUMPRODUCT - based on a list of […]

How to use the COUNTIF function to count not blank cells

The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]

The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]

How to create running totals

This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]

This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]

List all permutations with a condition

I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 […]

I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 […]

Count identical values if they are on the same row

This article describes a formula that counts values in two columns if they are duplicates on the same row. What's […]

This article describes a formula that counts values in two columns if they are duplicates on the same row. What's […]

Sum numerical ranges between two numbers

This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]

This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]

Working with overlapping date ranges

This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]

This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]

How to calculate overlapping time ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

Prevent overlapping date and time ranges using data validation

The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]

The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]

Compare the performance of your stock portfolio to S&P 500 using Excel

Table of Contents Compare the performance of your stock portfolio to S&P 500 Tracking a stock portfolio in Excel (auto […]

Table of Contents Compare the performance of your stock portfolio to S&P 500 Tracking a stock portfolio in Excel (auto […]

Excel calendar

Table of Contents Excel monthly calendar - VBA Â Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

Table of Contents Excel monthly calendar - VBA Â Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

Count groups of repeated values per row

Joe asks: I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0". These […]

Joe asks: I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0". These […]

Dependent drop-down lists in multiple rows

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

Running totals based on criteria

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

Tracking a stock portfolio #2

This is follow up post to:Â Tracking a stock portfolio in excel (auto update) In this post we are going to […]

This is follow up post to:Â Tracking a stock portfolio in excel (auto update) In this post we are going to […]

Count unique distinct records

Table of Contents Count unique distinct records Count records with possible blank rows in data set How to count blank […]

Table of Contents Count unique distinct records Count records with possible blank rows in data set How to count blank […]

Sort rows based on frequency and criteria

Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]

Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]

True round-robin tournament

Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]

Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]

Create a random playlist

This article describes how to create a random playlist based on a given number of teams using an array formula. […]

This article describes how to create a random playlist based on a given number of teams using an array formula. […]

Team Generator

Table of Contents Team Generator Dynamic team generator 1. Team Generator This section describes how to create teams randomly. There […]

Table of Contents Team Generator Dynamic team generator 1. Team Generator This section describes how to create teams randomly. There […]

Extract a unique distinct list and sum amounts based on a condition

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]

Find empty hours in a weekly schedule

The image above demonstartesÂ an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]

The image above demonstartesÂ an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]

Plot date ranges in a calendar

Table of Contents Plot date ranges in a calendar Plot date ranges in a calendar part 2 1. Plot date […]

Table of Contents Plot date ranges in a calendar Plot date ranges in a calendar part 2 1. Plot date […]

Identify overlapping date ranges

This article demonstrates formulas that show if a date range is overlapping another date range.Â The second section shows how […]

This article demonstrates formulas that show if a date range is overlapping another date range.Â The second section shows how […]

Automate net asset value (NAV) calculation on your stock portfolio

Table of Contents Automate net asset value (NAV) calculation on your stock portfolio Calculate your stock portfolio performance with Net […]

Table of Contents Automate net asset value (NAV) calculation on your stock portfolio Calculate your stock portfolio performance with Net […]

Create number series

Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]

Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]

How to return a value if lookup value is in a range

In this article, I will demonstrate four different formulas that allow youÂ to lookup a value that is to be found […]

In this article, I will demonstrate four different formulas that allow youÂ to lookup a value that is to be found […]

Identify numbers in sum using Excel solver

Table of Contents Identify numbers in sum using Excel solver Find numbers in sum - UDF Find positive and […]

Table of Contents Identify numbers in sum using Excel solver Find numbers in sum - UDF Find positive and […]

Formula for matching a date within a date range

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

Find the most recent date that meets a particular condition

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

Create a quartely date range

I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]

I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

This post explains how to lookup a value and return multiple values. No array formula required.

Count a specific text string in a cell

Table of Contents Count a specific text string in a cell (case sensitive) Count text string in a range (case […]

Table of Contents Count a specific text string in a cell (case sensitive) Count text string in a range (case […]

How to create date ranges in Excel

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Count unique distinct values

This article describes how to count unique distinct values. What are unique distinct values?Â They are all values but duplicates are […]

This article describes how to count unique distinct values. What are unique distinct values?Â They are all values but duplicates are […]

How to rank text uniquely without duplicates

Table of Contents How to rank text uniquely without duplicates How to rank uniquely based on a condition Get Excel […]

Table of Contents How to rank text uniquely without duplicates How to rank uniquely based on a condition Get Excel […]

Sum unique numbers

Table of Contents Sum unique numbers Get Excel *.xlsx file Sum unique distinct numbers Get Excel *.xlsx file Sum number […]

Table of Contents Sum unique numbers Get Excel *.xlsx file Sum unique distinct numbers Get Excel *.xlsx file Sum number […]

How to quickly find the maximum or minimum value [Formula]

This article demonstrates formulas that will return the largest and smallest numbers, the corresponding column headers, the cell addresses, and […]

This article demonstrates formulas that will return the largest and smallest numbers, the corresponding column headers, the cell addresses, and […]

Calculate machine utilization

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

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