When writing formulas in Excel, SUM and VLOOKUP are staples for beginners. But since Excel has over 400 functions, it’s helpful to know more of the extremely useful ones, especially when looking to become an intermediate user and beyond. Let me help you discover functions that will further streamline your Excel workflow.
The functions I cover here aren’t complex, and you’ll be able to use them in various settings, whether you’re a student, business professional, or analyst. It’s time to elevate your Excel game with some additional time-saving functions you may not know about.
COUNT offers a better way to tally cells
If they contain numerical data
The COUNT function counts all the cells with numerical values and returns the result. This saves you the hassle of manually counting them, which can be tedious and time-consuming in large datasets.
=COUNT(value1, value2, ... value_n)
The example formula below should output 10 if all the cells contain numbers.
=COUNT(A1:A10)
AVERAGE simplifies calculating the mean
Works for any range
As the name suggests, the AVERAGE function calculates the average (mean) by adding up all the numeric values in a range and dividing them by their total count.
=AVERAGE(value1, value2, ... value_n)
For example, here’s the formula to find the average of values in cells A1 through A10:
=AVERAGE(A1:A10)
MIN locates the minimum value in a range
There’s also a MAX function
Suppose you have a large dataset, and you need to find the smallest value. The MIN function is the fastest way to do it.
=MIN(value1, value2, ... value_n)
Here is an example of how it works:
=MIN(A1:A10)
On the flip side, if you want to find the largest value in a range, you should use the MAX function.
=MIN(value1, value2, ... value_n)
SUMIF is the smarter version of the SUM function
Some conditional logic is required
The SUM function just adds whatever numerical values you give it. The SUMIF function, however, only adds them if they meet a specified criterion. It essentially combines the SUM and IF function in Excel, so you don’t have to manually sort or filter the data before summing it.
=SUMIF(range_to_evaluate, criteria, [range_to_sum])
Here, range_to_evaluate is the cells with the values the function must check against the condition specified in the criteria parameter. The optional argument, range_to_sum, is the specific cells in the range you want to sum. If you omit this parameter, it will just sum everything in the range that meets the condition.
Below is an example of the function in action. This formula only sums the values in A1:A10 if they’re greater than 50.
=SUMIF(A1:A10, ">50")
There are variations of the SUMIF function you can look into as well, including COUNTIF, AVERAGEIF, MINIF, and MAXIF. Some versions allow you to specify multiple criteria, such as SUMIFS, COUNTIFS, and AVERAGEIFS.
SUBTOTAL offers cleaner aggregation
Unneeded data shouldn’t mess up your calculations
SUBTOTAL is used by Excel pros because it intelligently excludes filtered or hidden data, while SUM and similar functions include everything. This makes it the ideal choice when you’re working with dynamic datasets, especially when hiding and filtering ensure accuracy.
=SUBTOTAL(function_code, range)
The parameter function_code is a number from 1-11 or 101-111 that specifies the function to use (e.g., 1 for AVERAGE, 2 for COUNT, 9 for SUM). The numbers 1-11 will include all data, while 101-111 will exclude hidden rows. The range parameter is the cells to subtotal.
The example below will sum the range A1:A10 but exclude any hidden rows:
=SUBTOTAL(109, A1:A10)
XLOOKUP overcomes VLOOKUP’s flaws
The modern way to perform a lookup
Since you’re familiar with VLOOKUP, you probably know its major flaw — it can only search from left to right and requires the lookup column to be on the left side of the return column. XLOOKUP is more powerful and flexible, allowing you to search in any direction. You don’t even need to sort the columns.
=XLOOKUP(lookup_value, lookup_range, return_range)
Here, lookup_value is what you’re searching for, lookup_range is where to search for the value, and return_range is what to return when the value is found. Also, keep in mind that this is a simplified version of XLOOKUP, with the full version including error handling as an optional argument.
Here’s an example where the range A2:A5 contains customer IDs and the range B2:B5 contains customer names. We use XLOOKUP to find the name of the customer whose ID is found in cell D1.
=XLOOKUP(D1, A2:A5, B2:B5)
INDEX/MATCH is the classic lookup combo
Before XLOOKUP was a thing
XLOOKUP is not available in Excel versions older than 2021 and other office suites like LibreOffice or OnlyOffice. So, if you want the flexibility of XLOOKUP in these scenarios, you will need to combine the INDEX and MATCH functions. Furthermore, this combination gives you better control of each lookup step, even though it doesn’t have the error handling of XLOOKUP.
=INDEX(return_range, MATCH(lookup_value, lookup_range, match_type))
Here, return_range is the range of cells that contains the value you want to retrieve, lookup_value is the value you want to look up, and lookup_range is the range of cells where you want to search for the lookup value. The match_type parameter accepts the following values: 0 for exact match, 1 for less than, and -1 for greater than.
Continuing with the XLOOKUP example in the previous section, the INDEX MATCH version would be:
=INDEX(B2:B5, MATCH(D1, A2:A5, 0))
As mentioned earlier, you have control here. You can, for example, use the XMATCH function instead of MATCH for more advanced lookups. Some people even use the FILTER function if they don’t want to do manual filtering and sorting during their lookups.
SUM and VLOOKUP are just the beginning
Mastering Excel goes far beyond SUM and VLOOKUP. By learning functions like COUNT, AVERAGE, SUMIF, SUBTOTAL, XLOOKUP, and INDEX/MATCH, you can find opportunities to improve your Excel sheet. Some functions help you analyze data faster, automate tasks, and make your spreadsheets smarter, but those are for another time.
