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) …
Tag: MATCH function
Lookup the 2nd, the 3rd, or the nth value
Both the MATCH function and the XLOOKUP function look only for the first (last) available match in the array. This is perfectly reasonable in most cases, as we are expecting to deal with unique identifiers when looking up data. However, sometimes we will have to look up the second, third, fourth, or nth value. Consider the following example: Our table contains a list of contract numbers in column B and contact email addresses in column A. Contract numbers are …
Lookup with multiple criteria
If we have to look up items in tables where we can’t use unique identifiers (there are no names located in a table column that contains data in all of the rows, and that data is non-repeating), we will probably have to resort to matching multiple criteria in multiple columns. Consider the following example: Each year, multiple game tournaments are held in repeating cities, organized by various repeating organizers. Items in the column City are not unique, nor are …
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 …
XLOOKUP function
We’ve previously covered how we can combine INDEX and MATCH functions in order to “look up” data. The XLOOKUP function works much the same way as the INDEX MATCH combination. It is intended to replace (still wildly used) legacy functions with outdated syntax and limited capabilities, such as VLOOKUP, HLOOKUP, and LOOKUP, while retaining accessibility. The XLOOKUP function searches a range or an array for a match and returns the corresponding item from a second range or array. The …
IFERROR and IFNA functions
We’ve previously covered how we can nest the ISERROR function, the ISERR function, or the ISNA function inside the IF function in order to check for errors and perform appropriate actions if errors occur. While effective, this can make the whole formula longer than necessary and hard to read/understand/correct. The specialized IFERROR and IFNA functions with simpler syntax can take the place of the IF ISERROR and IF ISNA combinations. The IFERROR function checks whether the cell or formula …
Cleaning up text in Excel: CLEAN, TRIM, and SUBSTITUTE
In Excel, you will occasionally deal with text fields containing line breaks, multiple spaces, hyphenation, currency and letterlike symbols, etc. This is common when dealing with unrestricted user-inputted data and can cause issues. In particular, these kinds of values will be hard to connect with related values without cleanup. When we MATCH those values with related data tables, errors are bound to occur. Basic cleanup approaches include manual replacement of all of the unnecessary characters with a desired or …
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 …
Line breaks in Excel
A line break is the termination of one line of text, and the beginning of the next line. If needed inside of a single Excel cell, line breaks can be achieved in two ways: the Wrap Text feature allows you to make text appear to be structured into multiple lines, manual line breaks can be entered by pressing Alt + Enter. Consider the following sentence: As of Unicode version 14.0, there are 144,697 characters with code points, covering 159 …
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 …