Formulas and Functions Help
- Welcome
- Intro to formulas and functions
-
- ACCRINT
- ACCRINTM
- BONDDURATION
- BONDMDURATION
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNUM
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENCYCODE
- CURRENCYCONVERT
- CURRENCYH
- DB
- DDB
- DISC
- EFFECT
- FV
- INTRATE
- IPMT
- IRR
- ISPMT
- MIRR
- NOMINAL
- NPER
- NPV
- PMT
- PPMT
- PRICE
- PRICEDISC
- PRICEMAT
- PV
- RATE
- RECEIVED
- SLN
- STOCK
- STOCKH
- SYD
- VDB
- XIRR
- XNPV
- YIELD
- YIELDDISC
- YIELDMAT
-
- AVEDEV
- AVERAGE
- AVERAGEA
- AVERAGEIF
- AVERAGEIFS
- BETADIST
- BETAINV
- BINOMDIST
- CHIDIST
- CHIINV
- CHITEST
- CONFIDENCE
- CORREL
- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS
- COVAR
- CRITBINOM
- DEVSQ
- EXPONDIST
- FDIST
- FINV
- FORECAST
- FREQUENCY
- GAMMADIST
- GAMMAINV
- GAMMALN
- GEOMEAN
- HARMEAN
- INTERCEPT
- LARGE
- LINEST
- LOGINV
- LOGNORMDIST
- MAX
- MAXA
- MAXIFS
- MEDIAN
- MIN
- MINA
- MINIFS
- MODE
- NEGBINOMDIST
- NORMDIST
- NORMINV
- NORMSDIST
- NORMSINV
- PERCENTILE
- PERCENTRANK
- PERMUT
- POISSON
- PROB
- QUARTILE
- RANK
- SLOPE
- SMALL
- STANDARDIZE
- STDEV
- STDEVA
- STDEVP
- STDEVPA
- TDIST
- TINV
- TTEST
- VAR
- VARA
- VARP
- VARPA
- WEIBULL
- ZTEST
Functions that accept conditions and wild cards as arguments
Some functions, such as SUM, operate on entire collections. Other functions, such as SUMIF, operate only on the cells in the collection that meet a condition. For example, to add up all the numbers in column B that are less than 5, you could use:
=SUMIF(B,“<5”)
The second argument of SUMIF is called a condition because it causes the function to ignore cells that do not meet the requirements.
There are two types of functions that take conditions:
Functions that have names ending in IF or IFS (except for the function IF, which does not take a condition; it instead takes an expression that should evaluate to either TRUE or FALSE). These functions can do numeric comparisons using the comparison operators in their conditions, such as “>5”, “<=7” or “<>2”. These functions also accept wild cards in specifying conditions. For example, to count the number of cells in column B that begin with the letter “a”, you could use:
=COUNTIF(B,“a*”)
Functions that take conditions, such as HLOOKUP, but can’t do numeric comparisons. These functions sometimes permit the use of wild cards.
The table below lists all the functions that can accept conditions — either numeric comparisons, wildcards, regular expressions, or a combination of the three.
Function | Allows numeric comparisons | Accepts wild cards | Accepts regular expressions |
|---|---|---|---|
Yes | Yes | Yes | |
Yes | Yes | Yes | |
Yes | Yes | Yes | |
Yes | Yes | Yes | |
No | Yes | Yes | |
No | If exact match specified | Yes | |
No | No | Yes | |
No | No | Yes | |
No | Yes | Yes | |
No | If exact match specified | Yes | |
Yes | Yes | Yes | |
Yes | Yes | Yes | |
No | Yes | Yes | |
No | Yes | Yes | |
Yes | Yes | Yes | |
Yes | Yes | Yes | |
No | Yes | Yes | |
No | Yes | Yes | |
No | Yes | Yes | |
No | If exact match specified | Yes | |
No | If exact match specified | Yes | |
No | If exact match specified | Yes |