Dave Bruns

In this example, the goal is to look up the correct shipping cost for an item based on the shipping service selected and the item's weight. At the core, this is an approximate match lookup based on weight. The challenge is that we also need to filter by service. This means we must apply criteria...Read more

In this example, the goal is to sum the values in columns C, E, G, and I conditionally using the text values in columns B, D, F, and H for criteria. This problem can be solved with the SUMPRODUCT function, which is designed to multiply ...Read more

In this example, the goal is to sum every nth value by column in a range of data, as seen in the worksheet above. For example, if n=2, we want to sum every second value by column, if n=3, we want to sum every third value by column, and so on....Read more

The first COLUMN function generates an array of 7 numbers like this:

{2,3,4,5,6,7,8}

The second COLUMN function generates an array with just one item like this:

{2}

which is then...Read more

This formula uses the value in cell F7 for a lookup value, the range B6:C10 for the lookup table, the number 2 to indicate "2nd column", and zero as the last argument to force an exact match.

Although in this case we are mapping text values to numeric outputs, the same formula can...Read more

Normally, the MATCH function receives a single lookup value, and returns a single match if any. In this case, however, we are giving MATCH an array for lookup value, so it will return an array of results, one per element in the lookup array. MATCH is configured for "exact match". If a match isn...Read more

In this example, the goal is to count orders (rows)  where the state is Texas ("TX"), the amount is greater than $100, and the month is March. In this case, we can't use COUNTIFS, because COUNTIFS is a...Read more

In this example the goal is to count products (rows) where sales have increased and sales have decreased, where previous sales are in column C (Previous) and current sales are in column D (Current). In this case, we can't use...Read more