## Archive for Math and trigonometry

How to use the AGGREGATE function

The AGGREGATE function allows you to calculate different specific functions to a list or database. A special AGGREGATE function feature […]

The NETWORKDAYS function allows you to count weekdays or workdays between two dates. It ignores weekends and a custom date list […]

Converts negative numbers to positive numbers, in other words, the ABS function removes the sign. Excel Function Syntax ABS(number) Arguments […]

Excel’s SUMIFS function explained

The SUMIFS function in cell D11 adds numbers from column D based on criteria applied to column B and C. […]

If cell contains multiple values

The array formula in cell C3 checks if text string in B3 contains all values in F2:F3.

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

The above image demonstrates how to ignore blank cells in a SUMPRODUCT formula. The following formula is shown in cell E3.

You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. […]

SUMPRODUCT – multiple criteria

The formula above in cell G8 uses two conditions in cell G2 and G3 and a date range G5:G6 to […]

To extract groups from cell range B3:B10 I use the following regular formula in cell B13.

Learn how to use the SUBTOTAL function

The picture above shows row 4 and row 8 hidden, the SUM argument 9 sums all values in C3:C11 whereas […]

The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]

Excel’s SUMIF function explained

Excel function syntax SUMIF(range, criteria, [sum_range]) The SUMIF function sums values based on a condition. The condition can be applied […]

The SUM function in cell D3 uses only a single cell reference and still manages to sum current and previous […]

Find empty cells and sum cells above

Is it possible to quickly select all empty cells and then sum cells above to next empty cell? Can I have […]

The SUM function in excel allows you to add values, the function returns the sum in the cell it is […]

Filter rows where a cell contains a numeric value

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]

Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I […]

Count overlapping days across multiple date ranges

NC asks: Thanks a tonne, Oscar. It took me about 8 hours to work through this formula piece by piece, […]

Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and […]

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

Working with overlapping date ranges

Today's blog post is about date ranges, the techniques demonstrated here can also be applied to time or other numerical […]

You are about to send your company products to customers. You have boxes you can send the products in. You […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Find the longest/shortest consecutive sequence of a value

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

Learn how the MOD function works

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]

Find last matching value in an unsorted list

I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about […]

Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function. RAND() function returns a random […]

How to use Excel SUMPRODUCT function

Multiplies cell ranges and then sum all values.

MMULT function – Matrix multiplication

MMULT(array1, array2) Returns the matrix product of two arrays, an array as the same number of rows as array1 and […]

Count multiple text strings in a cell range

The array formula below counts how many times multiple text strings exist in a cell range. It is easy to […]

Kamran Mumtaz asked: Is there any way to use VLOOKUP for multiple criteria and I do not want to use CSE?; […]

Vlookup visible data in a table and return multiple values

This post describes how to search visible values and return multiple values from a table. Some rows are hidden because […]

Sum the cells in a range that meet multiple criteria

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

Lookup with multiple criteria and display multiple unique search results (array formula)

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

Running totals within date range

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]

Table of Contents Count all overlapping days in any number of date ranges Count overlapping days in a date range […]

Identify overlapping date ranges

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]

Sum values in a range where adjacent cell value equals a criterion

Question: How do I sum all values in a range where adjacent cell value equals a criterion? The criterion is […]

Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Color even months Conditional formatting formula: =NOT(MOD(MONTH($B6),2)) Color odd years Conditional formatting formula: […]

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

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

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

Array formula in E3: =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)) Recommended articles: How to enter an array formula Select cell E3 Type […]

Create a repeating list of numbers from 1 to Nth value

Question: How do I create a repeating list of numbers from 1 to Nth value? In my case from 1 […]

Count specific text string in a cell range

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

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

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

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