## Archive for SUMPRODUCT function

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

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

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

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

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

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

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

Dynamic team generator in excel

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

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

Identify overlapping date ranges

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

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]