# Formula answers you can test

> 60 answers to the Excel and Google Sheets formula questions people ask most. Every formula was run and checked by the formula engine, sits on a live sheet you can edit, and comes with a graded exercise.

Source: https://spreadsheetpractice.com/formulas · Updated October 29, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 29, 2026

## Counting formulas

Count rows that match one condition or several, between dates, by text, blanks and unique values. [All count answers](https://spreadsheetpractice.com/formulas/topic/count)

- [How to count cells between two dates in Excel](https://spreadsheetpractice.com/formulas/count-cells-between-two-dates): Use COUNTIFS with two conditions on the same date column: on or after the start date, and on or before the end date. `=COUNTIFS(B2:B11,">="&G1,B2:B11,"<="&G2)`
- [How to count cells that contain specific text in Excel](https://spreadsheetpractice.com/formulas/count-cells-that-contain-specific-text): Use COUNTIF with wildcards: =COUNTIF(range,"*"&text&"*") counts every cell that contains the text anywhere. `=COUNTIF(A2:A10,"*"&D1&"*")`
- [How to count with multiple criteria in Excel](https://spreadsheetpractice.com/formulas/count-with-multiple-criteria): Use COUNTIFS with one range-and-condition pair per criterion; a row is counted only when all of them are true. `=COUNTIFS(B2:B10,G1,C2:C10,">="&G2,D2:D10,"Won")`
- [How to count unique values in Excel](https://spreadsheetpractice.com/formulas/count-unique-values): In Excel 365, wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(range)). In older versions, use =SUMPRODUCT(1/COUNTIF(range,range)). `=COUNTA(UNIQUE(A2:A10))`
- [How to count cells that are not blank in Excel](https://spreadsheetpractice.com/formulas/count-cells-that-are-not-blank): Use =COUNTA(range). It counts every cell that has anything in it: numbers, text, dates and errors. `=COUNTA(C2:C8)`
- [How to count cells greater than a number in Excel](https://spreadsheetpractice.com/formulas/count-cells-greater-than-a-number): Use COUNTIF with the operator in quotes joined to the cell: =COUNTIF(range,">"&E1). `=COUNTIF(B2:B8,">"&E1)`

## Sum formulas

Totals with conditions, running totals, totals across columns and ranges that grow. [All sum answers](https://spreadsheetpractice.com/formulas/topic/sum)

- [How to sum values based on criteria in Excel (SUMIF)](https://spreadsheetpractice.com/formulas/sumif-sum-based-on-criteria): Use =SUMIF(criteria_range, criteria, sum_range): it adds the numbers in sum_range on the rows where criteria_range matches. `=SUMIF(B2:B11,G1,D2:D11)`
- [How to sum with multiple criteria in Excel (SUMIFS)](https://spreadsheetpractice.com/formulas/sum-with-multiple-criteria): Use SUMIFS with the sum range first, then one range-and-condition pair per criterion: =SUMIFS(sum_range, range1, crit1, range2, crit2). `=SUMIFS(D2:D11,B2:B11,G1,C2:C11,G2)`
- [How to calculate a running total in Excel](https://spreadsheetpractice.com/formulas/running-total): Sum from a locked first cell to the current row, =SUM($B$2:B2), and fill it down. The range grows by one row each time. `=SUM($B$2:B2)`
- [How to sum values between two dates in Excel](https://spreadsheetpractice.com/formulas/sum-between-two-dates): Use SUMIFS with two date conditions on the same column: =SUMIFS(sum_range, dates, ">="&start, dates, "<="&end). `=SUMIFS(D2:D11,A2:A11,">="&G1,A2:A11,"<="&G2)`
- [How to sum if a cell contains specific text in Excel](https://spreadsheetpractice.com/formulas/sum-if-cell-contains-text): Use SUMIF with wildcards around the text: =SUMIF(range,"*"&text&"*",sum_range). `=SUMIF(A2:A9,"*"&E1&"*",B2:B9)`
- [How to sum the top 3 values in Excel](https://spreadsheetpractice.com/formulas/sum-top-3-values): Use LARGE with an array of ranks inside SUM: =SUM(LARGE(range,{1,2,3})). `=SUM(LARGE(B2:B8,{1,2,3}))`

## Lookup formulas

Find a value and bring back what goes with it: by ID, by two keys, the last match, the closest match. [All lookup answers](https://spreadsheetpractice.com/formulas/topic/lookup)

- [How to use VLOOKUP to find an exact match in Excel](https://spreadsheetpractice.com/formulas/vlookup-exact-match): Use =VLOOKUP(lookup_value, table, column_number, FALSE). The FALSE at the end makes it look for an exact match. `=VLOOKUP(G1,A2:D9,3,FALSE)`
- [How to look up a value to the left in Excel](https://spreadsheetpractice.com/formulas/lookup-value-to-the-left): Use XLOOKUP, which can return any column: =XLOOKUP(value, lookup_column, result_column). In older versions use INDEX and MATCH. `=XLOOKUP(G1,D2:D7,A2:A7)`
- [How to look up a value with two criteria in Excel](https://spreadsheetpractice.com/formulas/lookup-with-two-criteria): Multiply two tests to build the lookup array: =XLOOKUP(1,(range1=crit1)*(range2=crit2),result_range). `=XLOOKUP(1,(A2:A8=G1)*(B2:B8=G2),D2:D8)`
- [How to find the last occurrence of a value in Excel](https://spreadsheetpractice.com/formulas/find-last-match): Use XLOOKUP with search mode -1 to search from the bottom up: =XLOOKUP(value, lookup_range, return_range,,0,-1). `=XLOOKUP(F1,B2:B8,A2:A8,,0,-1)`
- [How to look up a value between two numbers in Excel](https://spreadsheetpractice.com/formulas/lookup-value-between-two-numbers): Put the lower bound of each band in a sorted column and use an approximate match: =XLOOKUP(value, lower_bounds, results,,-1). `=XLOOKUP(E1,A2:A6,B2:B6,,-1)`
- [How to return a message instead of #N/A in a lookup](https://spreadsheetpractice.com/formulas/lookup-return-text-if-not-found): Give XLOOKUP its if_not_found argument, =XLOOKUP(value, lookup, return, "Not found"), or wrap an older lookup in IFNA. `=XLOOKUP(G1,A2:A9,C2:C9,"Not found")`

## IF and logic formulas

Flags, pass and fail, grades, multiple conditions and hiding errors the right way. [All logic answers](https://spreadsheetpractice.com/formulas/topic/logic)

- [How to check if a cell contains specific text in Excel](https://spreadsheetpractice.com/formulas/if-cell-contains-text): Combine SEARCH with ISNUMBER inside IF: =IF(ISNUMBER(SEARCH(text,cell)),"Yes","No"). `=IF(ISNUMBER(SEARCH($E$1,A2)),"Yes","No")`
- [How to use IF with multiple conditions in Excel](https://spreadsheetpractice.com/formulas/if-with-multiple-conditions): Put the conditions in AND (all must be true) or OR (any can be true) inside IF: =IF(AND(cond1,cond2),"yes","no"). `=IF(AND(B2>=$G$1,C2>=$G$2),"Bonus","No bonus")`
- [How to assign letter grades with IF or IFS in Excel](https://spreadsheetpractice.com/formulas/nested-if-grades): Use IFS with the conditions from the highest grade down: =IFS(score>=90,"A",score>=80,"B",score>=70,"C",score>=60,"D",TRUE,"F"). `=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F")`
- [How to check if a cell is blank in Excel](https://spreadsheetpractice.com/formulas/if-cell-is-blank): Test the cell against "": =IF(C2="","Not shipped",C2-B2). ISBLANK(C2) works too, but only for truly empty cells. `=IF(C2="","Not shipped",C2-B2)`
- [How to replace #DIV/0! with 0 or a blank in Excel](https://spreadsheetpractice.com/formulas/replace-div0-error-with-zero): Wrap the division in IFERROR: =IFERROR(B2/C2,0). For only the zero case, test the divisor: =IF(C2=0,0,B2/C2). `=IFERROR(B2/C2,0)`
- [How to check if a number is between two values in Excel](https://spreadsheetpractice.com/formulas/if-between-two-numbers): Use AND with both limits: =IF(AND(value>=low,value<=high),"In range","Out of range"). `=IF(AND(B2>=$F$1,B2<=$F$2),"In range","Out of range")`

## Text formulas

Split, join, extract and clean text: names, emails, codes and messy imports. [All text answers](https://spreadsheetpractice.com/formulas/topic/text)

- [How to extract the first name from a full name in Excel](https://spreadsheetpractice.com/formulas/extract-first-name): In Excel 365 use =TEXTBEFORE(A2," "). In any version, =LEFT(A2,FIND(" ",A2)-1) takes everything before the first space. `=TEXTBEFORE(A2," ")`
- [How to extract text after a character in Excel](https://spreadsheetpractice.com/formulas/extract-text-after-a-character): Use =TEXTAFTER(A2,"@") in Excel 365. In any version, =MID(A2,FIND("@",A2)+1,LEN(A2)) returns everything after the character. `=TEXTAFTER(A2,"@")`
- [How to combine text from two cells in Excel](https://spreadsheetpractice.com/formulas/combine-text-from-two-cells): Join them with & and put a space in quotes between: =A2&" "&B2. TEXTJOIN joins whole ranges with a separator. `=A2&" "&B2`
- [How to remove extra spaces from text in Excel](https://spreadsheetpractice.com/formulas/remove-extra-spaces): Use =TRIM(A2). It removes spaces at the start and end and turns runs of spaces between words into one. `=TRIM(A2)`
- [How to capitalize the first letter of each word in Excel](https://spreadsheetpractice.com/formulas/capitalize-first-letter-of-each-word): Use =PROPER(A2). It capitalizes the first letter of every word and lowercases the rest. `=PROPER(A2)`
- [How to count characters in a cell in Excel](https://spreadsheetpractice.com/formulas/count-characters-in-a-cell): Use =LEN(A2). It counts every character, including spaces and punctuation. `=LEN(A2)`

## Date formulas

Days between dates, working days, ages, month ends, quarters and adding months. [All dates answers](https://spreadsheetpractice.com/formulas/topic/date)

- [How to calculate the number of days between two dates in Excel](https://spreadsheetpractice.com/formulas/days-between-two-dates): Subtract the earlier date from the later one: =C2-B2. Dates are numbers of days, so the difference is the number of days. `=C2-B2`
- [How to count working days between two dates in Excel](https://spreadsheetpractice.com/formulas/working-days-between-two-dates): Use =NETWORKDAYS(start, end, holidays). It counts Monday to Friday, both ends included, and skips any dates listed as holidays. `=NETWORKDAYS(B2,C2,$F$2:$F$3)`
- [How to calculate age from a date of birth in Excel](https://spreadsheetpractice.com/formulas/calculate-age-from-date-of-birth): Use =DATEDIF(birth_date, as_of_date, "y"). It returns completed years, so the age only goes up on the birthday. `=DATEDIF(B2,$F$1,"y")`
- [How to add months to a date in Excel](https://spreadsheetpractice.com/formulas/add-months-to-a-date): Use =EDATE(start_date, months). It moves the date by whole months and handles month ends correctly. `=EDATE(B2,C2)`
- [How to get the last day of the month in Excel](https://spreadsheetpractice.com/formulas/last-day-of-the-month): Use =EOMONTH(date, 0). The 0 means this month; 1 is next month and -1 is last month. `=EOMONTH(B2,0)`
- [How to get the month name from a date in Excel](https://spreadsheetpractice.com/formulas/get-month-name-from-a-date): Use =TEXT(date,"mmmm") for the full name or "mmm" for the short one. `=TEXT(A2,"mmmm")`

## Dynamic array formulas

FILTER, SORT, UNIQUE and friends: one formula that returns a whole table. [All dynamic arrays answers](https://spreadsheetpractice.com/formulas/topic/dynamic)

- [How to filter rows based on a condition with a formula in Excel](https://spreadsheetpractice.com/formulas/filter-rows-based-on-criteria): Use =FILTER(table, condition). It returns every matching row and spills the result below the formula. `=FILTER(A2:C10,B2:B10=G1)`
- [How to use FILTER with multiple criteria in Excel](https://spreadsheetpractice.com/formulas/filter-with-multiple-criteria): Multiply the conditions for AND and add them for OR: =FILTER(table,(cond1)*(cond2)). `=FILTER(A2:C10,(B2:B10=G1)*(C2:C10>=G2))`
- [How to sort data with a formula in Excel](https://spreadsheetpractice.com/formulas/sort-data-with-a-formula): Use =SORT(range, sort_column, -1) for largest first, or 1 for smallest first. The sorted copy spills and updates automatically. `=SORT(A2:B7,2,-1)`
- [How to get a list of unique values in Excel](https://spreadsheetpractice.com/formulas/list-unique-values): Use =UNIQUE(range) to list each value once, and wrap it in SORT for alphabetical order: =SORT(UNIQUE(range)). `=SORT(UNIQUE(B2:B10))`
- [How to get the top 3 rows by value in Excel](https://spreadsheetpractice.com/formulas/top-n-rows): Sort the table by the value column, largest first, and take the first rows: =TAKE(SORT(table,2,-1),3). In Google Sheets use QUERY with limit. `=TAKE(SORT(A2:B8,2,-1),3)`
- [How to return multiple matching values in one cell in Excel](https://spreadsheetpractice.com/formulas/return-multiple-matches-in-one-cell): Filter the matches and join them: =TEXTJOIN(", ",TRUE,FILTER(results,lookup_range=value)). `=TEXTJOIN(", ",TRUE,FILTER(B2:B8,A2:A8=E1))`

## Duplicates and data cleanup formulas

Find, flag, count and remove duplicates, and fix numbers stored as text. [All duplicates and cleanup answers](https://spreadsheetpractice.com/formulas/topic/cleanup)

- [How to find duplicates in a column in Excel](https://spreadsheetpractice.com/formulas/find-duplicates-in-a-column): Count each value in the whole column: =IF(COUNTIF($A$2:$A$10,A2)>1,"Duplicate",""). Any value that appears more than once is flagged. `=IF(COUNTIF($A$2:$A$10,A2)>1,"Duplicate","")`
- [How to compare two lists and find matches in Excel](https://spreadsheetpractice.com/formulas/compare-two-lists-for-matches): Check each item against the other list with COUNTIF: =IF(COUNTIF(other_list,A2),"In both","Only in list 1"). `=IF(COUNTIF($D$2:$D$5,A2),"Registered","Not yet")`
- [How to convert numbers stored as text to numbers in Excel](https://spreadsheetpractice.com/formulas/convert-text-to-numbers): Use =VALUE(A2), or multiply by 1. The result is a real number that sums, sorts and looks up correctly. `=VALUE(A2)`
- [How to remove a specific character from text in Excel](https://spreadsheetpractice.com/formulas/remove-a-character-from-text): Replace it with nothing: =SUBSTITUTE(A2,"-",""). SUBSTITUTE swaps every copy of the character. `=SUBSTITUTE(A2,"-","")`
- [How to mark the first occurrence of each value in Excel](https://spreadsheetpractice.com/formulas/mark-first-occurrence-of-duplicates): Count from the top of the list down to the current row: =IF(COUNTIF($A$2:A2,A2)=1,"First","Repeat"). `=IF(COUNTIF($A$2:A2,A2)=1,"First","Repeat")`
- [How to add leading zeros to numbers in Excel](https://spreadsheetpractice.com/formulas/add-leading-zeros): Format the number as text with a fixed number of digits: =TEXT(A2,"00000"). `=TEXT(A2,"00000")`

## Rounding and math formulas

Round to any step, round up and down, weighted averages, ranks and remainders. [All rounding and math answers](https://spreadsheetpractice.com/formulas/topic/math)

- [How to round to the nearest 100 or 1000 in Excel](https://spreadsheetpractice.com/formulas/round-to-the-nearest-hundred): Use ROUND with a negative number of digits: =ROUND(A2,-2) rounds to the nearest 100, and -3 to the nearest 1000. `=ROUND(A2,-2)`
- [How to always round up in Excel](https://spreadsheetpractice.com/formulas/always-round-up): Use =ROUNDUP(value,0) to round up to the next whole number, or =CEILING(value,step) to round up to a multiple. `=ROUNDUP(B2/C2,0)`
- [How to calculate a weighted average in Excel](https://spreadsheetpractice.com/formulas/weighted-average): Multiply each value by its weight, add them up and divide by the total weight: =SUMPRODUCT(values,weights)/SUM(weights). `=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)`
- [How to rank numbers in Excel](https://spreadsheetpractice.com/formulas/rank-values): Use =RANK.EQ(value, list) for largest = 1, adding 1 as the third argument for smallest = 1. Lock the list with $. `=RANK.EQ(B2,$B$2:$B$7)`
- [How to calculate an average without zeros in Excel](https://spreadsheetpractice.com/formulas/average-excluding-zeros): Use =AVERAGEIF(range,"<>0"). It averages only the cells that are not zero. `=AVERAGEIF(B2:B7,"<>0")`
- [How to multiply a column by the same number in Excel](https://spreadsheetpractice.com/formulas/multiply-a-column-by-a-number): Put the number in a cell and lock it with $: =B2*$E$1, then fill down. `=B2*$F$1`

## Percentage formulas

Percent of total, percent change, adding and removing a percentage, and margins. [All percentages answers](https://spreadsheetpractice.com/formulas/topic/percent)

- [How to calculate percentage change between two numbers in Excel](https://spreadsheetpractice.com/formulas/percentage-change): Subtract the old value from the new one and divide by the old: =(C2-B2)/B2, formatted as a percentage. `=(C2-B2)/B2`
- [How to calculate the percentage of a total in Excel](https://spreadsheetpractice.com/formulas/percentage-of-total): Divide each value by the total, with the total locked: =B2/SUM($B$2:$B$7), or =B2/$B$8 if the total is in a cell. `=B2/SUM($B$2:$B$7)`
- [How to add a percentage to a number in Excel](https://spreadsheetpractice.com/formulas/add-a-percentage-to-a-number): Multiply by 1 plus the percentage: =B2*(1+$E$1). To increase by 8%, multiply by 1.08. `=B2*(1+$F$1)`
- [How to subtract a percentage from a number in Excel](https://spreadsheetpractice.com/formulas/subtract-a-percentage): Multiply by 1 minus the percentage: =B2*(1-$F$1). A 15% discount is 85% of the price. `=B2*(1-$F$1)`
- [How to calculate what percentage one number is of another in Excel](https://spreadsheetpractice.com/formulas/what-percentage-of-a-number): Divide the part by the whole: =B2/C2, and format the result as a percentage. `=B2/C2`
- [How to calculate profit margin percentage in Excel](https://spreadsheetpractice.com/formulas/profit-margin): Divide profit by revenue: =(B2-C2)/B2, where B is the price or revenue and C the cost. `=(B2-C2)/B2`

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.