Dave Bruns

In this example the goal is to sum the numbers in the range F5:F16 when corresponding cells in the range C5:C15 are not equal to "Red". To solve this problem, you can use either the SUMIFS function or the ...Read more

In this example, the goal is to sum the Amounts in C5:C16 when the Lead in D5:D16 is not blank (i.e., not empty). A good way to solve this problem is to use the SUMIFS function. However, you can also use the ...Read more

The example shown contains almost 10,000 rows of data. The data represents temperature readings taken every 2 minutes over a period of days. For any given date (provided in cell H7), we want to get the maximum temperature on that date.

Inside the IF...Read more

In this example, the goal is to map the numbers 1-6 to the arbitrary values seen in the table below. For example:

• If the input is 1, the output should be 10
• If the input is 2, the output should be 81
• If the input is 3, the output should be 17
• If the
...Read more

In this example, the goal is to sum Total when the corresponding Color is either "Red" or "Blue". For convenience, all data is in an Excel Table named data. This is a tricky problem, because the solution is not obvious. The go-to function for...Read more

In this example the goal is to sum the numbers in the range F5:F16 when cells in the range C5:C15 contain "Red". To solve this problem, you can use either the SUMIFS function or the SUMIF function. The SUMIF...Read more

In this example, the goal is to subtotal invoices by age, where age represents the number of days since the invoice was issued. This problem can be solved with the SUMIFS function and the COUNTIFS function, as explained below. For convenience, age (E5:E16) and amount...Read more

In this example, the goal is to subtotal (count and sum) values based on cell color. This is a tricky problem, because there is no Excel function that will let you count cells by color directly. There are several different approaches, as explained below.

Standard formula logic

...Read more

Sometimes you encounter data that mixes units directly with numbers (i.e. 8km, 12v, 7.5hrs). Unfortunately, Excel will treat the numbers in this format as text,  and you won't be able to perform math operations on such values.

To split a number from a unit value, you need to...Read more

In this example, the goal is to clean up telephone numbers with inconsistent formatting and then reformat the numbers in the same way. In practice, this means we need to start by removing the extra non-numeric characters, including spaces, dashes, periods, and parentheses. Once these...Read more