Sum multiple rows or columns with XLOOKUP

sum multiple rows or columns using xlookup

To sum multiple rows or columns based on a lookup value, you can use the SUM function with the XLOOKUP function. Even if we have a definitive XLOOKUP guide, we learn something special daily. Today’s example will be on how to find the matching value in a table and sum multiple rows or columns. The … Read more

Nested XLOOKUP

nested xlookup using two way lookup

Learn how to create a nested XLOOKUP formula if you need two lookup values to find the matching record in Excel. We will show you how to use XLOOKUP to replace the INDEX and MATCH combination when you need a two-way lookup. For example, sometimes, we need two inputs to find the matching record in … Read more

Find the 2nd match using XLOOKUP

find-the-2nd-match-using-xlookup

This guide will explain how to find the 2nd match or the nth occurrence in an array using the XLOOKUP function. There is a common problem with all lookup functions in Excel: how to skip the exact match and get the 2nd, 3rd, or nth match. We will use the XLOOKUP function in the example … Read more

Lookup first negative value

lookup first negative value in Excel

Learn how to lookup the first negative value in a range using the XLOOKUP function or INDEX and MATCH functions. Generic Formula Here is the generic formula to lookup the first negative value in a range: In the formula, “rng” is the range where we find the first negative value; enter the formula in cell … Read more

Get first text value with XLOOKUP

get first text value in a range with xlookup

To lookup and get the first text value in a range, use the XLOOKUP function with a wildcard character match mode. From time to time, you want to find and extract the first text value from a range that contains numbers and text values. Use the XLOOKUP function and apply the wildcard character (*) as … Read more

Count cells that contain numbers

count cells that contain numbers excel

This guide will explain how to count cells that contain numbers in a range using the Excel COUNT function. In the example, we want to count all cells in a range where the cells are numbers. There are two common methods: using the COUNT function or applying the SUMPRODUCT function. If you want to learn … Read more

Highlight Every Other Row in Excel

highlight-every-other-row-excel

This tutorial will explain how to highlight every other row in Excel. To improve your spreadsheet’s readability, shade alternate rows! Working with large data tables and organizing ranges in Excel is sometimes not too easy. In this Excel guide, I will cover the following topics: At first, you’ll learn how to highlight every other row … Read more

Count unique values and distinct values

count unique values excel and distinct values

To count unique values in a list or range, use the UNIQUE function and configure the function’s third argument to TRUE. In Microsoft365 for Excel, you can create powerful formulas. Today’s guide will be on how to count unique and distinct values in a range using COUNT and UNIQUE functions. Differences between unique values and … Read more

Count if cells less than a given number

count if cells less than a given number

To count cells that contain values less than a given number in a range, use the generic formula based on the COUNTIF function. Generic Excel Formula to count the number of cells with values less than a given number: In the picture above, “range” is the range of cells that contains numbers, and “number” is … Read more

SUM cells that contain formulas

SUM cells that contain formulas in Excel

This tutorial will explain how to sum cells that contain formulas in Excel using the SUMPRODUCT and ISFORMULA functions. Generic formula to sum results where the cell contains formulas only: How to SUM cells that contain formulas In the example, we have a sales table that contains orders and sales for a given location. Some … Read more

How to convert boolean TRUE and FALSE to 1 or 0

how-to-convert-booleans-to-numbers

Learn how to convert boolean TRUE or FALSE to numbers (0 or 1) using the Excel N function or the double negative method. When working with complex Excel formulas, you must know how to convert TRUE and FALSE values to the numeric equivalents 1 and 0. If you are unfamiliar with boolean logic, read this … Read more

Count cells over n characters

count cells over n characters

To count cells that contain more than n characters, use a formula based on three Excel functions: SUMPRODUCT, LEN, and N. The general formula to count cells over n characters: In the example, n=70 in cell F2, we find the cells which contain more than 70 characters. The result is 3 since three cells in … Read more