Search results

You would think you could use the COUNTIF function to count birthdays, but the trouble is COUNTIF only works with ranges, and won't let you use something like MONTH to extract just the month number from...Read more

In this example, the goal is to sum the amounts in column C by week, using the dates in the range E5:E10 which are all Mondays. All data is in an Excel Table named data in the range B5:C16. This problem can be solved in a straightforward way with the...Read more

In this example, the goal is to sum by group, where each group is represented by a different color: Blue, Red, Green, and Purple. The worksheet shown contains two different approaches. In the range F5:G8, we have created a summary table to summarize counts by color. In column D, we are...Read more

In this example, the goal is to calculate a running total in column D of the worksheet as shown. A running total, or cumulative sum, is a set of partial sums that changes as more data is collected. Each calculation represents the sum of values up to that point. In this example, each...Read more

In this example, the goal is to count birthdays by year. The source data is an Excel Table named data in the range C5:C16. The birthdays we want to count are in the Birthday column. In column E, the years of interest have been previously ...Read more

In this example, the goal is to sum the amounts shown in column C by month using the dates in column B. The article below explains two approaches. One approach is based on the SUMIFS function, which can sum numeric values based on multiple...Read more

In this example, the goal is to calculate a monthly average for the amounts shown in column C using the dates in column B. The article below explains two approaches. One approach is based on the AVERAGEIFS function, which is designed to calculate...Read more

In this example, the goal is to group ages into buckets. One way to do this is to prepare a table with age breakpoints in the first column, and the name of the appropriate group or bucket in the second column. Then use a lookup function to find the right bucket or group for each age. In the...Read more

In this example, the goal is to use a single formula to extract high-value projects and list them in a simple table. We also want to remove unnecessary columns to create a clean, uncluttered view. The solutions explained below are based on a combination of several functions in Excel, including...Read more

In this example, the goal is to get the maximum value in the data for each month listed in column E. The easiest way to do this is with the MAXIFS function, which is designed to return a maximum value based on one or more criteria. In older versions of Excel without the MAXIFS function, you...Read more

Note the purpose of this example is to how one way to "normalize" data when the order of values is random. There are many ways to approach this problem.

The formula in G6 relies on the COUNTIF function to count the presence of a given class (i.e...Read more

If you need to group times into buckets (i.e. group by 6 hours, group by 3 hours, etc.) you can do so with a rounding function called FLOOR.

In the example shown, we have a number of transactions, each with a timestamp. Let's say you want to group these transactions into buckets of 3...Read more

To do this, LOOKUP is configured as follows:

• Lookup values are ages in column C
• The lookup vector is the named range "age" (F5:F8)
• The result vector is the named range "group" (G5:G8)

With this setup, LOOKUP performs an approximate match on the...Read more

In May 2025, I ran a survey asking Exceljet newsletter subscribers what version of Excel they use most. This is an important question for Excel learning because the new dynamic array engine has completely changed how many formulas are written. These changes started rolling out after Excel...Read more