'SUMPRODUCT function' category
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 […]
Sum unique distinct numbers
The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]
The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]
Count a given pattern in a cell value
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
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 with data
The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains 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 […]
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 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 […]
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 […]
Sum numbers between two dates
The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]
The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]
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 […]
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 […]
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 […]
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 […]
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.
Heat map yearly calendar
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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, […]
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. […]
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 […]
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 […]
This is follow up post to:Â Tracking a stock portfolio in excel (auto update) In this post we are going to […]
Tracking a stock portfolio in excel (auto update)
Question: I found a question here about tracking a stock portfolio. He would like to automatically create an overview table […]
Question: I found a question here about tracking a stock portfolio. He would like to automatically create an overview table […]
Count unique distinct records
The image above shows a table with 3 columns containing random data. It is quite complicated trying to manually count […]
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 […]
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 […]
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 Highlight […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Highlight […]
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 […]
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
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 […]
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 […]
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. […]
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 […]
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 […]
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 […]
Count groups in calendar
Question: Sam asks: Is there a formula that can count blocks For eg in your picture (see picture above) if […]
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 […]
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
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 […]
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, […]
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 […]
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 […]
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 […]
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, […]
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 […]
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, […]
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 […]
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 […]
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 […]
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 […]
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.
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: […]
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), […]
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 […]
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
Sum number based on corresponding unique value
The formula in cell E14 adds a number from column C if the corresponding value in column B is unique […]
The formula in cell E14 adds a number from column C if the corresponding value in column B is unique […]
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 uniquely based on a condition
The following formula ranks text values in column C uniquely based on the category in column B. Formula in D3: […]
The following formula ranks text values in column C uniquely based on the category in column B. Formula in D3: […]
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 […]
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)) + […]
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 […]
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 […]
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 […]
I will now demonstrate with the following table how to add check-boxes and sum enabled check-boxes using a formula. Add […]