Skip to main content

Excel Formulas Examples | ExtendOffice

Count
Count cells equal to COUNTIFSUMPRODUCTEXACT
Count cells not equal to COUNTIF
Count cells equal to either x or y COUNTIFSUMPRODUCTSUM
Count cells equal to both x and y COUNTIFS
Count cells greater than or less than COUNTIF
Count number of cells between two values / dates COUNTIFS
Count number of cells not between two given numbers COUNTIFSUMPRODUCT
Count number of cells equals to one of many values COUNTIFSUMPRODUCT
Count number of cells not equal to many values COUNTIFCOUNTASUMPRODUCT ISNAMATCH
Count number of cells not equal to x or y COUNTIFSSUMPRODUCT
Count blank or nonblank cells COUNTBLANKCOUNTIF
Count cells that begin/end with specific text COUNTIF
Count cells that contain either x or y SUMPRODUCTISNUMBERFIND
Count cells that contain specific text with case sensitive SUMPRODUCTISNUMBERFIND
Count How Many Cells Contain Errors SUMPRODUCTISNUMBERSUM
Count number of cells contain numeric or non-numeric values SUMPRODUCTISNUMBERNOTCOUNT
Count number of cells that contain odd or even numbers SUMPRODUCTMOD
Count number of cells that contain positive or negative values COUNTIF
Count number of cells that contain specific number of characters COUNTIFSUMPRODUCTLENN
Count Number Of Cells That Contain Specific Text COUNTIFSUMPRODUCTFINDISNUMBER
Count the number of text cells COUNTIFCOUNTIFS
Count cells that do not contain errors ISERRORSUMPRODUCTSUM
Count cells that do not contain specific text COUNTIFCOUNTIFS
Count Number Of Cells That Do Not Contain Many Values SUMMMULTISNUMBERTRANSPOSEROW
Count Number Of Dates By Day Of Week SUMSUMPRODUCTWEEKDAY
Count Multiple Criteria With NOT Logic SUMPRODUCTMATCH
Count Numbers That Begin With A Specific Number SUMPRODUCTLEFT
Count Occurrences Of Particular Text In Entire Excel Workbook SUMPRODUCTCOUNTIFINDIRECT
Count Or Sum Only Whole Numbers SUMPRODUCT MOD
Count Numbers Where The Nth Digit Equals To X SUMPRODUCT MID
Count cells that match two or more criteria COUNTIFS
Count matches between two columns SUMPRODUCT
Count number of dates by year, month SUMPRODUCTYEARMONTH
Count rows if meet internal criteria SUMPRODUCT
Count Rows If Meet Multiple Criteria SUMPRODUCT
Count All Matches / Duplicates Between Two Columns SUMPRODUCTCOUNTIFCOUNTMATCHISNUMBER
Count Number Of Rows That Contain Specific Values SUMMMULTTRANSPOSECOLUMN
Count Number Of Rows With Multiple OR Criteria SUMPRODUCT
Count Unique Numeric Values Based On Criteria SUMFREQUENCYROWSUNIQUEFILTER
Count Unique Numeric Values Or Dates In A Column SUMFREQUENCYISNUMBERUNIQUECOUNTIFCOUNT
Count Number Of Visible Rows In A Filtered List SUBTOTAL
Count Unique Values In A Range SUMPRODUCTFREQUENCYMATCHROWCOUNTIF
Count Unique Values With Criteria SUMFREQUENCYMATCHROWIF
Count Visible Rows With Criteria SUMPRODUCTSUBTOTALROWMIN
Use COUNTIF On A Non-Contiguous Range SUMCOUNTIFINDIRECT
Countifs With OR Logic For Multiple Criteria SUMCOUNTIFSUMPRODUCTMATCH ISNUMBER
Calculate Percentage Breakdown Of Items In A List COUNTIFCOUNTA
Create A Summary Count By Month With COUNTIFS COUNTIFSEDATE
Do Running Count Of Occurrence In A List COUNTIFIF
Summary Count Of Non-Blank Categories COUNTIFS
Sum
3D sum or sumif across multiple worksheets SUMSUMPRODUCTSUMIFINDIRECT
Calculate Running Total SUM
Get Subtotal By Invoice Number COUNTIFSUMIFIF
Sum Values By Group SUMIFIF
Subtotal By Colors SUMIF
Two-Way Summary Counting COUNTIF
Subtotal Invoice Amounts By Age SUMIF
Sum All Number Cells Ignoring Errors SUMIFIFERRORAGGREGATE
Sum Smallest Or Bottom N Values SUMPRODUCTSMALL
Sum Smallest Or Bottom N Values Based On Criteria SUMSMALLIF
Sum Values By Month (With Or Without Year) SUMIFSSUMPRODUCTEOMONTHMONTH
Sum Values By The Week Of A Given Date SUMIFS
Sum Values By Weeknum Or Weekday WEEKNUMSUMPRODUCTWEEKDAY
Sum Every N Rows Or Columns SUMOFFSETROWCOLUMNS
Sum Every Nth Row Or Column SUMPRODUCTMODROWCOLUMN
Sum If Begins With Or Ends With Specific Text Or Characters SUMIF
Sum If Cells Contain Specific Text In Another Column SUMIFSUMPRODUCTSEARCHISNUMBER
Sum If Between Two Values SUMIFS
Sum Only Cells Containing Formulas SUMPRODUCTISFORMULA
Sum Values By Year SUMPRODUCT SUMIFS DATE YEAR
Sum If Cells Contain An Asterisk SUMIF
Sum If Cells Contain Both X And Y SUMIFS
Sum If Cells Contain Or Equal To Either X Or Y SUMPRODUCT ISNUMBER SEARCH SUMIFS
Sum If Date Is Between Two Dates SUMIFS
Sum If Cells Are Equal Or Not Equal To A Certain Value SUMIF
Sum If Greater Than Or Less Than A Specific Value SUMIF
Sum If Date Is Greater Than Or Less Than A Specific Date SUMIF
Sum If Equal To One Of Many Things SUMIFSUMPRODUCT
Sum Values Based On Blank Or Not-Blank Criteria SUMIF
Sumif With Multiple Criteria Based On OR And AND Logic SUMIFSUMIFS
Sum Multiple Columns If One Criterion Is Met SUMPRODUCT
Sum The Last N Columns SUM INDEX COLUMNS
Sum Top N Values Or Top N Values With Criteria SUMPRODUCT LARGE
Sum Values Based On Column Or Both Column And Row SUMPRODUCT
Sum Values In Last N Days Based On Criteria SUMIFS TODAY
Sum only visible cells or rows in a filtered list SUBTOTAL
Sum values in horizontal range SUMIFS
Use SUMIFS with multiple criteria based on OR logic SUMSUMIFS
Use SUMPRODUCT with IF function SUMPRODUCT
Financial
Calculate interest payments per period or total IPMTCUMIPMT
Lookup
Approximate match with INDEX and MATCH INDEXMATCH
Approximate match with VLOOKUP VLOOKUP
Case-sensitive lookup INDEXMATCHVLOOKUPEXACTCHOOSE
Case-sensitive lookup to return matching numbers SUMPRODUCTMATCHEXACT
Count missing values SUMPRODUCTMATCHISNACOUNTIF
Dynamic worksheet or workbook reference INDIRECT
Exact match with INDEX and MATCH INDEXMATCH
Exact match with VLOOKUP VLOOKUP
Find longest or shortest text string in a column or row INDEXMATCHLENMAXMIN
Find longest text string with criteria INDEXMATCHLENMAX
Find missing values IFISNAMATCHVLOOKUPCOUNTIF
Get cell address of lookup results INDEXMATCHCELL
Get first non-blank value in a column or row INDEXMATCHISBLANK
Get first numeric value in a column or row INDEXMATCHISNUMBER
Get first text value in a column INDEXMATCHVLOOKUP
Get first text value in a row HLOOKUP
Get information corresponding to maximum value INDEXMATCHMAX
Get information corresponding to minimum value INDEXMATCHMIN
Get last text value in a column INDEXMATCH
Get student or employee information with VLOOKUP VLOOKUP
Get value at given row and column INDEXMATCHSMALL
INDEX and MATCH across multiple columns INDEXMATCHMMULTTRANSPOSECOLUMN
INDEX and MATCH with multiple arrays INDEXMATCHCHOOSE
Left lookup with INDEX and MATCH INDEXMATCH
Left lookup with VLOOKUP VLOOKUPCHOOSE
Locate maximum value in a range MATCHMAX
Locate first error MATCHISERROR
Locate first match that does not begin with MATCHLEFTIF
Locate first match that does not contain MATCHISNUMBERSEARCH
Locate first partial match with wildcards MATCH
Lookup a value containing specific text with wildcards INDEXMATCH
Lookup closest match INDEXMATCHABSMIN
Lookup closest match value with multiple criteria IFINDEXMATCH
Lookup and retrieve entire column INDEXMATCHSUMAVERAGEMAXLARGE
Lookup and retrieve entire row INDEXMATCHSUMAVERAGEMIN
Lookup next largest match with INDEX and MATCH INDEXMATCH
Lookup the first partial match number MATCHTEXTINDEX
Lookup values in descending order INDEXMATCH
Lookup values from another worksheet or workbook VLOOKUP
Merge tables with INDEX and MATCH INDEXMATCH
Multiple-criteria lookup with INDEX and MATCH INDEXMATCH
Partial match with VLOOKUP VLOOKUP
Retrieve first list value from a cell INDEXMATCHSEARCHISNUMBER
Retrieve first matching value in cell against a list INDEXMATCHSEARCHAGGREGATE
Retrieve information associated with lowest n values VLOOKUP
Retrieve nth match with INDEX INDEXROWSMALLIF
Retrieve nth match with VLOOKUP VLOOKUP
Shipping cost calculator VLOOKUP
Two-way approximate match with multiple criteria INDEXMATCHIF
Two-way lookup with INDEX and MATCH INDEXMATCH
Vlookup with dymanic sheet name VLOOKUPINDIRECT
Math
Convert binary value to decimal or octal or hex value BIN2DECDECIMALBIN2OCTBIN2HEX
Convert hexadecimal value to decimal or binary or octal value HEX2DECDECIMALHEX2OCTHEX2BIN
Convert octal value to decimal or binary or hexadecimal value OCT2DECDECIMALOCT2HEXOCT2BIN
Convert decimal value to binary or octal or hexadecimal value DEC2DECDEC2OCTDEC2HEX
Convert decimal number to IP address MID
Convert decimal to whole number in Excel ROUND ROUNDDOWN ROUNDUP
Date and Time
Add business days to date WORKDAYWORKDAT.INTL
Add hours to time TIME MOD
Add minutes to time TIME MOD
Add hours minutes seconds to time TIME
Add months to date  
Add years to date DATEYEARMONTHDAY
Assign points based on late time IFVALUE
Add or subtract days to date  
Calculate days hours minutes seconds between two dates TEXT
Calculate days remaining from today MAXTODAY
Calculate days remaining between dates  
Calculate days remaining in month EOMONTH
Calculate days remaining in year  
Calculate difference between two dates DATEDIF
Calculate difference between two times IF
Calculate expiry date EOMONTHEDATE
Calculate hours minutes seconds between to times HOUR  MINUTE SECOND
Calculate network time MOD
Calculate retirement date EDATE
Calculate overlapping days MAXMIN
Calculate overtime pay  
Calculate years months days between two dates DATEDIF
Check if dates is workday WORKDAY   WORKDAY.INTL
Check if date is last n day TODAY    AND
Check if date is last n month from today TODAY    AND     EOMONTH
Check if two dates are in same year month MONTH  YEAR
Combine date and time TEXT   CONCATENATE
Convert date to Julian TEXT  YEAR  DATE
Convert date to month year day TEXT
Convert date to text TEXT
Convert datetime string to datetime LEFT  MID
Convert decimal hours to time  
Convert decimal minutes to time  
Convert month name to number DATEVALUE   MONTH
Convert number to date TEXT  LEFT  MID  RIGHT  DATE
Convert time to another time zone MOD
Convert time to decimal hours  
Convert time to decimal minutes  
Convert time to decimal seconds  
Convert time to money  
Convert time to Unix DATE
Convert timestamp to time MID  TIME
Convert text to time DATEVALUE  VALUE  
Count days of month EOMONTH  DAY
Count days until expiration date TODAY
Count day of week between two dates INDIRECR  ROW  WEEKDAY  SUMPRODUCT
Count day of week in a date range WEEKDAY  SUMPRODUCT
Count days from Today TODAY  IF  ISBLANK  ABS
Count days between two dates DATEDIF
Count dyas left in current month year EOMONTH  YEAR  DATE
Count holidays between two dates SUMPRODUCT
Count months between two dates DATEDIF   YEARFRAC
Count number of calls in a time range COUNTIFS
Count times in a range COUNTIFS
Count workday only NETWORKDAYS  NETWORKDAYS.INTL
Create date range from two dates TEXT
Create dynamic date list ROWS
Create weekly date range ROWS  TEXT
Custom format weekday name WEEKDAY  CHOOSE
Display current date or time TODAY   NOW
Extract time only from datetime in Excel TIMEMOD
Extract date only from datetime in Excel INTTRUNC DATE
Find earlest or latest date of each group IF MAX  MIN
Find last weekday of month EOMONTH  WEEKDAY
Find next date in schedule list  
Get day name from given date TEXT  WEEKDAY  CHOOSE
Get date from day month year DATE
Get first or last workday in month YEAR     MONTH       DATE  WORKDAY
Get fiscal month from date MONTH  CHOOSE
Get fiscal quarter from date MONTH   CHOOSE
Get fiscal year from date MONTH   YEAR
Get first or last day of month by given date DAY  EOMONTH
Get first day of month by month text name  IF
Get first or last day of previous month EOMONTH
Get midpoint of two dates SUM  WORKDAY
Get Monday of week WEEKDAY
Get month from date MONTH
Get most recently day of week MOD
Get next specific weekday WEEKDAY
Get nth day of week in month by given date WEEKDAY DAY
Get nth day of year by given date YEAR  DATE
Get number of workday between two dates NETWORKDAYS  NETWORKDAYS.INTL
Get percentage of year complete or remained YEAR  DATE YEARFRAC
Get quarter from date MONTH  YEAR
Get same date last month or next month EDATE
Get same date last year or next year EDATE
Get week number from date in Excel WEEKNUM
Get work hours between two dates in Excel NETWORKDAYS NETWORKDAYS.INTL
Get or calculate age from birth date in Excel YEARFRAC DATEDIF
List holidays between two dates IF   TEXTJOIN
Text
Abbreviate Words or Names TEXTJOIN ISNUMBER ROW INDIRECT LEN MATCH MID TRIM LEFT SUBSTITUTE FIND UPPER
Add area code or country code to phone number CONCATENATE
Add character before each word SUBSTITUTE
Add comma after first word REPLACE FIND
Add comma between names FINDREPLACE TRIM SUBSTITUTE
Add dashes to phone number REPLACE
Add dashes to SSN in a cell LEFT MID RIGHT SUBSTITUTE
Add leading zeros to fix text length TEXT
Add text in middle LEFT MID REPLACE
Add space after comma TRIM SUBSTITUTE
Add space between number and text TRIMREPLACE MIN FIND MAX IFERROR ROW INDIRECT LEN
Capitalize first letter of text string or each word UPPERLEN FIND LOWER REPLACE LEFT MID
Check if a cell contains a specific text ISNUMBER SEARCH FIND
Check if cell contains all of many things SUMPRODUCT ISNUMBER SEARCH COUNTA
Check if cell contains one of many things SUMPRODUCT ISNUMBER SEARCH
Check if a cell contains one of several values but exclude other values SUMPRODUCT ISNUMBER SEARCH
Check if cell contains some texts but not contains others COUNT SEARCH AND
Convert letter to number COLUMN INDIRECT
Check if cell contains number COUNT FIND
Combine cells with line break CONCATENATE
Combine date and time into one cell in Excel TEXT
Combine text and date into same cell in Excel TEXT CONCATENATE
Count comma separated values in a cell LEN SUBSTITUTE TRIM
Count specific character in cell LEN SUBSTITUTE
Count specific character in a range of cells SUMPRODUCT LEN SUBSTITUTE
Count specific words in cell LEN SUBSTITUTE
Count specific words in a range of cells SUMPRODUCT LEN SUBSTITUTE
Count number of characters in a cell LEN
Count number of characters in a range of cells SUMPRODUCT LEN
Count number of words in a cell LEN TRIM SUBSTITUTE
Count number of words in a range of cells SUMPRODUCT LEN TRIM SUBSTITUTE
Use double quotes in formulas CHAR
Check if cell equals any value in list SUMPRODUCT
Clean and reformat telephone numbers SUBSTITUTE
Combine cells with commas TRIM SUBSTITUTE CONCATENATE
Combine initial and last name LEFT CONCATENATE
Compare two or more text strings EXACT IF COUNTIF
Concatenate cells but ignore blanks TEXTJOIN
Convert numbers to text in Excel TEXT FIXED
Convert text to number in Excel VALUE RIGHT LEFT MID
Count keywords cell contains based on a list SUMPRODUCT ISNUMBER SEARCH
Count lines which are separated by line breaks in a cell or a range LEN SUBSTITUTE
Show specific text based on value in Excel REPT IF
Extract all words but first or last from a cell RIGHT LEN FIND LEFT SUBSTITUTE
Extract capital letters only SUBSTITUTE
Extract extension from filename RIGHT LEN FIND SEARCH REPLACE
Extract filename from a path in Excel MID FIND SUBSTITUTE LEN IFERROR
Extract first line of cell SEARCH LEFT
Extract first and last name from email FIND LEFT LEN RIGHT
Extract folder name from path FIND SUBSTITUTE LEN RIGHT
Extract from right until a character SEARCH RIGHT SUBSTITUTE LEN IFERROR
Extract initials from names MID IF FIND LEFT
Extract multiple lines from a cell TRIM MID REPT LEN SUBSTITUTE
Extract last line of text from a multi-line cell SUBSTITUTE REPT RIGHT TRIM
Extract nth word from text string in Excel SUBSTITUTE REPT MID TRIM LEN
Extract path from full path FIND SUBSTITUTE LEN LEFT
Extract substring from text string in Excel MID LEFT RIGHT SEARCH
Extract the last two words from a cell MID FIND SUBSTITUTE LEN
Extract text between parentheses from text string MID SEARCH
Extract word beginning with a specific character in Excel MID TRIM LEFT FIND REPT LEN SUBSTITUTE
Extract word which containing specific text in Excel MID TRIM FIND REPT MAX SUBSTITUTE
Extract first initial and last name from full name LEFT RIGHT FIND LEN
Extract first middle and last names from full name in Excel LEFT RIGHT FIND LEN MID SEARCH SUBSTITUTE
Extract text before or after second space or comma LEFT MID FIND SUBSTITUTE TRIM
Extract text after the last instance of a specific character RIGHT SEARCH LEN SUBSTITUTE TRIM REPT
Extract text between first and second comma from text strings MID SEARCH FIND SUBSTITUTE
Find and replace multiple values in Excel SUBSTITUTE INDEX
Find nth occurrence of character in a cell SUBSTITUTE FIND
Find most frequenst text in a range MODE INDEX MATCH
Find most frequenst text with criteria MODE INDEX IF MATCH
Find position of nth occurrence FIND SUBSTITUTE
Flip or reverse first and last names in Excel list MID SEARCH LEN
Get or extract the first word from text string in Excel FIND LEFT
Get or extract the last word from text string in Excel TRIM RIGHT REPT
If cell contains text then display in Excel IF ISNUMBER SEARCH
Make text same length REPT LEN
Make first letter lowercase LEFT LOWER REPLACE
Move or combine multiple cell contents into one cell CONCATENATE TRANSPOSE TEXTJOIN
Normalize text TRIM SUBSTITUTE LOWER
Remove extension from filename FIND LEFT
Remove first n characters LEN RIGHT
Remove first or last word from a string RIGHT LEN FIND LEFT TRIM SUBSTITUTE
Remove from right of text LEN LEFT
Excel remove last or trailing comma in a cell IF RIGHT LEFT LEN
Remove leading and tailing space CLEAN TRIM SUBSTITUTE
Remove line breaks from cells in Excel CLEAN TRIM SUBSTITUTE
Remove middle initial from full name in Excel LEFT RIGHT FIND TRIM SUBSTITUTE REPT
Remove prefix or suffix from string RIGHT LEN LEFT
Remove text from cell by matching the content SUBSTITUTE
Remove text from a cell based on specific position REPLACE SUBSTITUTE
Remove text based on variable position in Excel REPLACE FIND
Remove unwanted characters from cell in Excel SUBSTITUTE
Remove text before or after first or last specific character from text strings RIGHT LEFT FIND SEARCH LEN SUBSTITUTE
Remove text after or before the second or nth space from text strings RIGHT LEFT FINDLEN SUBSTITUTE
Remove text within parentheses or brackets from text strings FIND MID LEN SUBSTITUTE
Replace a specific character in a cell with another SUBSTITUTE
Reverse the text string in a cell in Excel TEXTJOIN MID
Split dimensions into individual length, height and width LEFT MID RIGHT FIND LEN SUBSTITUTE
Split dimensions into two parts in Excel LEFT RIGHT FIND LEN SUBSTITUTE
Separate numbers from units of measurement MAX ISNUMBER VALUE MID LEFT TRIM RIGHT
Separate octets of IP address in Excel LEFT MID LEN FIND
Separate Email addresses to usernames and domains LEFT RIGHT LEN FIND
Split dollars and cents FIND RIGHT LEN
Split a cell by first space in Excel LEFT FIND RIGHT LEN
Split number into individual digits MID COLUMN
Split sentence into words SEARCH MID ROW COLUMN IF
Split text and numbers MID   FIND  LEFT RIGHT LEN
Split text string at specific character in a cell in Excel MID   FIND  LEFT RIGHT LEN
Split text with delimiter in a cell in Excel MID   TRIM  SUBSTITUTE LEN
Strip or remove non-numeric characters from text strings TEXTJOIN IFERROR MID ROW INDIRECT
Strip or remove numeric characters from text strings TEXTJOIN IF ISERR MID ROW INDIRECT LEN
Strip or remove html tags from text strings MID   LEN
Trim text to n words SUBSTITUTE   FIND  LEFT