EXCEL FORMULAS

images
This page provides examples of how to use excel formulas to perform many common tasks. Although many people use the term ‘Excel Formulas’ to refer to the Excel Built-In Functions, the term ‘Excel Formula’ can encompass a wider range of operations in Excel. Therefore on this site, when we use the term ‘Excel Formulas’ (or ‘Excel Formulae’), we are generally referring to any combination of Excel Operators and/or Excel Functions. This may be as simple as a basic addition (eg. “=A1+B1”), or it could be a complex combination of Excel Operators and multiple nested Excel Functions.
Web : excel
Filter:
Formula Related Functions
Count cells between dates DATECOUNTIFS
Count cells between two numbers COUNTIFCOUNTIFS
Count cells equal to case sensitive SUMPRODUCTEXACT
Count cells equal to either x or y COUNTIF
Count cells equal to one of many things SUMPRODUCTCOUNTIF
Count cells greater than COUNTIF
Count cells less than COUNTIF
Count cells not equal to COUNTIF
Count cells not equal to x or y SUMPRODUCTCOUNTIFS
Count cells that are blank COUNTA
Count cells that are not blank COUNTA
Count cells that begin with COUNTIF
Count cells that contain either x or y ISNUMBERSUMPRODUCTCOUNTIFFIND
Count cells that contain errors ISERRISERRORSUMPRODUCT
Count cells that contain five characters COUNTIF
Count cells that contain negative numbers COUNTIF
Count cells that contain numbers COUNT
Count cells that contain odd numbers MODSUMPRODUCT
Count cells that contain positive numbers COUNTIF
Count cells that contain specific text ISNUMBERSUMPRODUCTCOUNTIFFIND
Count cells that contain text ISTEXTSUMPRODUCTCOUNTIF
Count cells that do not contain COUNTIF
Count cells that do not contain errors ISERRORNOTSUMPRODUCT
Count cells that end with COUNTIF
Count if row meets internal criteria SUMPRODUCT
Count if row meets multiple internal criteria SUMPRODUCT
Count if two criteria match SUMPRODUCTCOUNTIFS
Count matches between two columns SUMPRODUCT
Count sold and remaining COUNTA
Count total matches in two ranges SUMPRODUCTCOUNTIF

Sum

Filter:
Formula Related Functions
3D SUMIF for multiple worksheets INDIRECTSUMIFSUMPRODUCT
Calculate running total SUM
Subtotal by color SUMIF
Subtotal invoices by age SUMIF
Sum bottom n values INDIRECTROWSUMSUMPRODUCTSMALL
Sum by group SUMIF
Sum by month EOMONTH
Sum columns based on adjacent criteria SUMPRODUCT
Sum entire column SUM
Sum every nth column COLUMNMODSUMPRODUCT
Sum if begins with SUMIF
Sum if between
Sum if by year DATE
Sum if cell contains text in another cell SUMIF
Sum if cells are equal to SUMIF
Sum if cells are not equal to SUMIF
Sum if cells contain an asterisk SUMIF
Sum if cells contain both x and y
Sum if cells contain either x or y ISNUMBERSUMPRODUCTFINDSEARCH
Sum if cells contain specific text SUMIF
Sum if date is between DATE
Sum if date is greater than DATESUMIF
Sum if ends with SUMIF
Sum if equal to either x or y SUMIFSUMPRODUCT
Sum if greater than SUMIF
Sum if less than SUMIF
Sum if multiple criteria
Sum if not blank SUMIF
Sum top n values INDIRECTROWSUMSUMPRODUCTLARGE
Sum visible rows in a filtered list SUBTOTAL

Average

Formula Related Functions
Average numbers AVERAGE
Average numbers ignore zero AVERAGEIF
Average top 3 scores AVERAGELARGE
Weighted average SUMSUMPRODUCT

Min and Max

Filter:
Formula Related Functions
Maximum if multiple criteria IFMIN
Maximum value
Maximum value if IF
Minimum if multiple criteria IFMIN
Minimum value MIN
Minimum value if IFMIN
nth largest value LARGE
nth largest value with criteria LARGE
nth smallest value SMALL
nth smallest value with criteria SMALL

Conditional Formatting

Filter:
Formula Related Functions
Highlight blank cells ISBLANKLEN
Highlight cells that begin with IFERRORCOUNTIFFIND
Highlight cells that contain ISNUMBERFINDSEARCH
Highlight cells that end with COUNTIFEXACTLENRIGHT
Highlight cells that equal EXACT
Highlight column differences NOTEXACT
Highlight dates between DATEAND
Highlight dates greater than DATE
Highlight dates in same month and year DATETEXT
Highlight dates in the next N days TODAYAND
Highlight dates that are weekends WEEKDAYOR
Highlight duplicate rows SUMPRODUCTCOUNTIFCOUNTIFS
Highlight duplicate values COUNTIF
Highlight entire rows
Highlight every other row ISEVENISODDROWMOD
Highlight integers only MOD
Highlight rows that contain FINDSEARCH
Highlight rows with dates between DATEAND
Highlight unique values COUNTIF
Highlight values between AND
Highlight values greater than
Highlight values not between X and Y ANDNOT
Highlight values that don’t exist COUNTIF

Lookup

Filter:
Formula Related Functions
Approximate match with multiple criteria IFINDEXMATCH
Basic INDEX MATCH approximate INDEXMATCH
Basic INDEX MATCH exact INDEXMATCH
Calculate grades with VLOOKUP VLOOKUP
Calculate shipping cost with VLOOKUP VLOOKUP
Dynamic lookup table with INDIRECT INDIRECTVLOOKUP
Exact match lookup with INDEX and MATCH INDEXMATCHEXACT
Exact match lookup with SUMPRODUCT SUMPRODUCTEXACT
Extract multiple matches into separate columns IFERRORCOLUMNSINDEXMATCHROWSMALL
Faster VLOOKUP with 2 VLOOKUPS NAVLOOKUP
Find missing values MATCHVLOOKUPCOUNTIF
Get address of lookup result CELLINDEXMATCH
Get employee information with VLOOKUP VLOOKUP
Get first match cell contains ISNUMBERINDEXMATCHSEARCH
Get first non-blank value in a list ISBLANKINDEXMATCH
Get first partial match in a range INDEXMATCH
Get first text value in a list VLOOKUP
Get nth match with INDEX / MATCH INDEXMATCHSMALL
Get nth match with VLOOKUP VLOOKUPCOUNTIF
Group arbitrary text values VLOOKUP
Group numbers with VLOOKUP VLOOKUP
Lookup up cost for product or service VLOOKUP
Map inputs to arbitrary values CHOOSEVLOOKUP
Map text to numbers VLOOKUP
Match first error ISERRORMATCH
Max if criteria match IF
Partial match against numbers with wildcard MATCHTEXT
Partial match with VLOOKUP VLOOKUP
Position of max value in list MATCH
Related info for max value in range INDEXMATCH

If

Filter:
Formula Related Functions
If cell begins with x, y, or z SUMCOUNTIF
If cell contains IF
If cell equals IF
If cell is blank ISBLANKIF
If cell is greater than IF
If cell is not blank ISBLANKIFNOT
If cell is this OR that IFOR
If cell is x or y and z ANDIFOR
If else IF
If NOT this or that IFNOTOR
If this AND that ANDIF
Nested IF function example IF

Round

Filter:
Formula Related Functions
Get decimal part of a number TRUNC
Get integer part of a number INTTRUNC
Round a number ROUND
Round a number down ROUNDDOWN
Round a number down to nearest multiple FLOOR
Round a number to n significant digits ABSINTLOG10ROUND
Round a number to nearest multiple MROUND
Round a number up ROUNDUP
Round a number up to nearest multiple CEILING
Round a number up to next half CEILING
Round a price to end in .99 ROUND
Round time to nearest 15 minutes MROUND
Round to nearest 1000 ROUND
Round to nearest 5 CEILINGFLOORMROUND

Date And Time

Filter:
Formula Related Functions
Add business days to date NETWORKDAYSWORKDAY
Add workdays to date custom weekends NETWORKDAYS
Calculate elapsed work time IFMOD
Calculate years between dates YEARFRACINT
Convert date string to date time DATEVALUETIMEVALUELEFTMID
Convert date to Julian format DATEYEARTEXT
Convert date to text TEXT
Convert hours to time
Convert minutes to time
Convert seconds to time
Convert text to date DATELEFTMIDRIGHT
Convert time to time zone MOD
Count day of week between dates WEEKDAYINDIRECTROWSUMPRODUCT
Custom weekday abbreviation WEEKDAYCHOOSE
Date is same month MONTH
Date is same month and year MONTHYEAR
Date is workday WORKDAY
Days in month DAYEOMONTH
Display the current date TODAY
Display the current date and time NOW
Extract date from a date and time INTTRUNC
Extract time from a date and time MOD
Get age from birthday TODAYYEARFRACINT
Get day from date DAY
Get day name from date WEEKDAYCHOOSE
Get days before a date TODAY
Get days between dates DAYSTODAY
Get days between dates ignoring years DATEDIF
Get first day of month DAY
Get first day of previous month EOMONTH

Date Series

Formula Related Functions
Series of dates by day
Series of dates by month DATEDAYEOMONTHMONTHYEAR
Series of dates by weekends WEEKDAYIF
Series of dates by workdays WEEKDAYWORKDAYIF
Series of dates by year DATEDAYMONTHYEAR

Text

Filter:
Formula Related Functions
Add a line break with a formula CHAR
Add line break based on OS INFOCHAR
Cell contains all of many things ISNUMBERSUMPRODUCTCOUNTASEARCH
Cell contains any numbers COUNTFIND
Cell contains one of many things ISNUMBERSUMPRODUCTSEARCH
Cell contains some words but not others ANDCOUNTSEARCH
Cell contains specific text ISNUMBERFINDSEARCH
Cell contains which things ISNUMBERFINDSEARCH
Cell equals one of many things SUMPRODUCT
Clean and reformat telephone numbers SUBSTITUTE
Compare two strings EXACT
Count line breaks in cell ISBLANKCHARLENSUBSTITUTE
Count specific characters in a cell LENSUBSTITUTEUPPER
Count specific characters in a range SUMPRODUCTLENSUBSTITUTEUPPER
Count specific words in a cell LENSUBSTITUTEUPPER
Count specific words in a range SUMPRODUCTLENSUBSTITUTEUPPER
Count total characters in a cell LEN
Count total characters in a range SUMPRODUCTLEN
Count total words in a cell ISBLANKLENSUBSTITUTETRIM
Count total words in a range SUMPRODUCTLENSUBSTITUTETRIM
Double quotes inside a formula CHAR
Extract nth word from text string LENMIDREPTSUBSTITUTETRIM
Get first word ISERRORFINDLEFT
Get last word REPTRIGHTSUBSTITUTETRIM
Position of 2nd 3rd etc instance of character FINDSUBSTITUTE
Remove last n characters from text LEFTLENVALUE
Remove leading and trailing spaces from text CLEANSUBSTITUTETRIM
Remove text by matching SUBSTITUTE
Remove text by position REPLACE
Remove text by variable position FINDREPLACE

Finalcial

Formula Related Functions
Calculate compound interest FV
Calculate simple interest

Workbook

Formula Related Functions
Get full workbook name and path CELL
Get sheet name only CELLFINDMID
Get workbook name and path without sheet CELLFINDLEFTSUBSTITUTE
Get workbook name only CELLFINDMID
Get workbook path only CELLFINDLEFT

Internet

Formula Related Functions
Get domain from email address FINDLENRIGHT
Get domain name from URL FINDLEFT
Get name from email address FINDLEFT
Get top level domain (TLD) FINDLENRIGHTSUBSTITUTE

Names

Formula Related Functions
Get first name from Last, First FINDLENRIGHT
Get first name from name FINDLEFT
Get last name from Last, First LEFTLEN
Get last name from name FINDLENRIGHTSUBSTITUTE
Get middle name from full name LENMIDTRIM
Join first and last name CONCATENATE
Put names into proper case PROPERTRIM

Percentage

Filter:
Formula Related Functions
Calculate percent variance
Decrease by percentage
Get amount with percentage
Get original price from percentage discount
Get percent change
Get percentage discount
Get percentage of total
Get profit margin percentage
Get total from percentage
Increase by percentage
Percent of goal
Percent of students absent

Range

Filter:
Formula Related Functions
Address of first cell in range CELLADDRESSCOLUMNROW
Address of last cell in range ADDRESSCOLUMNCOLUMNSROWROWS
All cells in range are blank SUMPRODUCT
First column number in range ROWSMIN
First row number in range ROWMIN
Get relative column numbers in range COLUMN
Get relative row numbers in range ROW
Last column number in range COLUMNCOLUMNSMIN
Last row number in range ROWROWSMIN
Range contains a value not in another range ISNAMATCHSUMPRODUCT
Range contains numbers ISNUMBERSUMPRODUCT
Total cells in a range COLUMNSROWS
Total columns in range COLUMNS
Total rows in range ROWS

Miscellaneous

Filter:
Formula Related Functions
Build hyperlink with VLOOKUP HYPERLINKVLOOKUP
Calculate a ratio from two numbers GCD
Change negative numbers to positive ABS
Extract unique items from a list INDEXMATCHCOUNTIF
Flag first duplicate in a list COUNTIF
Flip table rows to columns TRANSPOSE
Get value of last non-empty cell LOOKUP
Increment a calculation with ROW or COLUMN COLUMNROW
List contains duplicates SUMPRODUCTCOUNTIF
Longest winning streak IFFREQUENCY
Mixed reference for multiplication table
Pad a number with zeros REPTTEXT
Random date between two dates WORKDAYRANDBETWEEN
Random number between two numbers RANDBETWEEN
Random text values CHOOSERANDBETWEEN
Randomly assign data to groups CHOOSERANDBETWEEN
Range contains one of many substrings ISNUMBERSUMPRODUCTSEARCH
Range contains one of many values ISNUMBERSUMPRODUCTSEARCH
Range contains specific text COUNTIF
Range contains specific value MATCHCOUNTIF
Reverse a list or range INDEXROWCOUNTA
Transpose table without zeros IFTRANSPOSE
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s