Search Results

Learn how the INDEX and MATCH functions work individually and how putting them together allows you to conduct flexible lookups.

This tutorial takes you through three powerful Excel formulas that lookup values in ranges that include lower bound and upper bound values. They are SUMIFS, SUMPRODUCT and INDEX/MATCH. Find the one that works best for your scenario.

Learn how to use the conditional aggregation functions: SUMIFS, AVERAGEIFS, COUNTIFS. You don’t need to waste time copying filtered ranges, you can get the value with a single formula.

Learn how to create a description column that includes the number of sub-items within a main item and also provides the description of the main item. You can achieve that in Excel with a formula combining the COUNTA, INDEX and MATCH functions.

Consider a scenario, where you need to return a unique count across rows rather than column. You could use the TRANSPOSE function, but in older versions of Excel, TRANSPOSE requires pressing Control Shift Enter (CSE). If you’d rather avoid it, but keep the solution dynamic, you need to look for an alternative. In this tutorial, you will find 3 different solutions based around the ROW function – in combination with INDIRECT, INDIRECT & ADDRESS, and INDEX.

With this tutorial, you will improve your chart design. I share a few different methods used for visualizing performance comparisons, deviations, as well as future estimated performance.

Learn how to get the unique count across rows using an array formula based on the MMULT and SUMPRODUCT functions.

Learn how to get the unique count across rows using an array formula based on the SUMPRODUCT and FREQUENCY functions.

Think of the OFFSET function as a GPS for your spreadsheet. You give it a starting point and then you tell it how many rows to go down, how many columns to move across, and what range you want returned. Master OFFSET with the help of this tutorial, which looks at calculating the average for the last six months in a dynamic way.

This tutorial explains the MOD function in simple terms and shows you three different practical cases. Use MOD to decide the ideal size of groupings, prepare data for quarterly reporting, and control the occurrences of data labels in charts.

Learn how to write dynamic YTD formulas in Excel using SUMPRODUCT, OFFSET & SUM functions, even when your data set goes over a few years, with each month occurring more than once.

Master one of the most useful functions in Excel – SUMPRODUCT. This tutorial explains how it works in easy steps. You’ll understand why you have to use a multiplication operator or a double negative, how to handle ‘and’ & ‘or’ conditions, and what to watch out for. You can test your knowledge with the attached exercise book.