Formula answers you can test
931 answers to the spreadsheet questions people ask most. Every formula on these pages has been run and checked by our formula engine, sits in a live sheet you can edit, and comes with an exercise that grades your own version.
Most asked
- How to use VLOOKUP to find an exact match in Excel
=VLOOKUP(G1,A2:D9,3,FALSE) - How to count cells that contain specific text in Excel
=COUNTIF(A2:A10,"*"&D1&"*") - How to sum values based on criteria in Excel (SUMIF)
=SUMIF(B2:B11,G1,D2:D11) - How to use IF with multiple conditions in Excel
=IF(AND(B2>=$G$1,C2>=$G$2),"Bonus","No bonus") - How to find duplicates in a column in Excel
=IF(COUNTIF($A$2:$A$10,A2)>1,"Duplicate","") - How to calculate percentage change between two numbers in Excel
=(C2-B2)/B2 - How to calculate age from a date of birth in Excel
=DATEDIF(B2,$F$1,"y") - How to look up a value with two criteria in Excel
=XLOOKUP(1,(A2:A8=G1)*(B2:B8=G2),D2:D8) - How to filter rows based on a condition with a formula in Excel
=FILTER(A2:C10,B2:B10=G1) - How to count unique values in Excel
=COUNTA(UNIQUE(A2:A10)) - How to calculate the number of days between two dates in Excel
=C2-B2 - How to extract the first name from a full name in Excel
=TEXTBEFORE(A2," ")
By topic
Counting formulas
Count rows that match one condition or several, between dates, by text, blanks and unique values.
- Count cells between two dates
- Count cells that contain specific text
- Count with multiple criteria
- Count unique values
- Count cells that are not blank
- Count cells greater than a number
Sum formulas
Totals with conditions, running totals, totals across columns and ranges that grow.
- Sum values based on criteria in Excel (SUMIF)
- Sum with multiple criteria in Excel (SUMIFS)
- Calculate a running total
- Sum values between two dates
- Sum if a cell contains specific text
- Sum the top 3 values
Lookup formulas
Find a value and bring back what goes with it: by ID, by two keys, the last match, the closest match.
- Use VLOOKUP to find an exact match
- Look up a value to the left
- Look up a value with two criteria
- Find the last occurrence of a value
- Look up a value between two numbers
- Return a message instead of #N/A in a lookup
IF and logic formulas
Flags, pass and fail, grades, multiple conditions and hiding errors the right way.
- Check if a cell contains specific text
- Use IF with multiple conditions
- Assign letter grades with IF or IFS
- Check if a cell is blank
- Replace #DIV/0! with 0 or a blank
- Check if a number is between two values
Text formulas
Split, join, extract and clean text: names, emails, codes and messy imports.
- Extract the first name from a full name
- Extract text after a character
- Combine text from two cells
- Remove extra spaces from text
- Capitalize the first letter of each word
- Count characters in a cell
Date formulas
Days between dates, working days, ages, month ends, quarters and adding months.
- Calculate the number of days between two dates
- Count working days between two dates
- Calculate age from a date of birth
- Add months to a date
- Get the last day of the month
- Get the month name from a date
Dynamic array formulas
FILTER, SORT, UNIQUE and friends: one formula that returns a whole table.
- Filter rows based on a condition with a formula
- Use FILTER with multiple criteria
- Sort data with a formula
- Get a list of unique values
- Get the top 3 rows by value
- Return multiple matching values in one cell
Duplicates and data cleanup formulas
Find, flag, count and remove duplicates, and fix numbers stored as text.
- Find duplicates in a column
- Compare two lists and find matches
- Convert numbers stored as text to numbers
- Remove a specific character from text
- Mark the first occurrence of each value
- Add leading zeros to numbers
Rounding and math formulas
Round to any step, round up and down, weighted averages, ranks and remainders.
- Round to the nearest 100 or 1000
- Always round up
- Calculate a weighted average
- Rank numbers
- Calculate an average without zeros
- Multiply a column by the same number
Percentage formulas
Percent of total, percent change, adding and removing a percentage, and margins.
- Calculate percentage change between two numbers
- Calculate the percentage of a total
- Add a percentage to a number
- Subtract a percentage from a number
- Calculate what percentage one number is of another
- Calculate profit margin percentage
Formula error fixes
Why a formula shows #N/A, #VALUE!, #DIV/0!, #REF!, #NAME? or #SPILL!, or a wrong answer, and the formula that fixes it.
- Fix VLOOKUP #N/A caused by extra spaces
- Fix VLOOKUP #N/A with numbers stored as text
- Fix VLOOKUP #N/A when IDs have leading zeros
- Fix VLOOKUP #N/A errors after filling the formula down
- Why does VLOOKUP return the wrong value in Excel?
- Why does an approximate-match VLOOKUP return #N/A?
Time formulas
Hours between times, night shifts, overtime, breaks and pay, rounding to 15 minutes, and converting decimal hours.
- Calculate the hours between two times
- Calculate time worked across midnight
- Calculate the time difference in minutes
- Calculate hours between two dates and times
- Convert decimal hours to hours and minutes
- Convert time to decimal hours
Google Sheets formulas
QUERY, ARRAYFORMULA, REGEX functions, SPLIT, SORTN and other formulas that work the Google Sheets way.
- Use the QUERY function
- Use QUERY with a text condition
- Use a cell reference in a QUERY
- Filter between two dates with QUERY
- Sum by category with QUERY
- Count rows by group with QUERY
Finance formulas
Loan payments, interest, savings growth, NPV and IRR, CAGR, depreciation and break-even.
- Calculate a loan payment
- Calculate a monthly mortgage payment
- Calculate total interest paid on a loan
- Calculate how many months to pay off a loan
- Calculate the interest rate on a loan
- Calculate the future value of savings
How these answers are checked
Each answer is data, not just text: a sample sheet, the formula, the expected result and the alternatives. Before a page is published, our formula engine runs the formula, compares it with the expected result, runs the Google Sheets version in Sheets mode, checks that every alternative agrees, and checks that the practice exercise rejects a typed-in answer. If any check fails, the page isn't built. Last run 2026-11-21.
Want the reasoning behind a function rather than a quick answer? See the function library or the lessons.