The best **Excel formulas** include detailed examples and functions like XLOOKUP, FILTER, UNIQUE, SUMPRODUCT, and COUNTIFS.

**Take a closer look at the formulas that are powered by user-defined functions.** Microsoft Excel formulas help you to count, analyze, filter, convert, summarize, and transform data to boost productivity and speed up your work. You can use our **free utility**, **DataFX**, to perform advanced calculations.

**Jump to category:**

For Excel newbies, we have good news; you can** read our detailed article about the basics**. Let the adventure begin!

## COUNT Formulas

Formula | Related Functions |
---|---|

Count cells that contain numbers | COUNT, SUMPRODUCT, ISNUMBER |

Count cells that contain text | COUNTX |

Count cells that contain specific text | COUNTTEXT |

Count rows that contain specific values | ROWCRT, MMULT, LAMBDA |

Count cells that do not contain errors | COUNTX |

Count if cells less than a given number | COUNTIF |

Count cells that begin with specific text | COUNTBEGIN |

Count cells over n characters | SUMPRODUCT, N, LEN |

Count cells between two numbers | COUNTBETWEEN, COUNTIFS |

Count cells not between two numbers | NOTBETWEEN |

Count numbers by nth digit | COUNTDIGITS, COUNTIF, SUMPRODUCT |

Count unique text values in a range | SUM, COUNTIF, UTEXT |

## SUM formulas

Formula | Related Functions |
---|---|

Calculate the running total (cumulative SUM) | RTOTAL |

Sum if between two numbers | SUMBETWEEN |

Sum comma-separated numbers in a cell | SUMCSV |

Sum the last or first n columns in a range | SUMCOLUMNS |

Sum the top n values in a range | SUMHIGH |

Sum every n rows | SUMN_ROWS |

Sum every nth column | SUMCOLUMNS |

Sum every nth row | SUMN |

Sum multiple rows or columns | SUM, XLOOKUP |

Sum visible rows in a filtered list | SUMVISIBLE |

Sum cells that contain formulas | SUMPRODUCT, ISFORMULA |

## TEXT and STRING manipulation formulas

## LOOKUP formulas

**XLOOKUP changes everything**. This section contains modern lookup formulas.

Formula | Related Functions |
---|---|

Left lookup formula | XLOOKUP |

Lookup and return multiple values in one cell | MLOOKUP |

Lookup across multiple Worksheets | XLOOKUP |

Lookup and return the nth value | NVLOOKUP |

Lookup row and column | XLOOKUP |

Lookup with multiple criteria | XLOOKUP |

Lookup and return match text | XLOOKUP |

Lookup and find the closest match | XLOOKUP, INDEX, MATCH |

Lookup and find the last match | XLOOKUP |

Lookup values between two numbers | XLOOKUP |

Lookup and find the first text value in a range | HLOOKUP, XLOOKUP |

Lookup the first or last positive value in a list | XLOOKUP |

Lookup the name of the nth largest value | LARGE, XLOOKUP |

Lookup with logical criteria | XLOOKUP |

Lookup formula with boolean logic | XLOOKUP |

Find the second match in a range | XLOOKUP |

Nested lookup using XLOOKUP | XLOOKUP |

XLOOKUP returns blank if blank | XLOOKUP |

Lookup and return all matches | XLOOKUP |

Lookup multiple matches using VLOOKUP | FILTER |

### Dynamic Array Formulas

Formula | Related Functions |
---|---|

Count unique and distinct values | UNIQUE |

Extract common values from two lists | COMPARE, FILTER, COUNTIF |

Extract unique values with criteria | UNIQUE, FILTER |

Get column totals | TOTALCOL, SEQUENCE, MMULT |

Get row totals | TOTALROW, SEQUENCE, MMULT |

## IF Formulas

Formula | Related Functions |
---|---|

MEDIAN IF Formula | MEDIANIF |

## DATE and TIME Formulas

Formula | Related Functions |
---|---|

Add years to date | XDATE |

Convert date to number or number to date |

## Formulas and Functions

There are two ways to perform calculations in Excel; using Formulas and Functions. But first, it’s time to clarify their differences.

What is a Formula? Excel Formulas are math statements that can perform calculations and manipulate the values of other cells. Also, it can test conditions and frequently uses logical operators.

Let’s see a simple example: =B1 + B2 + B3 + B4 finds the sum of the cell values between cell B1 and cell B4.

What is a function? Functions are built-in presets, so there are hard-coded Excel formulas. With its help, you can avoid manual data entry, and it has small, user-friendly names.

For example, the =SUM(B1:B4) formula uses the SUM function to sum all cell B1 to B4 values.

## Best practices for using Excel Formulas

**Toggle between relative and absolute reference**. First, select the cell that contains the formula and use the F2 shortcut to enter the cell edit mode. Then, press F4 to switch between the reference types.

**Apply autocomplete formula function**. First, type an equal sign (=) and type the first character of the function. Excel shows a dynamic list of built-in functions. Next, select the function that you want to use.

**Use Function ScreenTips**. Type an equal sign and enter the function name. To add arguments, type the opening parenthesis. A screen tip will appear and show your formula’s corresponding argument.

**Useful resources to learn Excel Formula**s

Finally, if you want to learn more about Excel Formulas, we recommend you the related articles below: