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 […]
The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]
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, […]
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 […]
This article describes ways to work with the SUMPRODUCT function and blanks, error values, N/A# errors. Table of Contents SUMPRODUCT […]
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 […]
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.
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 […]
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 […]
Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]
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, […]
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 […]
This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]
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 […]
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 […]
Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]
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 […]
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
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 […]
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 […]
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 […]
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 […]
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: […]
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 […]
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 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 […]
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 […]
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
I will now demonstrate with the following table how to add check-boxes and sum enabled check-boxes using a formula. Add […]