SUMIFS criteria: INDEX MATCH or XLOOKUP criteria

Using the SUMIFS function, we can sum all of the values in a defined column (or row) that meet one or more criteria. SUMIFS criteria can reference cells, contain values or text, contain logical tests, or contain formulas and functions. By nesting the INDEX MATCH combination or the XLOOKUP function as SUMIFS criteria, we can return values based on criteria that are not present in the table we are returning values from.   Consider the following example: This table contains (repeating)

FILTER function

The FILTER function replicates and extends Excel AutoFilter filtering functionality in formula form. The FILTER function allows us to filter a range of data based on defined criteria. By default, the FILTER function will return multiple values, which will be placed in the neighboring cells (to the bottom and/or to the right). I.e., we are dealing with dynamic array formulas here.   The syntax of the FILTER function is as follows: = FILTER ( array ; include ; [if_empty] )

Combining SUMIFS with XLOOKUP

Using the SUMIFS function, we can sum all of the values in a defined column (or row) that meet one or more criteria. When SUMIFS is combined with XLOOKUP, that sum range doesn’t have to be defined anymore; it is now rather specified in the function arguments. By combining SUMIFS with XLOOKUP, we can then sum all of the values that meet multiple criteria in different rows and columns and do this in a simple way, avoiding complex and resource-intensive

Excel Tables

What are Excel Tables? Excel Tables, or just Tables, are tables superimposed over regular cells. Excel Tables are assumed to be containing structured data. Both tables and individual table columns are named and intended to be referenced in formulas by those names. As they are typically used to load structured data from an external source into Excel, and that external data is typically accessed from Excel using the Data ribbon and then loaded into Table, they are also sometimes called

SUMIFS by text string length

Values that meet one or more criteria can be added up with the SUMIFS function. The sum of values next to cells containing specific text, i.e., a sum based on text criteria, is possibly the most common use case. The sum of values next to cells containing (non) specific text of a defined length (the defined number of characters in a text string) is a far less common need, but still crucial in specific situations.   We can accomplish this

Combining INDIRECT with ADDRESS

We’ve previously covered how we can use any function to generate a text that could be a valid cell reference, both the column and row parts of the address, and then use the INDIRECT function to convert that text to a cell reference. This process can be greatly enhanced by the ADDRESS function.   The ADDRESS function returns a text string that represents the address of a particular cell. Row, column, type of reference (locked or absolute), reference style (A1

Top 10 lists in Excel

When analyzing large amounts of data in Excel, often the best approach is to retrieve the top (10) values. Consider the following example: This table contains all of the invoices from January 29th. There are 904 invoices for that day. The values in the invoice column are unique, as those are invoice numbers. The values in the user column are not unique, as some users have multiple invoices on that day, and the values in the team column are not

SUMIFS criteria: formulas, functions and conditional statements

We’ve previously established the rules for writing criteria in the SUMIFS function: we can reference any cell we can enter any number directly into the function we can enter text directly in the function, nested inside quotation marks “” a logical test is nested inside of quotation marks “”, with the comparison operator coming first and a number second. We will further expand on the topic in this article. Formulas and functions can also be nested in SUMIFS criteria. If needed,

Combining SUMIFS with INDEX MATCH

Using the SUMIFS function, we can sum all of the values in a defined column (or row) that meet one or more criteria. When SUMIFS is combined with INDEX MATCH, that sum range doesn’t have to be defined anymore; it is now rather specified in the function arguments. By combining SUMIFS with INDEX MATCH, we can then sum all of the values that meet multiple criteria in different rows and columns and do this in a simple way, avoiding complex and

Lookup with unique identifiers

INDEX function The INDEX function returns the value from a range of cells based on the row and column specified in the function arguments. We are simplifying here: the INDEX function can actually return values from one or more arrays, i.e., a broader term for lists of data that includes cell ranges. But the most common use is with cell ranges, as that is the typical way of structuring data in Excel.   The syntax of the INDEX function is