exceljet.net Open in urlscan Pro
2600:3c00::f03c:91ff:fee7:ea6e  Public Scan

Form analysis
4 forms found in the DOM

GET /search

<form class="search-form" action="/search" method="get" id="header-search" accept-charset="UTF-8">
  <input type="text" name="keys" value="" placeholder="Search" size="15" maxlength="128" class="form-text search-text">
  <input type="submit" name="op" value="Search" class="form-submit search-submit">
</form>

GET /search

<form class="search-form" action="/search" method="get" id="header-search" accept-charset="UTF-8">
  <input type="text" name="keys" value="" placeholder="Search" size="15" maxlength="128" class="form-text search-text">
  <input type="submit" name="op" value="Search" class="form-submit search-submit">
</form>

GET /formulas

<form class="views-exposed-form autosubmit-full-form autosubmit-processed jquery-once-1-processed" action="/formulas" method="get" id="views-exposed-form-formulas-page" accept-charset="UTF-8">
  <div>
    <div class="views-exposed-form">
      <div class="views-exposed-widgets clearfix">
        <div id="edit-search-wrapper" class="views-exposed-widget views-widget-filter-combine">
          <div class="views-widget">
            <div class="form-item form-type-textfield form-item-search" role="application">
              <input type="text" id="edit-search" name="search" value="" size="30" maxlength="128" class="form-text form-autocomplete autosubmit-processed" autocomplete="OFF" aria-autocomplete="list"><input type="hidden" id="edit-search-autocomplete"
                value="https://exceljet.net/index.php?q=autocomplete_filter/combine/formulas/page/0" disabled="disabled" class="autocomplete autocomplete-processed">
              <span class="element-invisible" aria-live="assertive" id="edit-search-autocomplete-aria-live"></span>
            </div>
          </div>
        </div>
        <div class="views-exposed-widget views-submit-button">
          <input class="autosubmit-click form-submit" type="submit" id="edit-submit-formulas" name="" value="Filter">
        </div>
        <div class="views-exposed-widget views-reset-button">
          <input type="submit" id="edit-reset" name="op" value="Show all" class="form-submit">
        </div>
      </div>
    </div>
  </div>
</form>

POST https://sendy.exceljet.net/subscribe

<form id="newsletter_signup_footer" class="newsletter-signup-form newsletter-signup-footer-form" action="https://sendy.exceljet.net/subscribe" method="POST" accept-charset="utf-8">
  <label for="email" hidden="">Email</label>
  <input type="email" name="email" placeholder="email address" id="newsletter-signup-footer-email" class="newsletter-signup-footer-email newsletter-signup-email text-input" required="">
  <div style="display:none;">
    <label for="hp">HP</label>
    <input type="text" name="hp" id="newsletter-signup-footer-hp">
  </div>
  <input type="hidden" name="SignupLocation" id="newsletter-signup-footer-SignupLocation" value="footer">
  <input type="hidden" name="list" value="T1JX0z264YewQdKf7gXIig">
  <input type="hidden" name="subform" value="yes">
  <input type="submit" name="submit" id="newsletter-signup-footer-submit" class="newsletter-signup-footer-submit form-submit" value="Subscribe">
</form>

Text Content

Skip to main content
 * Login

Cart
Exceljet
 * Training
 * Videos
 * Functions
 * Formulas
 * Shortcuts
 * Articles


Cart
 * Login

Main menu toggle
 * Training
 * Videos
 * Functions
 * Formulas
 * Shortcuts
 * Articles

 * Login




500 EXCEL FORMULAS

Over 500 working Excel formulas with detailed explanations, videos, and related
links. Includes key functions like VLOOKUP, XLOOKUP, INDEX & MATCH, FILTER,
RANK, ROUND, AVERAGE, COUNTIFS, SUMIFS, UNIQUE, SORT, TEXTSPLIT, and more.




JUMP TO CATEGORY

 * Count
 * Sum
 * Average
 * Dynamic array
 * Min and Max
 * Lookup
 * If
 * Grouping
 * Conditional formatting
 * Data validation
 * Rank
 * Round
 * Date and Time
 * Date series
 * Text
 * Financial
 * Geometry
 * Workbook
 * Internet
 * Names
 * Percentage
 * Random
 * Range
 * Tables
 * Errors
 * Miscellaneous

#Count Formula Related Functions Count between dates by age range COUNTIFS FIND
LEFT RIGHT SUMPRODUCT TEXTBEFORE Count birthdays by year SUMPRODUCT YEAR
COUNTIFS BYROW UNIQUE SORT LET Count cells between dates COUNTIFS SUMPRODUCT
Count cells between two numbers COUNTIFS SUMPRODUCT Count cells equal to COUNTIF
SUMPRODUCT Count cells equal to case sensitive SUMPRODUCT EXACT Count cells
equal to one of many things COUNTIF SUMPRODUCT ISNUMBER MATCH Count cells equal
to this or that COUNTIF SUM SUMPRODUCT Count cells greater than COUNTIF COUNTIFS
Count cells less than COUNTIF COUNTIFS Count cells not between two numbers
COUNTIF SUMPRODUCT Count cells not equal to COUNTIF COUNTIFS Count cells not
equal to many things MATCH ISNA SUMPRODUCT COUNTA COUNTIF Count cells not equal
to x or y COUNTIFS SUMPRODUCT Count cells over n characters SUMPRODUCT LEN N
Count cells that are blank COUNTBLANK COUNTA COUNTIF SUMPRODUCT Count cells that
are not blank COUNTA COUNTBLANK COUNTIFS SUMPRODUCT Count cells that begin with
COUNTIF SUMPRODUCT LEFT LEN EXACT Count cells that contain case sensitive
SUMPRODUCT ISNUMBER FIND Count cells that contain either x or y SUMPRODUCT
ISNUMBER FIND COUNTIF Count cells that contain errors SUMPRODUCT ISERROR ISERR
ERROR.TYPE IFERROR Count cells that contain n characters COUNTIF SUMPRODUCT LEN
REPT Count cells that contain negative numbers COUNTIF SUMPRODUCT Count cells
that contain numbers COUNT SUMPRODUCT ISNUMBER Count cells that contain odd
numbers SUMPRODUCT ISODD MOD ISEVEN Count cells that contain positive numbers
COUNTIF SUMPRODUCT Count cells that contain specific text COUNTIF SUMPRODUCT
SEARCH FIND ISNUMBER Count cells that contain text COUNTIF COUNTIFS SUMPRODUCT
ISTEXT NOT Count cells that do not contain COUNTIF SUMPRODUCT SEARCH FIND NOT
Count cells that do not contain errors ISERROR NOT SUMPRODUCT ISERR COUNTIF
Count cells that do not contain many strings ISNUMBER SEARCH MMULT TRANSPOSE
REDUCE Count cells that end with COUNTIF Count columns that contain specific
values MMULT COLUMN TRANSPOSE BYCOL LAMBDA Count dates by day of week SUMPRODUCT
WEEKDAY MATCH Count dates in given year YEAR SUMPRODUCT Count if row meets
internal criteria SUMPRODUCT Count if row meets multiple criteria SUMPRODUCT
Count if two criteria match COUNTIFS SUMPRODUCT Count items in list COUNTIFS
Count long numbers SUMPRODUCT COUNTIF Count matches between two columns
SUMPRODUCT Count matching values in matching columns SUMPRODUCT ISNUMBER MATCH
SEARCH Count missing values COUNTIF SUM SUMPRODUCT MATCH ISNA Count non-blank
cells by category COUNTIFS Count not equal to multiple criteria SUMPRODUCT ISNA
MATCH COUNTIFS Count numbers by nth digit SUMPRODUCT MID Count numbers by range
COUNTIFS FREQUENCY DROP Count numbers that begin with SUMPRODUCT LEFT LEN
COUNTIF Count numbers with leading zeros SUMPRODUCT COUNTIF SUMIF Count
occurrences in entire workbook COUNTIF SUMPRODUCT VSTACK Count or sum variance
SUMPRODUCT SUM ABS Count or sum whole numbers only MOD SUMPRODUCT Count paired
items in listed combinations COUNTIFS CONCAT Count rows that contain specific
values MMULT COLUMN TRANSPOSE BYROW LAMBDA Count rows with at least n matching
values MMULT COLUMN TRANSPOSE Count rows with multiple OR criteria SUMPRODUCT
Count rows with OR logic SUMPRODUCT Count sold and remaining COUNTA COUNTIF
Count total matches in two ranges SUMPRODUCT COUNTIF MATCH ISNUMBER COUNT Count
unique dates UNIQUE COUNT COUNTIF SUMPRODUCT Count unique numeric values in a
range FREQUENCY SUM COUNTIF Count unique numeric values with criteria FREQUENCY
SUM Count unique text values in a range FREQUENCY MATCH ROW SUMPRODUCT Count
unique text values with criteria FREQUENCY MATCH ROW SUM Count unique values in
a range with COUNTIF SUMPRODUCT COUNTIF Count visible rows in a filtered list
SUBTOTAL Count visible rows with criteria SUBTOTAL OFFSET SUMPRODUCT INDEX
COUNTIF with non-contiguous range COUNTIF INDIRECT VSTACK COUNTIFS with multiple
criteria and OR logic COUNTIFS Histogram with FREQUENCY FREQUENCY Running count
of occurrence in list COUNTIF IF Summary count by month with COUNTIFS COUNTIFS
EDATE Summary count with COUNTIF COUNTIF Summary count with percentage breakdown
COUNTIF COUNTA SUMPRODUCT count multiple OR criteria SUMPRODUCT Two-way summary
count COUNTIFS LET UNIQUE HSTACK VSTACK

#Sum Formula Related Functions Calculate running total SUM SCAN Count cells that
contain formulas SUMPRODUCT ISFORMULA NOT Subtotal by color SUMIF COUNTIF
Subtotal by invoice number SUMIF COUNTIF Subtotal invoices by age SUMIF COUNTIFS
Sum across multiple worksheets SUM Sum across multiple worksheets with criteria
SUMPRODUCT SUMIF INDIRECT Sum and ignore errors SUM SUMIF AGGREGATE IFERROR Sum
bottom n values SUMPRODUCT SMALL SUM ROW INDIRECT Sum bottom n values with
criteria SMALL SUM FILTER SEQUENCE Sum by group SUMIF SUMIFS IF Sum by month
SUMIFS EDATE SUMPRODUCT TEXT Sum by month ignore year SUMPRODUCT MONTH Sum by
month in columns SUMIFS EOMONTH Sum by quarter SUMIFS ROUNDUP MONTH SUMPRODUCT
LET LAMBDA BYROW Sum by week SUMIFS LET LAMBDA UNIQUE WEEKDAY BYROW HSTACK
VSTACK Sum by week number SUMIFS WEEKNUM LAMBDA LET BYROW UNIQUE VSTACK HSTACK
Sum by weekday SUMPRODUCT TEXT WEEKDAY Sum by year SUMPRODUCT YEAR SUMIFS DATE
LET BYROW UNIQUE VSTACK HSTACK Sum columns based on adjacent criteria SUMPRODUCT
Sum entire column SUM Sum entire row SUM Sum every n rows SUM OFFSET Sum every
nth column FILTER SEQUENCE SUMPRODUCT COLUMN MOD Sum every nth row FILTER
SEQUENCE MOD SUM SUMPRODUCT ROW Sum first n matching values FILTER TAKE SUM Sum
first n rows TAKE SUM OFFSET Sum formulas only SUMPRODUCT ISFORMULA NOT Sum if
begins with SUMIF SUMIFS Sum if between SUMIFS Sum if case-sensitive SUMPRODUCT
EXACT Sum if cell contains text in another cell SUMIF SUMIFS SUMPRODUCT FIND
ISNUMBER Sum if cells are equal to SUMIF SUMIFS Sum if cells are not equal to
SUMIF SUMIFS Sum if cells contain an asterisk SUMIF SUMIFS FIND FILTER Sum if
cells contain both x and y SUMIFS Sum if cells contain either x or y SUMIFS
SUMPRODUCT ISNUMBER FIND SEARCH Sum if cells contain specific text SUMIF SUMIFS
SUMPRODUCT FIND ISNUMBER Sum if date is between SUMIFS DATE Sum if date is
greater than SUMIFS SUMIF DATE Sum if ends with SUMIF SUMIFS Sum if greater than
SUMIF SUMIFS Sum if less than SUMIF SUMIFS Sum if multiple columns SUMPRODUCT
SUMIFS Sum if multiple criteria SUMIFS Sum if not blank SUMIFS SUMPRODUCT FILTER
SUM Sum if one of many things SUMIFS SUMPRODUCT ISNUMBER MATCH FILTER Sum if x
or y SUMIF SUMPRODUCT ISNUMBER MATCH Sum last 30 days SUMIFS TODAY SUMPRODUCT
FILTER Sum last n columns TAKE OFFSET COLUMNS INDEX SUM Sum last n rows TAKE SUM
OFFSET INDEX Sum matching columns SUMPRODUCT LEFT FILTER SUM Sum matching
columns and rows SUMPRODUCT FILTER SUM Sum top n values SUM LARGE SEQUENCE
SUMPRODUCT ROW INDIRECT Sum top n values with criteria SUM LARGE FILTER SEQUENCE
IF Sum visible rows in a filtered list SUBTOTAL AGGREGATE SUMIFS with horizontal
range SUMIFS SUMIFS with multiple criteria and OR logic SUMIFS SUM SUMPRODUCT
with IF SUMPRODUCT IF

#Average Formula Related Functions Average and ignore errors AVERAGEIF AGGREGATE
AVERAGE IFERROR FILTER Average by group UNIQUE AVERAGEIFS Average by month
AVERAGEIFS EDATE FILTER AVERAGE Average call time per month AVERAGEIFS EDATE
FILTER AVERAGE TEXT Average hourly pay per day SUMPRODUCT SUMIFS Average if not
blank AVERAGEIFS FILTER AVERAGE Average if with filter AVERAGE FILTER SUMPRODUCT
IF AVERAGEIFS Average last 3 numeric values AVERAGE FILTER TAKE LOOKUP LARGE ROW
Average last n columns TAKE AVERAGE OFFSET COUNT INDEX COLUMNS Average last n
rows TAKE AVERAGE OFFSET COUNT Average numbers ignore zero AVERAGEIF AVERAGEIFS
AVERAGE FILTER Average salary by department UNIQUE AVERAGEIFS Average top 3
scores LARGE AVERAGE SEQUENCE Average with multiple criteria AVERAGEIFS Basic
average example AVERAGE AVERAGEIFS AGGREGATE Moving average formula OFFSET
AVERAGE MIN Must pass 4 out of 6 subjects IF COUNTIF AND Weighted average
SUMPRODUCT SUM AVERAGE TRANSPOSE

#Dynamic array Formula Related Functions Basic filter example FILTER Biggest
gainers and losers FILTER LARGE SMALL SORT Combine ranges INDEX SEQUENCE ROWS
COLUMNS LET Count unique dates ignore time INT UNIQUE COUNT LET LAMBDA SCAN
Count unique values UNIQUE COUNTA Count unique values with criteria UNIQUE
FILTER LEN SUM COUNTA Detailed LET function example LET VLOOKUP IF XLOOKUP
Distinct values UNIQUE Dynamic summary count UNIQUE COUNTIF LET SORT SCAN LAMBDA
Dynamic two-way average UNIQUE TRANSPOSE AVERAGEIFS Dynamic two-way count UNIQUE
TRANSPOSE COUNTIFS LET HSTACK VSTACK Dynamic two-way sum UNIQUE TRANSPOSE
AVERAGEIFS Extract common values from two lists FILTER COUNTIF UNIQUE SORT
Filter and sort without errors FILTER SORT Filter and transpose horizontal to
vertical FILTER TRANSPOSE Filter by column, sort by row FILTER SORT Filter by
date FILTER MONTH FILTER case-sensitive FILTER SEARCH ISNUMBER Filter contains
one of many FILTER ISNUMBER MATCH Filter data between dates FILTER Filter every
nth row FILTER SEQUENCE MOD ROW Filter exclude blank values FILTER Filter
horizontal data FILTER TRANSPOSE FILTER last n valid entries FILTER SEQUENCE
INDEX FILTER on first or last n values FILTER SEQUENCE INDEX FILTER on top n
values FILTER LARGE SORT FILTER on top n values with criteria FILTER LARGE SORT
Filter text contains FILTER SEARCH ISNUMBER Filter this or that FILTER Filter to
extract matching values FILTER COUNTIF COUNTIFS FILTER to remove columns FILTER
MATCH ISNUMBER FILTER to show duplicate values UNIQUE FILTER COUNTIF Filter
values within tolerance FILTER ABS FILTER with complex multiple criteria FILTER
LEFT MONTH NOT Filter with multiple criteria FILTER FILTER with multiple OR
criteria FILTER ISNUMBER MATCH FILTER with partial match FILTER SEARCH ISNUMBER
Generate random text strings SORTBY RANDARRAY COUNTA SEQUENCE CHAR Get column
totals BYCOL LAMBDA MMULT ROW TRANSPOSE SEQUENCE Get row totals BYROW LAMBDA
MMULT COLUMN TRANSPOSE SEQUENCE LAMBDA append range LAMBDA LET INDEX SEQUENCE
LAMBDA append range horizontal LAMBDA LET INDEX SEQUENCE LAMBDA contains one of
many LAMBDA SUMPRODUCT SUM ISNUMBER SEARCH LAMBDA contains which things LAMBDA
LET ISNUMBER SEARCH SORTBY LAMBDA count words LAMBDA SUBSTITUTE TRIM LEN LAMBDA
replace characters recursive LAMBDA LEFT MID LEN LAMBDA split text to array
LAMBDA FILTERXML TRANSPOSE LAMBDA strip characters LAMBDA LET MID MATCH ISNUMBER
LAMBDA strip trailing characters recursive LAMBDA MID LEN List upcoming
birthdays SORTBY LET TEXT INDEX SEQUENCE XMATCH MAP with AND and OR logic MAP
AND OR Minimum value if unique UNIQUE MIN COUNTIF IF Random list of names SORTBY
RANDARRAY COUNTA SEQUENCE Random numbers without duplicates SEQUENCE SORTBY
RANDARRAY INDEX Random sort SORTBY RANDARRAY ROWS Sequence of days SEQUENCE
WORKDAY.INTL Sequence of months SEQUENCE EDATE EOMONTH Sequence of times
SEQUENCE TIME Sequence of years SEQUENCE YEAR MONTH DAY Sort birthdays by month
and day SORTBY TEXT Sort by custom list SORTBY MATCH Sort by one column SORT
Sort by two columns SORTBY Sort text by length SORTBY LEN Sort values by columns
SORT SORTBY Sum numbers with text TEXTBEFORE TEXTAFTER UNIQUE LET LAMBDA BYROW
Unique rows UNIQUE SORT Unique values UNIQUE Unique values by count UNIQUE
FILTER COUNTIF Unique values case-sensitive REDUCE LAMBDA EXACT VSTACK Unique
values from multiple ranges UNIQUE VSTACK Unique values ignore blanks UNIQUE
FILTER Unique values with criteria UNIQUE FILTER Unique values with multiple
criteria UNIQUE FILTER UNIQUE with non-adjacent columns FILTER UNIQUE SORT
XLOOKUP match any column MMULT SEQUENCE COLUMNS

#Min and Max Formula Related Functions Cap percentage between 0 and 100 MIN MAX
MEDIAN IF First in last out times MINIFS MAXIFS MIN MAX IF FILTER Large with
criteria LARGE FILTER IF Larger of two values MAX IF Max by month MAXIFS MAX
TEXT Max of every nth column MAX FILTER SEQUENCE COLUMNS CHOOSECOLS COLUMN MOD
Max value ignore all errors AGGREGATE MAXIFS Max value on given weekday FILTER
TEXT MAX IF AGGREGATE Max value with variable column INDEX MATCH MAX FILTER
COUNTIF Maximum change MAX INDEX MATCH XLOOKUP LET HSTACK SORT VSTACK Maximum if
multiple criteria MAX IF MAXIFS FILTER Maximum value MAX MAXIFS LARGE Maximum
value if MAXIFS MAX IF FILTER BYROW VSTACK HSTACK UNIQUE Minimum if multiple
criteria MIN IF MINIFS Minimum value MIN MINIFS SMALL Minimum value if MINIFS
MIN FILTER IF BYROW VSTACK HSTACK UNIQUE Name of nth largest value LARGE INDEX
MATCH XLOOKUP Name of nth largest value with criteria LARGE INDEX MATCH IF
XLOOKUP nth largest value LARGE nth largest value with criteria LARGE FILTER IF
nth largest without duplicates MAX UNIQUE IF nth smallest value SMALL nth
smallest value with criteria SMALL Smaller of two values MIN IF

#Lookup Formula Related Functions Case sensitive lookup EXACT INDEX MATCH
XLOOKUP Find closest match INDEX MATCH ABS MIN XLOOKUP Find longest string in
column INDEX MATCH MAX LEN Find longest string with criteria INDEX MATCH MAX LEN
Find lowest n values SMALL INDEX MATCH Find missing values COUNTIF VLOOKUP MATCH
First match in range with wildcard INDEX MATCH Get address of lookup result
INDEX MATCH CELL Get cell content at given row and column ADDRESS INDIRECT INDEX
Get employee information with VLOOKUP VLOOKUP Get first match cell contains
INDEX MATCH ISNUMBER SEARCH AGGREGATE Get first non-blank value in a list INDEX
MATCH ISBLANK Get first text value in a list VLOOKUP Get first text value with
HLOOKUP HLOOKUP Get information corresponding to max value MAX MATCH INDEX Get
last match INDEX MATCH MAX ROW Get last match cell contains LOOKUP SEARCH Get
location of value in 2D array SUMPRODUCT ROW COLUMN Get nth match SMALL IF MIN
ROW Get nth match with INDEX / MATCH INDEX SMALL Get nth match with VLOOKUP
VLOOKUP COUNTIF INDEX and MATCH advanced example INDEX MATCH LEFT INDEX and
MATCH all matches INDEX MATCH AND SUM INDEX and MATCH all partial matches INDEX
AGGREGATE ISNUMBER SEARCH INDEX and MATCH approximate match INDEX MATCH INDEX
and MATCH approximate match with multiple criteria INDEX MATCH IF INDEX and
MATCH case-sensitive INDEX MATCH EXACT XLOOKUP INDEX and MATCH descending order
INDEX MATCH INDEX and MATCH exact match INDEX MATCH TRANSPOSE Index and match on
multiple columns MMULT COLUMN TRANSPOSE INDEX INDEX and MATCH with multiple
criteria INDEX MATCH INDEX and MATCH with variable columns INDEX XMATCH XLOOKUP
CHOOSECOLS INDEX with variable array INDEX MATCH CHOOSE Join tables with INDEX
and MATCH INDEX MATCH Left lookup with INDEX and MATCH INDEX MATCH Left lookup
with VLOOKUP VLOOKUP CHOOSE Look up entire column XLOOKUP INDEX MATCH Look up
entire row XLOOKUP INDEX MATCH Lookup and sum column INDEX MATCH SUM Lookup
first negative value XLOOKUP FILTER TAKE INDEX MATCH Lookup last file version
LOOKUP ISNUMBER FIND Lookup latest price LOOKUP Lookup lowest Monday tide INDEX
MATCH IF MIN XLOOKUP Lookup lowest value INDEX MATCH MIN Lookup number plus or
minus N XLOOKUP ABS INDEX MATCH Lookup up cost for product or service VLOOKUP
Lookup value between two numbers LOOKUP Lookup with variable sheet name VLOOKUP
INDIRECT Match first does not begin with INDEX MATCH LEFT Match first error
MATCH ISERROR Match first occurrence does not contain INDEX MATCH ISNUMBER
SEARCH Match long text MATCH LEFT MID EXACT Match next highest value INDEX MATCH
Max if criteria match MAX IF MAXIFS Merge tables with VLOOKUP VLOOKUP
Multi-criteria lookup and transpose INDEX MATCH Multiple chained VLOOKUPs
VLOOKUP IFERROR Multiple matches in comma separated list TEXTJOIN Multiple
matches into separate columns FILTER UNIQUE SORT SEQUENCE INDEX SMALL IFERROR
COLUMN COLUMNS Multiple matches into separate rows FILTER UNIQUE SORT TRANSPOSE
INDEX SMALL IFERROR ROW ROWS Nearest location with XMATCH INDEX XMATCH Next
largest match with the MATCH function INDEX MATCH Partial match with numbers and
wildcard MATCH TEXT Partial match with VLOOKUP VLOOKUP Position of first partial
match MATCH INDEX Position of max value in list MAX MATCH Rank and score with
INDEX and MATCH RANK INDEX MATCH Reverse VLOOKUP example VLOOKUP CHOOSE INDEX
MATCH XLOOKUP Self-contained VLOOKUP VLOOKUP Sum lookup values using SUMIF SUMIF
SUMPRODUCT Sum range with INDEX INDEX SUM SUMIFS multiple criteria lookup in
table SUMIFS SUMPRODUCT case-sensitive lookup SUMPRODUCT EXACT Two-way
approximate match multiple criteria INDEX MATCH IF Two-way lookup with INDEX and
MATCH INDEX MATCH VLOOKUP by date VLOOKUP VLOOKUP calculate grades VLOOKUP
VLOOKUP calculate shipping cost VLOOKUP MAX VLOOKUP case-sensitive VLOOKUP EXACT
CHOOSE VLOOKUP faster VLOOKUP VLOOKUP NA VLOOKUP from another sheet VLOOKUP
VLOOKUP from another workbook VLOOKUP VLOOKUP if blank return blank VLOOKUP IF
VLOOKUP override output VLOOKUP VLOOKUP tax rate calculation VLOOKUP VLOOKUP
two-way lookup VLOOKUP MATCH VLOOKUP variable commission split VLOOKUP VLOOKUP
with 2 lookup tables VLOOKUP VLOOKUP with multiple criteria VLOOKUP VLOOKUP with
multiple criteria advanced VLOOKUP CHOOSE VLOOKUP with numbers and text VLOOKUP
ISTEXT IFERROR VLOOKUP with two client rates VLOOKUP VLOOKUP with variable table
array INDIRECT VLOOKUP IF VLOOKUP without #N/A error VLOOKUP IFERROR IFNA ISNA
XLOOKUP approximate match with multiple criteria XLOOKUP IF FILTER XLOOKUP basic
approximate match XLOOKUP XLOOKUP basic exact match XLOOKUP XLOOKUP
case-sensitive XLOOKUP EXACT XLOOKUP date of max value XLOOKUP INDEX MATCH
XLOOKUP horizontal lookup XLOOKUP XLOOKUP last match XLOOKUP XLOOKUP latest by
date XLOOKUP MAX XLOOKUP lookup left XLOOKUP XLOOKUP lookup row or column
XLOOKUP XLOOKUP match text contains XLOOKUP SEARCH FIND ISNUMBER XLOOKUP
rearrange columns XLOOKUP XLOOKUP return blank if blank XLOOKUP IF LET XLOOKUP
two-way exact match XLOOKUP INDEX MATCH XLOOKUP wildcard match example XLOOKUP
TRANSPOSE XLOOKUP with Boolean OR logic XLOOKUP INDEX MATCH XLOOKUP with complex
multiple criteria FILTER LEFT MONTH NOT XLOOKUP with logical criteria XLOOKUP
XLOOKUP with multiple criteria XLOOKUP INDEX MATCH XLOOKUP without #N/A error
XLOOKUP IFERROR IFNA XMATCH reverse search XMATCH INDEX Zodiac sign lookup INDEX
MATCH TEXT DATEVALUE IFNA YEAR

#If Formula Related Functions If cell begins with x, y, or z SUM COUNTIF If cell
contains IF if cell contains this or that COUNTIF SUM If cell equals IF If cell
is blank IF ISBLANK If cell is greater than IF If cell is not blank IF ISBLANK
NOT If cell is this OR that IF OR If cell is x or y and z IF OR AND If date is
between two dates IF AND If else IF If not blank multiple cells IF ISBLANK NOT
If NOT this or that IF OR NOT If this AND that IF AND If this AND that OR that
IF AND OR IF with boolean logic IF IF with other calculations IF SUM AND AVERAGE
IF with wildcards IF Invoice status with nested if IF TODAY Nested IF function
example IF Nested IF with multiple AND IF AND OR Only calculate if not blank IF
ISBLANK COUNTBLANK AND OR COUNT Return blank if IF ISBLANK COUNTBLANK Show
checkmark if complete IF ISBLANK NOT Tax rate calculation with fixed base IF
Two-tier sales tax calculation IF Win loss points calculation VLOOKUP IF

#Grouping Formula Related Functions Categorize text with keywords INDEX MATCH
ISNUMBER SEARCH XLOOKUP Group arbitrary text values VLOOKUP Group numbers at
uneven intervals LOOKUP Group numbers with VLOOKUP VLOOKUP Group times into 3
hour buckets FLOOR Group times into unequal buckets VLOOKUP If cell contains one
of many things INDEX MATCH ISNUMBER SEARCH Map inputs to arbitrary values
VLOOKUP CHOOSE Map text to numbers VLOOKUP Running count group by n size COUNTA
CEILING

#Conditional formatting Formula Related Functions Highlight 3 smallest values
with criteria SMALL AND Conditional formatting based on another cell Conditional
formatting based on another column Conditional formatting column is blank OR AND
NOT Conditional formatting date past due TODAY Conditional formatting dates
overlap SUMPRODUCT Conditional formatting highlight target percentage
Conditional formatting last n rows Find duplicate values in two columns COUNTIF
AND Gantt chart AND Gantt chart by week AND Gantt chart time schedule AND OR
Gantt chart with weekends WEEKDAY Highlight approximate match lookup conditional
formatting LOOKUP OR AND Highlight blank cells ISBLANK LEN Highlight bottom
values SMALL Highlight cells that begin with COUNTIF FIND IFERROR Highlight
cells that contain SEARCH FIND ISNUMBER Highlight cells that contain one of many
SEARCH FIND ISNUMBER SUMPRODUCT Highlight cells that end with COUNTIF RIGHT LEN
EXACT Highlight cells that equal EXACT Highlight column differences EXACT NOT
Highlight data by quartile QUARTILE Highlight dates between DATE AND Highlight
dates greater than DATE Highlight dates in same month and year DATE TEXT
Highlight dates in the next N days TODAY AND Highlight dates that are weekends
WEEKDAY OR Highlight duplicate columns SUMPRODUCT COUNTIF Highlight duplicate
rows COUNTIFS COUNTIF SUMPRODUCT Highlight duplicate values COUNTIF Highlight
entire rows Highlight every other row ISEVEN ISODD ROW MOD Highlight integers
only MOD Highlight many matching values COUNTIF Highlight missing values COUNTIF
Highlight multiples of specific value MOD Highlight numbers that include symbols
ISNUMBER MID Highlight row and column intersection exact match OR AND Highlight
rows that contain SEARCH FIND Highlight rows with blank cells COUNTBLANK
Highlight rows with dates between DATE AND Highlight specific day of week TEXT
Highlight top values LARGE Highlight unique values COUNTIF Highlight unprotected
cells CELL Highlight values between AND Highlight values greater than Highlight
values not between X and Y AND NOT Shade alternating groups of n rows ROW
CEILING ISEVEN ISODD

#Data validation Formula Related Functions Data validation allow numbers only
ISNUMBER Data validation allow text only ISTEXT Data validation allow uppercase
only UPPER EXACT AND Data validation allow weekday only YEAR TODAY Data
validation date in next 30 days AND TODAY Data validation date in specific year
YEAR TODAY Data validation don't exceed total SUM Data validation exists in list
COUNTIF Data validation must begin with EXACT LEFT COUNTIF Data validation must
contain specific text FIND ISNUMBER Data validation must not contain ISNUMBER
SEARCH SUMPRODUCT Data validation must not exist in list COUNTIF Data validation
no punctuation FIND COUNT Data validation number multiple 100 MOD Data
validation only dates between AND DATE Data validation require unique number AND
ISNUMBER COUNTIF Data validation specific characters only MATCH COUNT LEN MID
INDIRECT Data validation unique values only COUNTIF Data validation whole
percentage only TRUNC AND Data validation with conditional list IF

#Rank Formula Related Functions Break ties with helper column and COUNTIF SMALL
INDEX MATCH Rank function example RANK Rank if formula COUNTIFS RANK Rank race
results RANK Rank values by month LARGE INDEX MATCH TEXT IF Rank with ordinal
suffix CHOOSE ABS MOD Rank without ties RANK COUNTIF

#Round Formula Related Functions Get decimal part of a number TRUNC Get integer
part of a number TRUNC INT Get number at place value MOD Round a number ROUND
Round a number down ROUNDDOWN Round a number down to nearest multiple FLOOR
Round a number to n significant digits ROUND LOG10 INT ABS 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 MROUND Round by bundle size CEILING Round price to end in .45 or .95
CEILING Round time to nearest 15 minutes MROUND CEILING FLOOR Round to nearest
1000 ROUND Round to nearest 5 MROUND CEILING FLOOR

#Date and Time Formula Related Functions Add business days to date WORKDAY
NETWORKDAYS Add days exclude certain days of week WORKDAY.INTL Add days to date
DAY Add decimal hours to time TIME MOD Add decimal minutes to time TIME MOD Add
months to date EDATE Add workdays no weekends WORKDAY.INTL NETWORKDAYS Add
workdays to date custom weekends WORKDAY.INTL NETWORKDAYS Add years to date DATE
YEAR MONTH DAY Assign points based on late time IF VALUE Basic timesheet formula
with breaks MOD Calculate date overlap in days MAX MIN Calculate days open TODAY
IF ISBLANK Calculate days remaining TODAY Calculate expiration date EOMONTH
EDATE Calculate number of hours between two times IF MOD Calculate retirement
date EDATE YEARFRAC SIGN Calculate years between dates YEARFRAC INT Convert date
string to date time DATEVALUE LEFT MID TIMEVALUE Convert date to Julian format
DATE YEAR TEXT Convert date to month and year TEXT Convert date to text TEXT
Convert decimal hours to Excel time Convert decimal minutes to Excel time
Convert decimal seconds to Excel time Convert Excel time to decimal hours
Convert Excel time to decimal minutes Convert Excel time to decimal seconds
Convert Excel time to Unix time DATE Convert text date dd/mm/yy to mm/dd/yy DATE
LEFT MID RIGHT TRIM Convert text timestamp into time TIME MID Convert text to
date DATE LEFT MID RIGHT Convert time to money Convert time to time zone MOD
Convert Unix time stamp to Excel date DATE Count birthdays by month SUMPRODUCT
MONTH Count calls at specific times COUNTIFS Count dates in current month
COUNTIFS EOMONTH SUMPRODUCT N Count day of week between dates WEEKDAY ROW
INDIRECT SUMPRODUCT SEQUENCE Count holidays between two dates SUMPRODUCT Count
times in a specific range COUNTIFS TIME Create date range from two dates TEXT IF
Custom weekday abbreviation WEEKDAY CHOOSE Date is same month MONTH Date is same
month and year MONTH YEAR Date is workday WORKDAY WORKDAY.INTL Days in month DAY
EOMONTH Days until expiration date Display the current date TODAY Display the
current date and time NOW Due date by category VLOOKUP WORKDAY XLOOKUP Dynamic
calendar formula WEEKDAY CHOOSE TODAY SEQUENCE DATE Dynamic calendar grid
WEEKDAY CHOOSE TODAY Dynamic date list TODAY ROWS Extract date from a date and
time INT TRUNC Extract time from a date and time MOD Filter on dates expiring
soon FILTER SORT AND TODAY Future time intervals Get age from birthday DATEDIF
TODAY DATE YEARFRAC INT Get date from day number DATE RIGHT LEFT Get day from
date DAY Get day name from date TEXT WEEKDAY CHOOSE Get days before a date TODAY
Get days between dates TODAY DAYS Get days between dates ignoring years DATEDIF
Get days, hours, and minutes between dates INT TEXT Get days, months, and years
between dates DATEDIF LET Get earliest and latest project dates MINIFS MAXIFS
MIN MAX IF Get first day of month DAY EOMONTH Get first day of previous month
EOMONTH Get fiscal quarter from date MONTH CHOOSE Get fiscal year from date
MONTH YEAR Get last day of month EOMONTH DATE Get last weekday in month EOMONTH
WEEKDAY Get last working day in month WEEKDAY EOMONTH Get Monday of the week
WEEKDAY Get month from date MONTH DATE Get month name from date MONTH CHOOSE
TEXT Get months between dates DATEDIF Get most recent day of week MOD Get next
day of week WEEKDAY Get next scheduled event MIN IF MINIFS INDEX MATCH Get nth
day of week in month DAY WEEKDAY Get nth day of year DATE YEAR Get percent of
year complete YEARFRAC YEAR DATE Get previous Sunday WEEKDAY CHOOSE Get project
end date WORKDAY WORKDAY.INTL Get project midpoint WORKDAY WORKDAY.INTL Get
project start date WORKDAY WORKDAY.INTL Get quarter from date MONTH ROUNDUP Get
same date next month EDATE Get same date next year EDATE Get week number from
date WEEKNUM ISOWEEKNUM Get work hours between dates NETWORKDAYS
NETWORKDAYS.INTL Get work hours between dates and times NETWORKDAYS
NETWORKDAYS.INTL Get work hours between dates custom schedule MID ROW INDIRECT
WEEKDAY SUMPRODUCT Get workdays between dates NETWORKDAYS NETWORKDAYS.INTL Get
year from date YEAR Happy birthday message TEXT TODAY IF AND MONTH DAY If
Monday, roll back to Friday WEEKDAY IF Join date and text TEXT Last n days AND
TODAY Last n months AND TODAY EOMONTH Last n weeks AND TODAY WEEKDAY Last
updated date stamp TEXT TODAY List holidays between two dates TEXTJOIN IF Month
number from name MONTH DATEVALUE Next anniversary date EDATE DATEDIF Next
biweekly payday from date CEILING Next business day 6 months in future WORKDAY
WORKDAY.INTL Next working day WORKDAY WORKDAY.INTL Pad week numbers with zeros
TEXT WEEKNUM Parse time string to time TIME RIGHT LEFT MID Remove time from
timestamp INT TRUNC Sum by fiscal year MONTH YEAR SUMPRODUCT SUMIF Sum race time
splits SUM Sum time SUM SUMIF Sum time by week and project SUMIFS Sum time over
30 minutes SUMPRODUCT TIME SUMIFS COUNTIFS Sum time with SUMIFS SUMIFS Time
difference in hours as decimal value MOD Time duration with days TIME Time since
start in day ranges IFS IF Timesheet overtime calculation formula MIN Total
hours that fall between two times MIN MAX Workdays per month NETWORKDAYS
NETWORKDAYS.INTL EOMONTH Working days in year NETWORKDAYS NETWORKDAYS.INTL DATE
TODAY Working days left in month NETWORKDAYS NETWORKDAYS.INTL TODAY Year is a
leap year DATE YEAR MONTH

#Date series Formula Related Functions Series of dates by custom days
WORKDAY.INTL Series of dates by day Series of dates by month DAY MONTH YEAR DATE
EOMONTH Series of dates by weekends WORKDAY.INTL WEEKDAY Series of dates by
workdays WORKDAY NETWORKDAYS.INTL IF WEEKDAY Series of dates by year DAY MONTH
YEAR DATE

#Text Formula Related Functions 10 most common text values UNIQUE COUNTIF HSTACK
SORT TAKE Abbreviate names or words TEXTJOIN MID ROW CODE LEN INDIRECT Add a
line break with a formula CHAR Add line break based on OS INFO CHAR Capitalize
first letter LEFT MID LEN Cell begins with LEFT EXACT COUNTIF Cell contains all
of many things SUMPRODUCT ISNUMBER SEARCH COUNTA Cell contains number FIND COUNT
SEQUENCE MID LEN Cell contains one of many things SUMPRODUCT ISNUMBER SEARCH
Cell contains one of many with exclusions SUMPRODUCT ISNUMBER SEARCH Cell
contains some words but not others COUNT SEARCH AND Cell contains specific text
SEARCH ISNUMBER FIND Cell contains which things SEARCH ISNUMBER FILTER TEXTJOIN
Cell ends with LEFT EXACT COUNTIF Cell equals one of many things SUMPRODUCT
Clean and reformat telephone numbers SUBSTITUTE Compare two strings EXACT
Conditional message with REPT function REPT IF AND Convert numbers to text TEXT
Convert string to array MID ROW LEN INDIRECT Convert text to numbers VALUE LEFT
RIGHT Count keywords cell contains SUMPRODUCT MATCH ISNUMBER SEARCH Count line
breaks in cell LEN SUBSTITUTE CHAR ISBLANK Count numbers in text string COUNT
SEQUENCE MID LEN INDIRECT ROW Count specific characters in a cell LEN SUBSTITUTE
UPPER Count specific characters in a range LEN SUBSTITUTE UPPER SUMPRODUCT Count
specific words in a cell LEN SUBSTITUTE UPPER Count specific words in a range
LEN SUBSTITUTE UPPER SUMPRODUCT Count total characters in a cell LEN Count total
characters in a range LEN SUMPRODUCT Count total words in a cell LEN SUBSTITUTE
TRIM Count total words in a range SUMPRODUCT LEN SUBSTITUTE TRIM Double quotes
inside a formula CHAR Extract last two words from cell MID LEN SUBSTITUTE FIND
Extract multiple lines from a cell TRIM MID SUBSTITUTE REPT LEN Extract nth word
from text string TEXTSPLIT INDEX TRIM MID SUBSTITUTE REPT LEN Extract substring
MID Extract text between parentheses MID SEARCH Extract word containing specific
text MID MAX FIND SUBSTITUTE REPT TRIM Extract word that begins with specific
character MID LEN FIND SUBSTITUTE REPT TRIM Find and replace multiple values
SUBSTITUTE INDEX Find nth occurrence of character SUBSTITUTE FIND CHAR Get first
word LEFT FIND ISERROR Get last line in cell TRIM SUBSTITUTE RIGHT REPT CHAR Get
last word TRIM SUBSTITUTE RIGHT REPT Join cells with comma TRIM SUBSTITUTE
TEXTJOIN MAC address format TEXTJOIN SEQUENCE MID Make words plural IF IFNA
VLOOKUP Most frequent text with criteria INDEX MATCH MODE IF Most frequently
occurring text INDEX MATCH MODE Normalize text SUBSTITUTE TRIM LOWER Pad text to
equal length REPT LEN Position of 2nd 3rd etc instance of character SUBSTITUTE
FIND Remove characters from right LEFT LEN VALUE Remove file extension from
filename LEFT FIND Remove first character REPLACE RIGHT LEN LEFT Remove last
word MID FIND SUBSTITUTE LEN Remove leading and trailing spaces from text CLEAN
TRIM SUBSTITUTE Remove line breaks SUBSTITUTE CLEAN Remove text by matching
SUBSTITUTE Remove text by position REPLACE Remove text by variable position
REPLACE FIND Remove unwanted characters SUBSTITUTE CHAR CODE LEFT CLEAN Replace
one character with another SUBSTITUTE Replace one delimiter with another
TEXTSPLIT TEXTJOIN SUBSTITUTE CHAR Reverse text string TEXTJOIN MID INDIRECT
SEQUENCE Sort comma separated values TEXTSPLIT SORT TEXTJOIN TRIM FILTERXML
Split dimensions into three parts TEXTSPLIT LEFT RIGHT MID LEN SUBSTITUTE Split
dimensions into two parts SUBSTITUTE LEFT RIGHT FIND Split numbers from units of
measure MAX LEFT RIGHT ISNUMBER VALUE MID Split text and numbers FIND MIN RIGHT
LEFT Split text string at specific character TEXTBEFORE TEXTAFTER LEFT RIGHT LEN
FIND Split text string to character array MID SEQUENCE LEN Split text with
delimiter TRIM MID SUBSTITUTE REPT LEN Strip html from text or numbers MID LEN
Strip non-numeric characters TEXTJOIN MID ROW INDIRECT Strip numeric characters
from cell TEXTJOIN MID ROW INDIRECT SEQUENCE LET Text split to array TEXTSPLIT
FILTERXML SUBSTITUTE TRANSPOSE Translate letters to numbers TEXTJOIN VLOOKUP MID
ROW T Trim text to n words LEFT SUBSTITUTE FIND

#Financial Formula Related Functions Annual compound interest schedule FV
Annuity solve for interest rate RATE Bond valuation example PV FV PRICE CAGR
formula examples GEOMEAN RRI Calculate compound interest FV Calculate cumulative
loan interest CUMIPMT Calculate cumulative loan principal payments CUMPRINC
Calculate interest for given period PPMT Calculate interest rate for loan RATE
Calculate loan interest in given year CUMIPMT Calculate original loan amount PV
Calculate payment for a loan PMT Calculate payment periods for loan NPER
Calculate periods for annuity PMT PV FV Calculate principal for given period
PPMT Calculate simple interest Compare effect of compounding periods FV Currency
exchange rate example STOCKHISTORY Effective annual interest rate EFFECT RRI
Estimate mortgage payment PMT PV FV Future value of annuity FV PV Future value
vs. Present value PV FV Get current stock price FIELDVALUE STOCKHISTORY Get
stock price (latest close) STOCKHISTORY TODAY LOOKUP INDEX Get stock price last
n days STOCKHISTORY SORT Get stock price last n months STOCKHISTORY TODAY EDATE
INDEX Get stock price on specific date STOCKHISTORY Income tax bracket
calculation VLOOKUP NPV formula for net present value NPV Payment for annuity
PMT PV FV Present value of annuity PV FV Required recovery rate

#Geometry Formula Related Functions Area of a circle PI POWER Area of a
parallelogram Area of a trapezoid Area of a triangle Circumference of a circle
PI Distance formula SQRT Pythagorean theorem Surface area of a cone PI POWER
Surface area of a cylinder PI POWER Surface area of a sphere PI POWER Volume of
a cone PI POWER Volume of a cylinder PI POWER Volume of a rectangular prism
Volume of a sphere PI POWER

#Workbook Formula Related Functions Dynamic workbook reference INDIRECT Dynamic
worksheet reference INDIRECT Get full workbook name and path CELL Get sheet name
only CELL TEXTAFTER MID FIND Get workbook name and path without sheet CELL LEFT
FIND SUBSTITUTE LET Get workbook name only CELL TEXTAFTER TEXTBEFORE MID FIND
Get workbook path only CELL LEFT FIND LET Indirect named range different sheet
INDIRECT List sheet index numbers SHEET List sheet names with formula INDEX MID
ROW NOW T Worksheet name exists ISREF INDIRECT

#Internet Formula Related Functions Create email address from name LEFT LOWER
Create email address with name and domain LEFT LOWER Create email with display
name CONCAT CONCATENATE TEXTJOIN Get domain from email address RIGHT LEN FIND
Get domain name from URL LEFT FIND Get name from email address LEFT FIND Get
page from URL TRIM RIGHT SUBSTITUTE REPT Get top level domain (TLD) RIGHT LEN
SUBSTITUTE FIND Remove trailing slash from url LEN RIGHT LEFT Strip protocol and
trailing slash from URL MID RIGHT LEN FIND

#Names Formula Related Functions Get first name from name LEFT FIND Get first
name from name with comma RIGHT LEN FIND Get last name from name RIGHT LEN
SUBSTITUTE FIND Get last name from name with comma LEFT FIND Get middle name
from full name MID LEN TRIM Join first and last name CONCATENATE Put names into
proper case PROPER TRIM

#Percentage Formula Related Functions Calculate percent variance ABS Calculate
percentage of number Decrease by percentage Difference is within specific
percentage IF ABS Get amount with percentage Get original number from percent
change 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 Percent sold Project complete percentage COUNTA

#Random Formula Related Functions Random date between two dates RANDBETWEEN
WORKDAY Random number between two numbers RANDBETWEEN Random number from fixed
set of options CHOOSE RANDBETWEEN Random number weighted probability RAND MATCH
INDEX Random text values CHOOSE RANDBETWEEN Random times at specific intervals
RAND Random value from list or table INDEX RANDBETWEEN ROWS Randomly assign data
to groups RANDBETWEEN CHOOSE Randomly assign people to groups RAND ROUNDUP
CEILING

#Range Formula Related Functions Add row numbers and skip blanks COUNTA IF
ISBLANK Address of first cell in range ADDRESS ROW COLUMN CELL Address of last
cell in range ADDRESS ROW COLUMN ROWS COLUMNS All cells in range are blank
SUMPRODUCT All values in a range are at least COUNTIF NOT Automatic row numbers
SEQUENCE ROW INDEX COUNTA Combine ranges with CHOOSE CHOOSE Count visible
columns CELL N SUM COUNTIFS with variable range COUNTIFS OFFSET ADDRESS INDIRECT
ROW Define range based on cell value INDEX OFFSET Dynamic named range with INDEX
INDEX COUNTA Dynamic named range with OFFSET OFFSET COUNTA First column number
in range ROWS MIN First match between two ranges INDEX MATCH COUNTIF First row
number in range ROW MIN Get address of named range or table ADDRESS ROW COLUMN
ROWS COLUMNS LET TAKE LAMBDA TOCOL Get relative column numbers in range COLUMN
SEQUENCE COLUMNS Get relative row numbers in range ROW SEQUENCE ROWS Last column
number in range COLUMN COLUMNS MIN Last n rows ROW ROWS INDEX Last row in mixed
data with blanks MATCH Last row in mixed data with no blanks COUNTA Last row in
numeric data MATCH Last row in text data MATCH REPT Last row number in range ROW
ROWS MIN Multiple cells are equal AND EXACT Multiple cells have same value
COUNTIF Multiple cells have same value case sensitive EXACT SUMPRODUCT COUNTA
Multiple columns are equal AND COUNTIF Range contains a value not in another
range SUMPRODUCT MATCH ISNA Range contains numbers SUMPRODUCT ISNUMBER Range
contains specific date COUNTIFS DATE TODAY Total cells in a range ROWS COLUMNS
Total columns in range COLUMNS Total rows in range ROWS

#Tables Formula Related Functions Automatic row numbers in Table ROW INDEX
Average last N values in a table INDEX AVERAGE ROWS Basic inventory formula
example SUMIFS Count table columns COLUMNS Count table rows ROWS COUNTIFS with
variable table column COUNTIFS INDEX MATCH INDIRECT Dynamic reference to table
INDIRECT Get column index in Excel Table MATCH Get column name from index in
table INDEX Percentile IF in table PERCENTILE Running count in Table INDEX SUM
Running total in Table INDEX SUM Sum multiple tables SUM SUMIFS vs other lookup
formulas SUMIFS INDEX MATCH LOOKUP XLOOKUP SUMPRODUCT SUMIFS with Excel Table
SUMIFS Two-way lookup VLOOKUP in a Table VLOOKUP MATCH Two-way summary with
SUMIFS SUMIFS

#Errors Formula Related Functions How to fix a circular reference error How to
fix the #### (hashtag) error How to fix the #CALC! error IFERROR ISERROR
ERROR.TYPE How to fix the #DIV/0! error IFERROR ISERROR ERROR.TYPE How to fix
the #N/A error VLOOKUP IFERROR MATCH How to fix the #NAME? error IFERROR ISERROR
ERROR.TYPE How to fix the #NULL! error IFERROR ISERROR ERROR.TYPE How to fix the
#NUM! error IFERROR ISERROR ERROR.TYPE How to fix the #REF! error ISREF IFERROR
How to fix the #SPILL! error IFERROR ISERROR ERROR.TYPE How to fix the #VALUE!
error IFERROR ISERROR ERROR.TYPE

#Miscellaneous Formula Related Functions Abbreviate state names VLOOKUP INDEX
MATCH Add leading zeros to numbers TEXT All dates in chronological order IF
SUMPRODUCT SORT Basic array formula example MAX MIN Basic attendance tracking
formula COUNTIF Basic error trapping example IFERROR Basic in cell histogram
REPT CHAR Basic numeric sort formula RANK COUNTIF Basic outline numbering COUNTA
MID FIND LEN Basic text sort formula RANK COUNTIF BMI calculation formula
CONVERT POWER Build hyperlink with VLOOKUP HYPERLINK VLOOKUP Calculate a ratio
from two numbers GCD Calculate win loss tie totals SUMPRODUCT Cap percentage at
100 MIN Cap percentage at specific amount MIN Carry-on baggage Inches to
centimeters CONVERT Cash denomination calculator SUMPRODUCT INT FLOOR Celsius to
Fahrenheit conversion CONVERT Change negative numbers to positive ABS Check
register balance IF AND ISBLANK Coefficient of variation STDEV.P STDEV.S AVERAGE
Conditional median with criteria MEDIAN Conditional mode with criteria MODE
Convert column letter to number COLUMN INDIRECT Convert column number to letter
ADDRESS SUBSTITUTE TEXTBEFORE Convert expense time units INDEX MATCH Convert
feet and inches to inches LEFT FIND MID SUBSTITUTE Convert inches to feet and
inches INT MOD TRUNC ABS Convert negative numbers to zero MAX Convert numbers to
1 or 0 IF Convert pounds to kilograms CONVERT Copy value from every nth column
OFFSET COLUMN Copy value from every nth row OFFSET ROW Cost of living adjustment
Count consecutive monthly orders FREQUENCY MAX IF Count values out of tolerance
SUMPRODUCT ABS Count with repeating values ROUNDUP COLUMN ROW Course completion
status summary COUNTIFS Course completion summary with criteria COUNTIFS Create
array of numbers INDIRECT ROW Cube root of number POWER Customer is new COUNTIFS
Display sorted values with helper column INDEX MATCH ROWS Dropdown sum with all
option SUMIF SUM IF Easy bundle pricing with SUMPRODUCT SUMPRODUCT Expense
begins on specific month IF Extract unique items from a list INDEX MATCH COUNTIF
LOOKUP Filter values in array formula ISNUMBER MATCH IF COUNT Find and retrieve
missing values INDEX MATCH ISNA Fixed value every N columns MOD COLUMN Flag
first duplicate in a list COUNTIF COUNTIFS Flip table rows to columns TRANSPOSE
Forecast vs actual variance SUMIFS Formula with locked absolute reference
INDIRECT Get date associated with last entry LOOKUP Get first entry by month and
year INDEX MATCH TEXT Get last entry by month and year LOOKUP TEXT Get pivot
table grand total GETPIVOTDATA Get pivot table subtotal GETPIVOTDATA Get pivot
table subtotal grouped date GETPIVOTDATA Get value of last non-empty cell LOOKUP
Hyperlink to first blank cell HYPERLINK CELL INDEX MATCH Hyperlink to first
match HYPERLINK CELL INDEX MATCH Increment a calculation with ROW or COLUMN ROW
COLUMN Increment a number in a text string RIGHT TEXT Increment cell reference
with INDIRECT INDIRECT CELL Leave a comment in a formula N Link to multiple
sheets HYPERLINK CELL INDEX MATCH List most frequently occuring numbers MODE
ISNUMBER MATCH Longest winning streak FREQUENCY MAX IF Lookup last file revision
SEARCH ISERROR MAX INDEX IF ROW Mark rows with logical tests COUNTIFS IF Most
frequently occurring number MODE Multiplication table formula New customers per
month COUNTIFS EOMONTH Nightly hotel rate calculation SUMPRODUCT FILTER INDEX
MATCH Normalize size units to Gigabytes MATCH LEFT RIGHT nth root of number
POWER Number is whole number MOD TRUNC INT Odometer gas mileage log SUM MAX MIN
One or the other not both XOR Pad a number with zeros TEXT REPT Parse XML with
formula FILTERXML Random sort formula INDEX MATCH Range contains duplicates OR
COUNTIF SUMPRODUCT Range contains one of many substrings SUMPRODUCT COUNTIF
Range contains one of many values SUMPRODUCT ISNUMBER SEARCH Range contains
specific text COUNTIF Repeat fixed value every 3 months MOD DATEDIF Return array
with INDEX function INDEX MATCH N Reverse a list or range INDEX COUNTA ROW
SORTBY SEQUENCE Risk Matrix Example INDEX MATCH Score quiz answers with key SUM
COUNTA Search entire worksheet for value COUNTIF Search multiple worksheets for
value COUNTIF INDIRECT Send email with formula HYPERLINK Show formula text with
formula FORMULATEXT IFERROR ISFORMULA Simple currency conversion VLOOKUP INDEX
MATCH Sort and extract unique values MMULT TRANSPOSE INDEX MATCH Sort numbers
ascending or descending SMALL LARGE Sort text and numbers with formula RANK
COUNTIF COUNT Split payment across months AND Square root of number SQRT POWER
ABS Standard deviation calculation STDEV.P STDEV.S STDEV STDEVP Student class
enrollment with table IF COUNTIF Sum every 3 cells OFFSET COLUMN Sum Roman
numbers ARABIC ROMAN SUMPRODUCT SUM Sum text values like numbers INDEX MATCH N
Text is greater than number COUNTIF COUNTIFS SUMPRODUCT ISNUMBER Transpose table
without zeros TRANSPOSE IF Unwrap column into fields OFFSET TRANSPOSE ROW
Validate input with check mark IF COUNTIF Value exists in a range COUNTIF MATCH
ISNUMBER Value is between two numbers AND MAX MIN Value is within tolerance IF
ABS Volunteer hours requirement calculation AND COUNTIF SUM

Back to Top



DOWNLOAD 100+ IMPORTANT EXCEL FUNCTIONS

Get PDF Guide

OMG.....how easy! I will never do anything other than a Pivot Table if needed.
Annie
More Testimonials


GET TRAINING


QUICK, CLEAN, AND TO THE POINT TRAINING

Learn Excel with high quality video training. Our videos are quick, clean, and
to the point, so you can learn Excel in less time, and easily review key topics
when needed. Each video comes with its own practice worksheet.

View Paid Training & Bundles

Exceljet
Quick, clean, and to the point


RESOURCES

 * Training
 * Videos
 * Functions
 * Formulas
 * Shortcuts
 * Articles


ABOUT US

 * About
 * Testimonials
 * Topics
 * Contact


NEWSLETTER SIGN-UP

Work faster in Excel. Join more than 100,000 people who get weekly tips from us.
Email
HP
© 2012-2023 Exceljet. Terms of use
 * Visit our Twitter page