## SUMPRODUCT function

Excel » Functions » Math and trigonometry » SUMPRODUCT function »

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

Sum unique distinct numbers

The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]

Count cells with text

The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]

Count rows with data

The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]

Count cells equal to any value in a list

The formula in cell F9 counts the number of cells in column B (Values1) that are equal to any of the […]

Count specific WEEKDAYS between two dates

If you want to count specific weekdays like for example Mondays and Wednesdays you need a more complicated array formula. […]

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

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

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

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

Find empty cells and sum cells above

This article demonstrates how to find empty cells and populate them automatically with a formula that adds numbers above and […]

Highlight cells based on ranges

This article demonstrates a Conditional Formatting formula that lets you highlight cells based on numerical ranges specified in an Excel […]

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.

Heat map yearly calendar

The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]

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

Plot date ranges in a calendar part 2

I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]

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

Count a specific weekday in a date range

NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]

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

Free School Schedule Template

This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]

Count weekday within date range except holidays

Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]

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

Sum cells based on criteria

Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]

Sum unique distinct invoices

Question: I have a long table The key is actually col B&C BUT…sometime there are few rows with same key […]

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

Highlight records [AND logic]

The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. […]

Highlight records – multiple criteria [OR logic]

The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]

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

The image above shows a table with 3 columns containing random data. It is quite complicated trying to manually count […]

Compare tables: Highlight records not in both tables

The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]

Compare two lists of data: Highlight common records

In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]

Highlight duplicate records

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

Count cells based on a condition and month

Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]

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

Dynamic team generator

Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF(\$C\$1:C1, \$E\$2:\$E\$5)<5 changed so […]

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

This blog article describes how to create teams randomly. There are twenty names in column B and four teams in […]

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

Count groups in calendar

Question: Sam asks: Is there a formula that can count blocks For eg in your picture (see picture above) if […]

Convert dates into date ranges

The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Highlight duplicates with same date, week or month

The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]

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

Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]

Sum based on OR – AND logic

Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]

Count unique distinct numbers across multiple sheets

The image above demonstrates three different formulas in column E that counts unique numbers unique distinct numbers duplicate numbers from […]

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

Find positive and negative amounts that net to zero

I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]

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 latest date based on a condition

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

Sum values between two dates and based on a condition

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

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

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.

Create a list of dates with blanks between quarters

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

Count unique distinct months

The formula in cell D18 counts unique distinct months in cell range B3:B16. Formula in D18: =SUMPRODUCT((FREQUENCY(DATE(YEAR(\$B\$3:\$B\$16), MONTH(\$B\$3:\$B\$16), 1), DATE(YEAR(\$B\$3:\$B\$16), […]

Count a specific text string in a cell

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

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]

Count dates inside a date range

How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF((\$A\$2:\$A\$10<\$D\$2)*(\$A\$2:\$A\$10>\$D\$1), 1, 0)) + […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Calculate machine utilization

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

Sum cells with check boxes

I will now demonstrate with the following table how to add check-boxes and sum enabled check-boxes using a formula. Add […]