## 'SUM function' category

How to use the asterisk character

The asterisk character allows you to multiply numbers and boolean values in an Excel formula. It can also be used […]

The asterisk character allows you to multiply numbers and boolean values in an Excel formula. It can also be used […]

How to sum a cell range

I will in this article demonstrate different ways to sum values, the first method is so easy and fast it's […]

I will in this article demonstrate different ways to sum values, the first method is so easy and fast it's […]

How to count blank cells

The image above shows the COUNTBLANK function counting empty cells in cell range B3:B14. Note that the COUNTBLANK function ignores […]

The image above shows the COUNTBLANK function counting empty cells in cell range B3:B14. Note that the COUNTBLANK function ignores […]

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

How to sum overlapping time

The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]

The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]

Count cells containing text from list

The array formula in cell F3 counts cells in column B that contains at least one of the values in […]

The array formula in cell F3 counts cells in column B that contains at least one of the values in […]

Sum by group

To extract groups from cell range B3:B10 I use the following regular formula in cell B13. =LOOKUP(2,1/(COUNTIF($B$12:B12,$B$3:$B$10)=0),$B$3:$B$10) Copy cell B13 […]

To extract groups from cell range B3:B10 I use the following regular formula in cell B13. =LOOKUP(2,1/(COUNTIF($B$12:B12,$B$3:$B$10)=0),$B$3:$B$10) Copy cell B13 […]

If cell contains text

This article demonstrates different formulas based on if a cell contains a given text. Formula in cell C3: =B3=$E$3 The […]

This article demonstrates different formulas based on if a cell contains a given text. Formula in cell C3: =B3=$E$3 The […]

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

Count unique distinct values based on a condition

This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

How to use the SUM function

What is the SUM function? The SUM function in Excel allows you to add values, the function returns the sum […]

What is the SUM function? The SUM function in Excel allows you to add values, the function returns the sum […]

How to use Excel Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Rotating unique groups with no repeat

This article demonstrates a formula What's on this page Question Formula Array formula Explaining array formula Get the Excel File […]

This article demonstrates a formula What's on this page Question Formula Array formula Explaining array formula Get the Excel File […]

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

Count overlapping days across multiple date ranges

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

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

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

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 build a Team Generator – different number of people per team

JD asks in this post:Â Dynamic team generator Hi, what if we have different number of people per team? So in […]

JD asks in this post:Â Dynamic team generator Hi, what if we have different number of people per team? So in […]

Split expenses calculator

This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]

This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]

How to count the number of values separated by a delimiter

This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]

This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]

Working with classic ciphers in Excel

What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

Highlight unique values in a filtered Excel table

This article demonstrates a conditional formatting formula that allows you to highlight unique values based on a set of filtered […]

This article demonstrates a conditional formatting formula that allows you to highlight unique values based on a set of filtered […]

Highlight duplicates in a filtered Excel Table

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

Count specific multiple text strings in a given cell range

This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range.Â The […]

This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range.Â The […]

Working with three relational tables

I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers […]

I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers […]

Lookups in relational tables

Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]

Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]

Count unique distinct values in a filtered Excel defined Table

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

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

Find entry based on conditions

Bill Truax asks: Hello Oscar, I am building a spreadsheet for tracking calls for my local fire department.Â I have a […]

Bill Truax asks: Hello Oscar, I am building a spreadsheet for tracking calls for my local fire department.Â I have a […]

Label groups of duplicate records

Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]

Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Count unique distinct records with a date and column criteria

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

Filter unique distinct records case sensitive

This article demonstrates two ways to extract unique and unique distinct rows from a given cell range. The first one […]

This article demonstrates two ways to extract unique and unique distinct rows from a given cell range. The first one […]

Analyze word frequency in a cell range

This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]

This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]

Filter words containing a given string in a cell range

This post describes two ways to extract all matching strings from cells in a given cell range if they contain […]

This post describes two ways to extract all matching strings from cells in a given cell range if they contain […]

Count digits and ignore duplicates

Question: I have a question that I canâ€™t seem to find an answer to: I want to make a full […]

Question: I have a question that I canâ€™t seem to find an answer to: I want to make a full […]

Sort a list in random order in excel

Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]

Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]

Schedule recurring expenses in a calendar in excel (Personal Finance)

Below is an excel table containingÂ recurring expenses and corresponding amounts, dates and recurring intervals. An excel table allows you to […]

Below is an excel table containingÂ recurring expenses and corresponding amounts, dates and recurring intervals. An excel table allows you to […]

Count unique distinct values within same week, month or year

The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]

The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]

How to count unique distinct values based on a date

The array formula in cell D3 calculates the number of unique distinct items based on the given date in columnÂ B. […]

The array formula in cell D3 calculates the number of unique distinct items based on the given date in columnÂ B. […]

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

Using Excel Solver to schedule employees

This is a question I found at the bottom of this page Using Solver to schedule your workforce Bank 24 […]

This is a question I found at the bottom of this page Using Solver to schedule your workforce Bank 24 […]

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

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

Create unique distinct list sorted based on text length

The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]

The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]

Sum cells containing numbers and text based on a condition

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

Highlight more than once taken course in any given day

Question: My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any […]

Question: My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]

Question: How do I count the number of times a text string exists in a column? The text string may […]

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

Count unique distinct values in two columns

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]

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

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

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

Sort a column alphabetically

Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]

Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]

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

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

Sum only visible cells

The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture […]

The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture […]

## Excel formula categories

AverageChooseCombine MergeCompareConcatenateConditional FormattingCountCount valuesDatesDuplicatesExtractFilterFilter recordsFiltered valuesFilterxmlHyperlinkIf cellIndex MatchLogicLookupsMatchMaxMinNumbers in sumOR logicOverlappingPartial matchRecordsSequenceSmallSort bySort valuesSumSumifsSumproductString manipulationTimeUnique distinct recordsUnique distinct valuesUnique recordsUnique valuesVlookupVlookup return values