SUMPRODUCT function - Get Digital Help

'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 […]

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 […]

Count cells with text
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 […]

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 […]

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 […]

AVERAGE ignore blanks
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 […]

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 […]

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

SUMPRODUCT – multiple criteria
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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

How to use the SUMPRODUCT function
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 […]

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 […]

Excel calendar
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 […]

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 […]

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

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 […]

Count unique distinct records
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 […]

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 […]

Create a random playlist
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 […]

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 […]

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 […]

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 […]

Identify overlapping date ranges
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 […]

Create number series
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 […]

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 […]

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 […]

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 […]

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 […]

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.

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 […]

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 […]

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 […]

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 […]

Sum unique numbers
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 […]