More MUST KNOW functio
Already learned: Concatenate, PMT, IPMT, IF, IF/AND, Auto
CountA
Rank
Sumifs
Averageifs
Iferror
Countifs
Sumproduct
Find
CountA
Name Spending Month CountA
Mike $6,548.00 January
Peter $651.00 April
Jenny $9,624.00 March
Peter M $654.00 May
Emma June
Christine $4,598.00 January
Frank December
Conrad $459.00 August
Denise September
Hosea $6,587.00 March
Rank
Name English Maths Sience Average
Mike 82 91 96 89.6666667
Peter M 98 96 93 95.6666667
Christine 94 81 84 86.3333333
Jenny 81 94 78 84.3333333
Peter 91 89 86 88.6666667
Conrad 85 87 93 88.3333333
Emma 73 92 95 86.6666667
Frank 86 86 79 83.6666667
Hosea 68 73 95 78.6666667
Denise 73 95 85 84.3333333
Sumif
Item Region Tonnage Item
Steel North 527 Steel
Cobolt West 452 Cobolt
Gold West 1.1 Gold
Aluminium South 253 Aluminium
Aluminium East 214 Silver
Cobolt South 521
Silver West 2.3
Steel East 416
Silver East 4.8
Gold South 1.7 1308
Steel South 365
Averageif
Item Region Tonnage Item
Steel North 527 Steel
Cobolt West 452 Cobolt
Gold West 1.1 Gold
Aluminium South 253 Aluminium
Aluminium East 214 Silver
Cobolt South 521
Silver West 2.3
Steel East 416 436
Silver East 4.8
Gold South 1.7
Steel South 365
Iferror
Income
January February March Total Total with Iferror
$542.00 $1,263.00 $652.00 $2,457.00
$649.00 $1,202.00 $683.00
$321.00 $1,035.00 $792.00
$154.00 $654.00 $375.00
$695.00 $945.00 Off
$618.00 $862.00 $476.00
No pay $625.00 $498.00
$935.00 $453.00 $831.00
Countif
Item Region Tonnage Item
Steel North 527 Steel
Cobolt West 452 Cobolt
Gold West 1.1 Gold
Aluminium South 253 Aluminium
Aluminium East 214 Silver
Cobolt South 521
Silver West 2.3
Steel East 416
Silver East 4.8
Gold South 1.7
Steel South 365
Sumproduct
Item Cost P/I Est sold Total sold Est sold
Clutch £79.00 27 29
Hub assembly £85.00 52 48
Rack and pinion £190.00 11 16
Sway arm £30.00 25 21
Down pipe £45.00 21 26
Window regulator £95.00 52 53
Thermostat £43.00 23 24
Water pump £48.00 41 41
Oil filter £9.00 48 41
Cylinder head £240.00 17 15
Sump pan £83.00 36 32
Total:
Find
Course name Value Find result1 Find result2
ExcelBeginnerToAdvanced A
ExcelBeginnerToAdvanced T
ExcelBeginnerToAdvanced To
ExcelBeginnerToAdvanced A
ExcelBeginnerToAdvanced d
ExcelBeginnerToAdvanced dv
Course name Find result With IfError Find with IFERROR
ExcelBeginnerToAdvanced
BeginnerToAdvancedExcel
ExcelBeginnersToAdvanced
ExcelBeginners
BeginnersExcel
AdvancedExcel
OW functions
MT, IF, IF/AND, AutoSum, Vlookup
"CountA" can be used to quickly
find out if there is any missing
pieces of data
Total Rank
269
287
259
253
266 "Rank" can be used to rank data
265 in order of value. Not to be
confused with sorting
"Rank" can be used to rank data
in order of value. Not to be
260 confused with sorting
251
236
253
Totals
"Sumif" can be used to sum up
data from specified catagories
Totals
"Averageif" can be used to find
the average of specified
catagories
Total with Iferror
"Iferror" identifies errors that
originate from formulas or
functions and excludes them.
"Iferror" identifies errors that
originate from formulas or
functions and excludes them.
Totals
"Countif" can be used count a
certain number of criteria
Total sold
Est sold: "SumProduct" elimates
Total sold: unecessary steps that need t
be taken in order to find a tota
Remember!
The word "product" within th
Sumproduct function, refers t
the "Result of multiplication"
Use the Find function to find k
Find with IFERROR Example pieces of data
14
9
15
Error
Error
Error
ct" elimates
ps that need to
r to find a total.
mber!
uct" within the
nction, refers to
multiplication"
ction to find key
of data