Get started with formulas

In just a few steps, you'll be up and running with building formulas and functions in Excel, the
world's most powerful spreadsheet app.

Go back to top by pressing CTRL+HOME. To start the tour, press CTRL+PAGE DOWN.
 Basics: doing math with Excel
Basics: doing math with Excel
You can Add, Subtract, Mul ply, and Divide in Excel without using any built-in func ons. You just need to use some basic opera
To Add, select cell F3, type =C3+C4, then press Enter.
You
To Subtract, can cell
select Add,F4,Subtract, Multiply,
type =C3-C4, and Divide
then press Enter. in Excel without using any built
You just need to use some basic operators:
To Mul ply, select cell F5, type =C3*C4, then press Enter.
To Divide, sign.
select cell F6, type =C3/C4, then press Enter.
Check this out: change the numbers in cells C3 and C4, and watch the formula results automatically change.
EXTRA CREDIT: 1 You can raise a value to a power by using the carat (^) symbol, like =A1^A2. Enter it with Shift+6. In cell F7, en
Dive down for more detail
Next step
2
More about formulas, cells, and ranges
Excel is made up of individual cells that are grouped into rows and columns. Rows are numbered, and columns are le ered. Th
Formulas can
3 in our third example above, we used the SUM func on. A func on is a pre-built command that takes a value
You'll no ce that
Formulas with func ons start with an equals sign, then the func on name follows with its arguments (the values a func on us
You confirm a formula by pressing Enter. Once you do that the formula will calculate, and the result will be displayed in the ce
Some formula 4explanations
=10*20 is a formula, where 10 and 20 are constants, and the * sign is the operator.
=SUM(A1:A10) is a formula, where SUM is the function name, the opening and closing parentheses contain the formula argum
=SUM(A1:A10,C1:C10) is a formula, where SUM is the function name, the opening and closing parentheses contain the formu
GOOD TO
Previous
Next
More information on the web
More about formulas, cells, and ranges
Use Excel as a calculator
Overview Excel
of formulas in Excel
is made up of individual cells that are grouped into rows and columns. Rows are
Excel functions (by category)
numbered, and columns are lettered. There are over 1 million rows and 16,000 columns,
and you
Excel functions can put formulas in any of them.
(alphabetical)
Free ExcelFormulas can contain cell references, ranges of cell references, operators, and constants.
training online
The following are all examples of formulas:

You'll notice that in our third example above, we used the

pre-built command that takes a value or values, calculates them in a certain way, and
returns a result. For instance, the
specify, and totals them. In this example it takes the cells A1 through A10, and totals them.
Excel has over 400 functions, which you can explore on the

Formulas with functions start with an equals sign, then the function name follows with its
Formulas with functions start with an equals sign, then the function name follows with its
arguments (the values a function uses to calculate) wrapped in parentheses.

You confirm a formula by pressing

the result will be displayed in the cell. To see the formula itself, you can look at the formula
bar beneath the Ribbon, or press
cell. Press

More information on the web

 Basics: doing math with Excel
cs: doing math with Excel
can Add, Subtract, Mul ply, and Divide in Excel without using any built-in func ons. You just need to use some basic operators: +, -, *, /. A
Add, select cell F3, type =C3+C4, then press Enter.
You
ubtract, can cell
select Add,F4,Subtract, Multiply,
type =C3-C4, and Divide
then press Enter. in Excel without using any built-in functions.
Mul ply, select cell F5, type =C3*C4, then press Enter. +, -, *, /. All formulas start with an equals (=)
You just need to use some basic operators:
Divide, select cell F6, type =C3/C4, then press Enter.
ck this out: change the numbers in cells C3 and C4, and watch the formula results automatically change.
RA CREDIT: You TocanAdd,
raise select
a valuecell
to aF3, typeby
power =C3+C4,
using thethen
caratpress Enter.like =A1^A2. Enter it with Shift+6. In cell F7, enter =C3^C4.
(^) symbol,
e down for more detail

To cells,
re about formulas, Subtract, select cell F4, type =C3-C4, then press Enter.
and ranges
el is made up of individual cells that are grouped into rows and columns. Rows are numbered, and columns are le ered. There are over 1 m

ll no ce that in To
ourMultiply, select
third example cell F5,
above, we type
used =C3*C4, then on.
the SUM func press Enter.
A func on is a pre-built command that takes a value or values, calcu
mulas with func ons start with an equals sign, then the func on name follows with its arguments (the values a func on uses to calculate)
confirm a formula by pressing Enter. Once you do that the formula will calculate, and the result will be displayed in the cell. To see the for
To Divide, select cell F6, type =C3/C4, then press Enter.
me formula explanations
*20 is a formula, where 10 and 20 are constants, and the * sign is the operator.
M(A1:A10) is a formula, where SUM is the function name, the opening and closing parentheses contain the formula arguments, and A1:A
M(A1:A10,C1:C10) is a formula, where SUM is the function name, the opening and closing parentheses contain the formula arguments, an
Dive down for more detail Next step

re information on the web

More about formulas, cells, and ranges
Excel as a calculator
rview Excel
of formulas in Excel
is made up of individual cells that are grouped into rows and columns. Rows are
el functions (by category)
numbered, and columns are lettered. There are over 1 million rows and 16,000 columns,
and you
el functions can put formulas in any of them.
(alphabetical)
e ExcelFormulas can contain cell references, ranges of cell references, operators, and constants.
training online
The following are all examples of formulas: =A1+
=A1+B1
=10*20 B1
=SUM(A1:A10)
You'll notice that in our third example above, we used the SUM function. A function is a
built command that takes a value or values, calculates them in a certain way, and
returns a result. For instance, the SUM function takes the cell references or ranges you
specify, and totals them. In this example it takes the cells A1 through A10, and totals them.
Excel has over 400 functions, which you can explore on the Formulas tab. =SUM(A
Formulas with functions start with an equals sign, then the function name follows with its
Formulas with functions start with an equals sign, then the function name follows with its
arguments (the values a function uses to calculate) wrapped in parentheses.

You confirm a formula by pressing Enter. Once you do that the formula will calculate, and
the result will be displayed in the cell. To see the formula itself, you can look at the formula
bar beneath the Ribbon, or press F2 to enter Edit mode, where you'll see the formula in the
cell. Press Enter again to finalize the formula and calculate the result.

=SUM(A
)
More information on the web
Use Excel as a calculator

Overview of formulas in Excel

Excel functions (by category)

Excel functions (alphabetical)

Free Excel training online

 Numbers to use: Operation: Formulas: Answers:
1 Addition (+) 3
2 Subtraction (-) -1
Multiplication (*) 2
Division (/) 0.5
Power (^) 1
t+6. In cell F7, enter =C3^C4.
CHECK THIS OUT EXTRA CREDIT
Change the numbers here, and You can raise a value to a
watch the formula results power by using the carat (^)
symbol, like =C3^C4. Enter
ns are le ered. Thereautomatically change.
are over 1 million rows and 16,000 columns, and you canitput formulas
with Shift+6.in any of them.

hat takes a value or values, calculates them in a certain way, and returns a result. For instance, the SUM func on takes the cell references
lues a func on uses to calculate) wrapped in parentheses.
splayed in the cell. To see the formula itself, you can look at the formula bar beneath the Ribbon, or press F2 to enter Edit mode, where yo

he formula arguments, and A1:A10 is the cell range for the function.
ontain the formula arguments, and A1:A10,C1:C10 are the cell ranges for the function separated by a comma.

GOOD TO KNOW
Operator Operator Constants are values that you enter in cells o

=A1+
While =10*20 might calculate the same as =A

Cel =10*20 aren't a good practice. Why? Because you ca

constant without selecting the cell and lookin
make it hard to change later. It's much easier
l Cell

B1 ref
ere
referenc
e
Constant Constant constants in cells, where they can be easily a
referenced in your formulas.
nce
For example: Select the yellow cell with
we used the SUM function with a range of ce
Function Argument type in "4" or "8" directly into the formula.

=SUM(A1:A10)
A range of cells has a starting cell, colon, and an
 A range of cells has a starting cell, colon, and an
ending cell. When you select a range of cells for a
formula, Excel will automatically add the colon.

Commas separate multiple

arguments
Function Argument Argument

=SUM(A1:A10,C1:C10
) A range of cells Another range of cells

Opening parenthesis Closing parenthesis. Excel will usually add this

for you when you press Enter.
unc on takes the cell references or ranges you specify, and totals them. In this example it takes the cells A1 through A10, and totals them.

F2 to enter Edit mode, where you'll see the formula in the cell. Press Enter again to finalize the formula and calculate the result.

e values that you enter in cells or formulas.

20 might calculate the same as =A1+B1, constants
d practice. Why? Because you can't easily see the
hout selecting the cell and looking for it. That can
d to change later. It's much easier to put your
cells, where they can be easily adjusted, and
n your formulas.

: Select the yellow cell with 12 below. You'll see

function with a range of cells. We didn't
r "8" directly into the formula.

Values
4
 8
12

sually add this

A1 through A10, and totals them. Excel has over 400 func ons, which you can explore on the Formulas tab.

and calculate the result.

 Introduction to functions
Introduction to functions
Functions give you the ability to do a variety of things, like perform mathematical operations, look up values, or even calculat
Under the
Functions
Now let's try AutoSum.give youthe
Select thecell
ability
undertothe
do column
a varietyforofMeat
things,
(celllike
G7),perform
then go mathematical
to Formulas > AutoSum > select SUM. You
operations,
Here's a neat keyboardlook up values,
shortcut. Select or
celleven
D15,calculate
then pressdates
Alt =,and
thentimes.
Enter.Let's try a few ways
This automatically to add
enters upfor you.
SUM
Dive downvalues with
for more the
detail
EXTRA CREDITTry the COUNT func on using any of the methods you've already tried. The COUNT func on counts the number
Next step 1
More about functions
Go to the Formulas tab and browse through the Function Library, where functions are listed by category, like Text, D
2 on could talk, it would say, return the sum of all the values in cells D38 to D41, and all of column H. SUM is th
If the SUM func
The TODAY function returns today's date. It will automatically update when Excel recalculates.
CHECK THIS OUT
IMPORTANT
More information on the web
All about the3SUM function
Use AutoSum to sum numbers
All about the COUNT function
Free Excel training online
Back to top
Next step

More about functions

Go to the
by category, like
name, and launch a function wizard that can help you build your formula.

When you start typing a function name after you press

will list all of the functions starting with the letters you type. When you find the one you want,
press Tab, and Excel will automatically finish the function name and enter the opening
parenthesis for you. It will also display the optional and required arguments.

Now let's look at the anatomy of a few functions. The

 If the
to D41, and all of column H".

Now, let's try one that doesn't require any arguments.

More information on the web

Free Excel training online

Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN.
 Introduction to functions
oduction to functions
ctions give you the ability to do a variety of things, like perform mathematical operations, look up values, or even calculate dates and time

Functions
w let's try AutoSum. give youthe
Select thecell
ability
undertothe
do column
a variety
forofMeat
things,
(celllike
G7),perform
then go mathematical
to Formulas > AutoSum > select SUM. You'll see Excel auto
operations,
e's a neat keyboardlook up values,
shortcut. Select or
celleven
D15,calculate
then pressdates
Alt =,and
thentimes.
Enter.Let's try a few ways
This automatically to add
enters upfor you.
SUM
e downvalues with
for more the SUM function.
detail
Try the COUNT func on using any of the methods you've already tried. The COUNT func on counts the number of cells in a ran
Under the Amount column for Fruit (cell D7), enter =SUM(D3:D6), or type =SUM(,
re about functions then select that range with the mouse, and press Enter. This will sum the values in
to the Formulas cells
tabD3,
andD4, D5, and
browse D6. Your
through the answer
Function should bewhere
Library, 170. functions are listed by category, like Text, Date & Time, etc
e SUM func onNow
couldlet's
talk,try AutoSum.
it would Select
say, return the
the yellow
sum of all cell
the under thecells
values in column
D38 tofor Meat
D41, and(cell
all of column H. SUM is the func on name
TODAY functionG7), thentoday's
returns go to date.
Formulas
It will > AutoSum >update
automatically selectwhen
SUM.Excel
You'll see Excel
recalculates.
automatically enter the formula for you. Press Enter to confirmlook
CK THIS OUTSelect these cells. Then in the lower-right corner of the Excel window, it. The AutoSum
for SUM: 170 in the bo om bar. That's called the S
feature has all of the most common functions.
re information on the web
Here's a neat keyboard shortcut. Select cell D15, then press
about the SUM function Al t =
AutoSum to sum then, Enter. This automatically enters SUM for you.
numbers
about the COUNT function
e Excel training online
Dive down for more detail Next step

More about functions

to the Formulas tab and browse through the Function Library, where functions are listed
by category, like Text, Date & Time, etc. Insert Function will let you search for functions by
name, and launch a function wizard that can help you build your formula.

When you start typing a function name after you press =, Excel will launch Intellisense, which
will list all of the functions starting with the letters you type. When you find the one you want,
press Tab, and Excel will automatically finish the function name and enter the opening
parenthesis for you. It will also display the optional and required arguments.

Now let's look at the anatomy of a few functions. The SUM function is structured like this:

The function The first Additional

name. argument. It's arguments,
almost always separated by
 almost always separated by
required. commas (,).

=SUM(D38:D41,H:H)
If the SUM function could talk, it would say, "Return the sum of all the values in cells D38
to D41, and all of column H".

Now, let's try one that doesn't require any arguments.

The TODAY function returns today's date. It will

automatically update when Excel recalculates.

=TODAY()

More information on the web

All about the SUM function

Use AutoSum to sum numbers

All about the COUNT function

Free Excel training online

Back to top Next step

e Excel training online

back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN.
Fruit Amount Meat Amount
Apples 50 Beef 50
Oranges 20 Chicken 30
Bananas 60 Pork 10
Lemons 40 Fish 50
SUM > 170 SUM >

Item Amount Item Amount

Bread 50 Bread 50
Donuts 100 Donuts 100
Cookies 40 Cookies 40
Cakes 50 Cakes 50
Pies 20 Pies 20
SUM > COUNT >

EXTRA CREDIT
Try the COUNT function using any of
the methods you've already tried.
The COUNT function counts the
number of cells in a range that
contain numbers.

Fruit Amount
Apples 50 CHECK THIS OUT
Oranges 20 Select these cells. Then in the lower-right
Bananas 60 corner of the Excel window, look for this:
Lemons 40
SUM > 170
That's called the Status Bar, and it's just
another way to quickly find a total and other
details about a selected cell or range.

Item Amount
Cars 20
Trucks 10
Bikes 10
Skates 40

Total: Additional Value New Total

180 100 180

IMPORTANT DETAIL
Double-click this cell. You'll notice the 100 toward the end.
Although it's possible to put numbers in a formula like this,
we don't recommend it unless it's absolutely necessary.
This is known as a constant, and it's easy to forget that it's
there. We recommend referring to another cell instead,
like cell F51. That way it's easily seen and not hidden
inside a formula.
AVERAGE
function AVERAGE function
Use the AVERAGE
Select cellUse
D7,the
then use
Now select cell G7, and enter an
In cell D15, 1
you can use either
CHECK THIS
Activate the previous sheet
2
Go to the next sheet
EXTRA CREDIT
Links for more information on the web
3
Select to learn all about the AVERAGE function on the web
Select to learn all about the MEDIAN function on the web
Select to learn all about the MODE function on the web
Select to learn about free Excel training on the web

More information on the web

 AVERAGE function
AVERAGE function to get the average of numbers in a range of cells.
ect cellUse
D7,the
then use AutoSum
AVERAGE to to
function addget
anthe
AVERAGE
averagefunction.
of numbers in a range of cells.
w select cell G7, and enter an AVERAGE function by typing =AVERAGE(G3:G6).
ell D15, you can use either
Select AutoSum,
cell D7, or type to enter
then use AutoSum to addanother AVERAGE
an AVERAGE function.
function.

vate the previous sheet

o the next sheet Now select cell G7, and enter an AVERAGE function by typing
=AVERAGE(G3:G6).
s for more information on the web
In the
ct to learn all about cellAVERAGE
D15, you can use
function either
on the webAutoSum, or type to enter another AVERAGE
function.
ct to learn all about the MEDIAN function on the web
ct to learn all about the MODE function on the web
ct to learn about free Excel training on the web
Previous Next

More information on the web

All about the AVERAGE function

All about the MEDIAN function

All about the MODE function

Free Excel training online

Fruit Amount Meat Amount
Apples 50 Beef 50 CHECK THIS OUT
Oranges 20 Chicken 30 Select any range of numbers
Bananas 60 Pork 10 then look in the Status Bar for an
instant Average.
Lemons 40 Fish 50
AVERAGE > AVERAGE >

Item Amount Item Amount

Bread 50 Bread 50
Donuts 100 Donuts 100
Cookies 40 Cookies 40
Cakes 50 Cakes 50
Pies 20 Pies 20
AVERAGE >

EXTRA CREDIT
Try using MEDIAN or MODE here.

MEDIAN gives you the value in the

middle of the data set, while
MODE gives the one that occurs
the most frequently.

More about the SUM function

In some of the above tips, we taught you how to use the SUM function. Here are more details about it.
Cells C37 through D41 contain data with two columns: Fruit and Amount.
The formula in cell D42: =SUM(D38:D41).
If the SUM function in cell D42 could talk, it would say this: Sum up the values in cells D38, D39, D40, and D41.
Here's another way it can be used:
Cells C47 through D48 contain data with two columns: Item and Amount.

More information on the web

All about the SUM function
All about the SUMIF function
Use Excel as your calculator
Free Excel training online
Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN.
ny range of numbers,
k in the Status Bar for an

ails about it.

D39, D40, and D41.

 MIN and MAX functions
MIN and MAX functions
Use the MIN function to get the minumum number in a range of cells.
Use the MAX Use the to get the maximum number in a range of cells.
function
then use theUse the
G7, and enter a
can use either1
information on
All aobut the MIN function
2
All about the MAX function
Free Excel training online

3
KNOW

More information on the web

You can use either

More information on the web

All about the MIN
All about the MAX
Use Excel as your calculator
Free Excel training online
 MIN and MAX functions
and MAX functions
function to get the minumum number in a range of cells.
Use the MIN
function to getfunction to get
the maximum the smallest
number in a rangenumber
of cells. in a range of cells.
Use the MAX function to get the largest number in a range of cells.

Select cell D7, then use the AutoSum Wizard to add a MIN function.

obut the MIN function

Now select cell G7, and enter a MAX function by typing =MAX(D3:D6).
bout the MAX function
Excel training online

In cell D15, you can use either the AutoSum Wizard, or type to enter a MIN or
MAX function.

More information on the web

can use either MIN or MAX with multiple ranges, or values to show the greater or lesser of those values, like =MIN(A1:A10,B1:B10), or =MAX(A1:A10,10

e information on the web

All about the MIN function
MIN function
MAX function
All about the MAX function
Excel as your calculator
Excel training online
Free Excel training online
 Fruit Amount Meat Amount
Apples 50 Beef 50
Oranges 20 Chicken 30
Bananas 60 Pork 10
Lemons 40 Fish 50
MIN > MAX >

Item Amount Item Amount

Bread 50 Bread 50
Donuts 100 Donuts 100
Cookies 40 Cookies 40
Cakes 50 Cakes 50
Pies 20 Pies 20
MIN or MAX > 10

GOOD TO KNOW
You can use either MIN or MAX with multiple
A1:A10,B1:B10), or =MAX(A1:A10,10).
ranges, or values to show the greater or lesser of
those values, like =MIN(A1:A10,B1:B10), or
=MAX(A1:A10,B1), where B1 contains a threshold
value, like 10, in which case the formula would
never return a result less than 10.
 Date functions
Date functions
Excel can give you the current date, based on your computer's regional settings. You can also add and subtract Dates.

Excel can give you the current date, based on your computer's regional settings. You can
Check out the TODAY func on, which gives you Today's date. These are live, or vola le func ons, so when you open your wor
also add and subtract Dates.
Subtract Dates - Enter your next birthday in MM/DD/YY format in cell D7, and watch Excel tell you how many days away it is b
Add Dates - Let's say you want to know what date a bill is due, or when you need to return a library book. You can add days to
GOOD TO KNOW 1
DETAIL
Time functions
Excel can give you the current me, based on your computer's regional se ngs. You can also add and subtract mes. For insta
In cell D28, 2
Add up hours
If this formula could talk, it would say, "Take the Time Out and subtract it from the Time In, then subtract the Lunch Out/In Ti
Previous 3
Next
More information on the web
All about the TODAY function
All about the NOW function
All about the DATE function
Free Excel training online
Time functions
Excel can give you the current time, based on your computer's regional settings. You can
also add and subtract times. For instance, you might need to keep track of how many
hours an employee worked each week, and calculate their pay and overtime.

3
More information on the web
 Date functions
el can give you the current date, based on your computer's regional settings. You can also add and subtract Dates.

Excel can give you the current date, based on your computer's regional settings. You can
ck out the TODAY func on, which gives you Today's date. These are live, or vola le func ons, so when you open your workbook tomorrow
also add and subtract Dates.
tract Dates - Enter your next birthday in MM/DD/YY format in cell D7, and watch Excel tell you how many days away it is by using =D7-D6 i
Dates - Let's say you want to know what date a bill is due, or when you need to return a library book. You can add days to a date to find o
Check out the TODAY function, which gives you Today's date. These are live, or
OD TO KNOWExcel keeps dates and mes based on the number of days star ng from January 1, 1900. Times are kept in frac onal por ons
volatile functions, so when you open your workbook tomorrow, it will have
tomorrow's date. Enter =TODAY() in cell D6.

el can give you the current me, based on your computer's regional se ngs. You can also add and subtract mes. For instance, you might
Subtract Dates - Enter your next birthday in MM/DD/YY format in cell D7, and
watch Excel tell you how many days away it is by using =D7-D6 in cell D8.
is formula could talk, it would say, "Take the Time Out and subtract it from the Time In, then subtract the Lunch Out/In Times, then multi
Add Dates - Let's say you want to know what date a bill is due, or when you need to
return a library book. You can add days to a date to find out. In cell D10, enter a
re information onrandom
the web number of days. In cell D11, we added =D6+D10 to calculate the due date
about the TODAYfrom today.
function
about the NOW function
about the DATE function
e Excel training online
Time functions
Excel can give you the current time, based on your computer's regional settings. You can
also add and subtract times. For instance, you might need to keep track of how many
hours an employee worked each week, and calculate their pay and overtime.

In cell D28, enter =NOW(), which will give the current time, and will update
each time Excel calculates. If you need to change the Time format, you can go to
Ctrl+1 > Number > Time > Select the format you want.

Add up hours between times - In cell D36 we've entered =((D35-D32)-(D34-

D33))*24, which calculates someone's start and end times, then subracts the
time they took for lunch. The *24 at the end of the formula converts the
fractional portion of the day that Excel sees into hours. You'll need to format
the cell as a Number though. To do that, go to Home > Format > Cells
(Ctrl+1) > Number > Number > 2 decimals.

If this formula could talk, it would say, "Take the Time Out and subtract it from
the Time In, then subtract the Lunch Out/In Times, then multiply those by 24 to
convert Excel's fractional time to hours", or =((Time In - Time Out)-(Lunch In
- Lunch Out))*24.
*24 to convert Excel's
fraction of a day to hours
 fraction of a day to hours
Time Out Lunch Out

=((D35-D32)-(D34-D33))*24

Time In Lunch In

The inner parentheses () make sure Excel calculates those

parts of the formula by themselves. The outer parentheses make sure
Excel multiplies the final inner result by 24.

Previous Next

More information on the web

All about the TODAY function

All about the NOW function

All about the DATE function

Free Excel training online

 u open your workbook tomorrow, it will have tomorrow's date. Enter =TODAY() in cell D6.
y days away it is by using =D7-D6 in cell D8.
Date functions
Today's date:
Your birthday:
Days until your birthday: IMPORTANT DETAIL
ct mes. For instance, you might need to keep track of how many hours an Ifemployee
you don'tworked
want Excel
eachtoweek,
display
anda calculate
negative number,
their pay and over
Grace period days: because you haven't entered your birthday yet, you can
Bill due on: use an IF function like this: =IF(D7="","",D7
says, "IF D7 equals nothing, then show nothing, otherwise
e Lunch Out/In Times, then multiply those by 24 to convert Excel's fractional time to hours", or =((Time In - Time Out)-(Lunch In - Lunch O
show D7 minus D6".

GOOD TO KNOW
Excel keeps dates and times based on the number of days
starting from January 1, 1900. Times are kept in fractional
portions of a day based on minutes. So 01/01/2017 12:30
PM is actually stored as 42736.5208. If the Time or Date
show up as numbers like that, then you can press Ctrl+1 >
Number > select a Date or Time format.

Time functions
Current Time:

Daily Hours Worked

Time In: 8:00 AM
Lunch Out: 12:00 PM
Lunch In: 1:00 PM
Time Out: 5:00 PM
Total Hours: 8
 Static Date & Time
Date: 09/27/17
Time: 8:52 AM

GOOD TO KNOW
You can use keyboard shortcuts to enter Dates and
Times that won't continuously change:

Date - Ctrl+;
Time - Ctrl+Shift+:
ay
anda calculate
negative number,
their pay and over me.
our birthday yet, you can
D7="","",D7-D6), which
n show nothing, otherwise
- Time Out)-(Lunch In - Lunch Out))*24.
 Joining text from different cells
Joining text from different cells
There are many mes in Excel when you want to join text that's in different cells. This example is very common, where you ha
In cell E3, enter =D3&C3 to join the last and first names.
SmithNancy There are look
doesn't many times
quite in though.
right Excel when you want
We need to addtoa comma
join textand
that's in different
a space. cells.
To do that This
we'll use quotes to create a ne
example
To create the is very
full name, common,
we'll where
join first and lastyou have
name, butfirst
useand lastwithout
a space names, aand wantIntoF3,
comma. combine them"&D3.
enter =C3&"
as first name,
Dive down for more detail last name, or full name. Fortunately, Excel lets us do that with the Ampersand
Next (&) sign, which you can enter with
Using text and numbers together
Now we'll use1the & to join text and numbers, not just text and text
In cell C36, enter =C28&" "&TEXT(D28,"MM/DD/YYYY"). MM/DD/YYYY is the US format code for Month/Day/Year, like 09/25/
In cell C37, enter =C29&" "&TEXT(D29,"HH:MM AM/PM"). HH:MM AM/PM is the US format code for Hours:Minutes AM or PM
CHECK THIS OUT 2
WORTH EXPLORING
More information on the web
All about the TEXT function
Combine text and numbers
3 online
Free Excel training

Using text and numbers together

Now we'll use the & to join text and numbers, not just text and text

Look at cells C28:D29. See how the date and times are in separate cells? You can join them
together with the
it? Unfortunately, Excel doesn't know how you want to format the numbers, so it breaks
them down to their basest format, which is the the Serial date in this case. We need to
explicity tell Excel how to format the number portion of the formula, so it displays the way
you want in the resulting text string. You can do that with the
code.

2
More information on the web
 Joining text from different cells
ing text from different cells
re are many mes in Excel when you want to join text that's in different cells. This example is very common, where you have first and last
ell E3, enter =D3&C3 to join the last and first names.
thNancy There are look
doesn't many times
quite in though.
right Excel when you want
We need to addtoa comma
join textand
that's in different
a space. cells.
To do that This
we'll use quotes to create a new text string. Th
example
reate the is very
full name, common,
we'll where
join first and lastyou have
name, butfirst
useand lastwithout
a space names, aand wantIntoF3,
comma. combine them"&D3.
enter =C3&"
as first name,
e down for more detail last name, or full name. Fortunately, Excel lets us do that with the Ampersand
) sign, which you can enter with Shift+7.
ng text and numbers together
w we'll use the &Intocell
joinE3,
textenter =D3&C3not
and numbers, to join the and
just text last textLook
and first at
names.
cells C28:D29. See how the date and mes are in separate cells? Yo
ell C36, enter =C28&" "&TEXT(D28,"MM/DD/YYYY"). MM/DD/YYYY is the US format code for Month/Day/Year, like 09/25/2017.
ell C37, enter =C29&" "&TEXT(D29,"HH:MM AM/PM"). HH:MM AM/PM is the US format code for Hours:Minutes AM or PM, like 1:30 PM.
SmithNancy doesn't look quite right though. We need to add a comma and a
CK THIS OUTFormulas, especially big ones, can some mes be hard to read, but you can break up their parts with spaces like this:
space. To do that we'll use quotes to create a new text string. This time, enter
RTH EXPLORINGIf you don't know what format code to use, you can use Ctrl+1 > Number to format any cell the way you want. Then selec
=D3&", "&C3. The &", "& portion lets us join a comma and space with the text
re information on the web
in the cells.
about the TEXT function
mbine text and numbers
To create the full name, we'll join first and last name, but use a space without a
e Excel training online
comma. In F3, enter =C3&" "&D3.

Dive down for more detail

Using text and numbers together

Now we'll use the & to join text and numbers, not just text and text

Look at cells C28:D29. See how the date and times are in separate cells? You can join them
together with the & symbol like you'll see in cells C32:C33, but that doesn't look right, does
it? Unfortunately, Excel doesn't know how you want to format the numbers, so it breaks
them down to their basest format, which is the the Serial date in this case. We need to
explicity tell Excel how to format the number portion of the formula, so it displays the way
you want in the resulting text string. You can do that with the TEXT function and a format
code.

In cell C36, enter =C28&" "&TEXT(D28,"MM/DD/YYYY"). MM/DD/YYYY is the

US format code for Month/Day/Year, like 09/25/2017.

In cell C37, enter =C29&" "&TEXT(D29,"HH:MM AM/PM"). HH:MM AM/PM is

the US format code for Hours:Minutes AM or PM, like 1:30 PM.
 Previous Next

More information on the web

All about the TEXT function

Combine text and numbers

Free Excel training online

 First Name Last Name Last Name, First Name Full Name
Nancy Smith Smith, Nancy Nancy Smith
Andy North
Jan Kotas
Mariya Jones
Steven Thorpe
Michael Neipper
Robert Zare
Yvonne McKay
Minutes AM or PM, like 1:30 PM.
rts with spaces like this:=C28 & " " & TEXT(D28,"MM/DD/YYYY")
ell the way you want. Then select the Custom op on. You can copy the format code that's displayed back to your formula.

Using text & numbers

Today's date: 01/04/25
Current time: 5:10 AM

Joining text & numbers

Today's date: 45661
Current time: 45661.2152953704

Formatting text & numbers

CHECK THIS OUT
Formulas, especially big ones, can sometimes
Today's date: 01/04/2025
be hard to read, but you can break up their
Current time: 05:10 AM
parts with spaces like this:

=C28 & " " & TEXT(D28,"MM/DD/YYYY")

WORTH EXPLORING
If you don't know what format code to use, you can
use Ctrl+1 > Number to format any cell the way you
use Ctrl+1 > Number to format any cell the way you
want. Then select the Custom option. You can copy
the format code that's displayed back to your
formula.
 IF statements
IF statements
IF statements allow you to make logical comparisons between conditions. An IF statement generally says that if one condition
In cell D9
Copy D9 toIF statements allow you to make logical comparisons between conditions. An IF statement
Try anothergenerally says that if one condition is true do something, otherwise do something else. The
IMPORTANT formulas
DETAIL can return text, values, or even more calculations.
Dive down for more detail
Next
IF statement 1with another function
IF statements can also force addi onal calcula ons to be performed if a certain condi on is met. Here we're going to evaluate
In cell F33,
Next we've 2
Next, change
GOOD TO KNOW
EXPERT TIP 3
Previous
Next
More information on the web
All about the IF function
All about the IFS function
Advanced IF statements
Free Excel training online

IF statement with another function

IF statements can also force additional calculations to be performed if a certain condition is
met. Here we're going to evaluate a cell to see if Sales Tax should be charged, and calculate
it if the condition is true.

3
 3

More information on the web

 IF statements
atements allow you to make logical comparisons between conditions. An IF statement generally says that if one condition is true do some

IF statements allow you to make logical comparisons between conditions. An IF statement

generally says that if one condition is true do something, otherwise do something else. The
formulas
ORTANT can return
DETAILTRUE text,are
and FALSE values,
unlikeorother
evenwords
moreincalculations.
Excel formulas in that they don't need to be in quotes, and Excel will automa ca
e down for more detail

In cell D9
atement with another enter =IF(C9="Apple",TRUE,FALSE). The correct answer is TRUE.
function
atements can also force addi onal calcula ons to be performed if a certain condi on is met. Here we're going to evaluate a cell to see if S

Copy D9 to D10. The answer here should be FALSE, because an orange is not an
apple.
OD TO KNOWWhen you create a formula, Excel will automa cally place colored borders around any ranges referenced in the formula, and
Try another example by looking at the formula in cell D12. We got you started
with =IF(C12<100,"Less than 100","Greater than or equal to 100"). What
happens if you enter a number greater than or equal to
re information on 100
theinweb
cell C12?
about the IF function
about the IFS function
Dive down for more detail
anced IF statements Next
e Excel training online

IF statement with another function

IF statements can also force additional calculations to be performed if a certain condition is
met. Here we're going to evaluate a cell to see if Sales Tax should be charged, and calculate
it if the condition is true.

In cell F33, we've entered =IF(E33="Yes",F31*SalesTax,0), where we set up

SalesTax as a Named Range with a value of 0.0825. Our formula says If cell E33
equals Yes, then multiply cell F31 times SalesTax, otherwise return a 0.

Try changing Yes to No in cell E33 to see the calculation change.

Next we've added an IF statement to calculate shipping if it's required. In cell F35
you'll see =IF(E35="Yes",SUM(D28:D29)*1.25,0). This says "If cell E35 is Yes,
then take the sum of the Quantity column in the table above, and multiply it by
1.25, otherwise return a 0".

Next, change the 1.25 in the formula in cell F35 to "Shipping". As you start
typing, Excel's auto-correct, should find it for you. When it does, press Tab to
 typing, Excel's auto-correct, should find it for you. When it does, press Tab to
enter it. This is a Named Range, and we entered it from Formulas > Define
Name. Now, if you ever need to change your shipping cost, you only have to do
it in one place, and you can use the Shipping name anywhere in the workbook.

More information on the web

All about the IF function

All about the IFS function

Advanced IF statements

Free Excel training online

at if one condition is true do something, otherwise do something else. The formulas can return text, values, or even more calculations.

quotes, and Excel will automa cally capitalize them. Numbers don't need to be in quotes either. Regular text, like Yes or No does need to b

IF statements
Apple
Orange

50 Less than 100

es referenced in the formula, and the corresponding ranges in the formula will be the same color. You can see this if you select cell F33 and
IMPORTANT DETAIL
TRUE and FALSE are unlike other words in Excel formulas in
that they don't need to be in quotes, and Excel will
automatically capitalize them. Numbers don't need to be in
quotes either. Regular text, like Yes or No does need to be
in quotes like this:
=IF(C3="Apple","Yes","No")

Item Quantity Cost Total

Widget 2 $9.76 $19.52
Doohickey 3 $3.42 $10.26

Sub-Total $13.18 $29.78

Sales Tax? Yes $2.46 GOOD TO KNOW

When you create a formula, Excel will au
Shipping? Yes $6.25 place colored borders around any range
the formula, and the corresponding rang
formula will be the same color. You can
Total $38.48
select cell F33 and press F2

EXPERT TIP
Named Ranges allow you to define terms or values in a
Named Ranges allow you to define terms or values in a
single place, and then reuse them throughout a
workbook. You can see all of the named ranges in this
workbook by going to Formulas > Name Manager. Click
here to learn more.
ven more calculations.

e Yes or No does need to be in quotes like this: =IF(C3="Apple","Yes","No")

s if you select cell F33 and press F2 to edit the formula.

te a formula, Excel will automatically

borders around any ranges referenced in
nd the corresponding ranges in the
the same color. You can see this if you
F2 to edit the formula.
VLOOKUP
VLOOKUP
VLOOKUP is one of the most widely used func ons in Excel (and one of our favorites too!). VLOOKUP lets you look up a value
=VLOOKUP(A1,B:C,2,FALSE)
What do you VLOOKUP
want to is one
look of the most widely used functions in Excel (and one of our favorites too!).
for?
If you find VLOOKUP
it, how many lets you look
columns up aright
to the valuedo in
youa want
column on athe
to get left, then returns information in
value?
another column to
Where do you want to look for it? the right if it finds a match. VLOOKUP says:
Do you want an exact, or approximate match?
In cell D22,
Now try for
EXPERIMENTTry selec ng different items from the drop down lists. You'll see the result cells instantly update themselves with
Dive down for more detail
Next
VLOOKUP and #N/A
Invariably, you'll run into a situa on where VLOOKUP can't find what you asked it to, and it returns an error (#N/A). Some me
If you know
If you're not
IMPORTANT DETAIL
Previous
Next
More information on the web
1
All about the VLOOKUP function
All about the INDEX/MATCH functions
All about the IFERROR function
Use PivotTables to analyze worksheet data
2 online
Free Excel training

VLOOKUP and #N/A

Invariably, you'll run into a situation where VLOOKUP can't find what you asked it to, and it
returns an error (
can because the reference cell doesn't have a value yet.

1
 2

More information on the web

 VLOOKUP
OKUP is one of the most widely used func ons in Excel (and one of our favorites too!). VLOOKUP lets you look up a value in a column on t
OOKUP(A1,B:C,2,FALSE)
VLOOKUP
at do you want to is one
look of the most widely used functions in Excel (and one of our favorites too!).
for?
ou find VLOOKUP
it, how many lets you look
columns up aright
to the valuedo in
youa want
column on athe
to get left, then returns information in
value?
another column to
ere do you want to look for it?the right if it finds a match. VLOOKUP says:
you want an exact, or approximate match?
What do you If you find it, how many
want to look columns to the right do you
for? want to get a value?
Try selec ng different items from the drop down lists. You'll see the result cells instantly update themselves with new values.
e down for more detail

OKUP and #N/A=VLOOKUP(A1,B:C,2,FALSE)

riably, you'll run into a situa on where VLOOKUP can't find what you asked it to, and it returns an error (#N/A). Some mes, it's because th

Where do you Do you want

ORTANT DETAILIFERROR is what's known as a blanket
want to error
look handler,anmeaning
exact, orit will suppress any error your formula might throw. This can
for it? approximate
match?
re information on the web
about the VLOOKUP function
In cell D22, enter =VLOOKUP(C22,C17:D20,2,FALSE). The correct answer for
about the INDEX/MATCH functions
Apples is 50. VLOOKUP looked for Apples, found it, then went over one column
about the IFERROR function
to the right, and returned the amount.
PivotTables to analyze worksheet data
Now try for yourself in the Meat section, in cell G22. You should end up with
e Excel training online
=VLOOKUP(F22,F17:G20,2,FALSE).

Dive down for more detail Next

VLOOKUP and #N/A If the SUM function in cell D42 could talk,

Invariably, you'll run into a situation where VLOOKUP can't find what you asked it to, and it
returns an error (#N/A). Sometimes, it's because the lookup value simply doesn't exist, or it
can because the reference cell doesn't have a value yet.

If you know your lookup value exists, but want to hide the error if the lookup cell is
blank, you can use an IF statement. In this case, we'll wrap our existing VLOOKUP
formula like this in cell D43:

=IF(C43="","",VLOOKUP(C43,C37:D41,2,FALSE))
 =IF(C43="","",VLOOKUP(C43,C37:D41,2,FALSE))

This says, "If cell C43 equals nothing (""), then return nothing, otherwise return the
VLOOKUP's results". Note the second closing parenthesis at the end of the
formula. This closes the IF statement.

If you're not sure your lookup value exists, but you still want to suppress the
#N/A error, you can use an error handling function called IFERROR in cell G43:
=IFERROR(VLOOKUP(F43,F37:G41,2,FALSE),""). IFERROR says, "If the
VLOOKUP returns a valid result, then display that, otherwise, display nothing
("")". We displayed nothing here (""), but you can also use numbers (0,1, 2, etc.),
or text, such as "Formula isn't correct".

More information on the web

All about the VLOOKUP function

All about the INDEX/MATCH functions

All about the IFERROR function

Use PivotTables to analyze worksheet data

Free Excel training online

u look up a value in a column on the le , then returns informa on in another column to the right if it finds a match. VLOOKUP says:

themselves with new values.

#N/A). Some mes, it's because the lookup value simply doesn't exist, or it can because the reference cell doesn't have a value yet.

Fruit Amount Meat Amount

Apples 50 Beef 50
Oranges 20 Chicken 30
Bananas 60 Pork 10
Lemons 40 Fish 50

Apples Pork

EXPERIMENT
Try selecting different items from the drop down lists. You'll see
the result cells instantly update themselves with new values.

M function in cell D42 could talk, it would say this: Sum up the values in cells D38, D39, D40, and D41.
Item Amount Item Amount
Bread 50 Bread 50
Donuts 100 Donuts 100
Cookies 40 Cookies 40
Cakes 50 Cakes 50
Pies 20 Pies 20

#N/A Pastry
IMPORTANT DETAIL
IFERROR is what's known as a blanket error handler, meaning it
will suppress any error your formula might throw. This can
cause problems if Excel is giving you a notification that your
formula has a legitimate error that needs to be fixed.

A rule of thumb is to not add error handlers to your formulas

until you're absolutely certain they work properly.
ds a match. VLOOKUP says:

ll doesn't have a value yet.

 Conditional functions
Conditional functions - SUMIF
Conditional functions let you sum, average, count or get the min or max of a range based on a given condition, or criteria you
SUMIF lets you sum in one range based on a specifc criteria you look for in another range, like how many Apples you have. Se
Conditional functions let you sum, average, count or get the min or max of a range based
=SUMIF(C3:C14,C17,D3:D4)
What range ondoa you
given condition,
want or criteria you specify. Such
to look at?
many are apples? Or, how many oranges are the Florida type?
What value (text or number) do you want to look for?
For each match found, what range do you want to sum in?
SUMIFS is the 1 same as SUMIF, but it lets you use mul ple criteria. So in this example, you can look for Fruit and Type, instead
=SUMIFS(H3:H14,F3:F14,F17,G3:G14,G17)
What range do you want to sum?
This is the first range to look in for matches
This is the criteria for the first match
This is the
This is the criteria for the second match
EXPERT TIP
Dive down for more detail
Next step
Conditional functions - COUNTIF
COUNTIF and COUNTIFS let you count values in a range based on a criteria you specify. They're a bit different from the other
Select cell D64 and type =COUNTIF(C50:C61,C64). COUNTIF is structured like this:
=COUNTIF(C50:C61,C64)
What range do you want to look at?
What value 2 (text or number) do you want to look for?
COUNTIFS is
=COUNTIFS(F50:F61,F64,G50:G61,G64)
This is the first range to count
This is the criteria for the first match
This is the second range to count
This is criteria for the second match
Dive down for more detail
Next
More conditional functions
You've
=SUMIFS(H3:H14,F3:F14,F17,G3:G14,G17)
Dive down for more detail
Next
SUMIF with a value argument
Here's an example of the SUMIF function using greater than (>) to find all values greater than a given amount:
200
Sum up some values based on this criterion:
....Look through these cells...
...and if the value is greater than 50, sum it up.
NOTE: If you find you are making a lot of conditional formulas, you might find that a PivotTable is a better solution. See this P
More information on the web
All about the SUMIF function
All about the SUMIFS function
All about the COUNTIF function
All about the COUNTIFS function
Conditional functions
All about the AVERAGEIF function
All about the AVERAGEIFS function
All about the MINIFS function
COUNTIF
All about the MAXIFS function
They're
Create a drop-down list
range, and criteria. They don't evalute one range, then look in another to summarize.
Free Excel training online
Back to top
Next
1

=COUNTIFS(F50:F61,F64,G50:G61,G64)

More conditional functions

More conditional functions
You've already seen SUMIF, SUMIFS, COUNTIF, and COUNTIFS. Now you can try on your
own with the other functions, such as
structured the same way, so once you get one formula written, you can just replace the
function name with the one you want. We've written all the functions you'll need for cell
E106, so you can copy/paste these, or try to type them yourself for practice.

SUMIF
SUMIFS
AVERAGEIF
AVERAGEIFS
COUNTIF
COUNTIFS
MAXIFS
MINIFS

SUMIF with a value argument

Here's
than a given amount:

Sum up some
values based
on this
criterion:

=SUMIF(D118:D122,">=50")

NOTE:
PivotTable is a better solution.

More information on the web

 Conditional functions - SUMIF
ditional functions - SUMIF
ditional functions let you sum, average, count or get the min or max of a range based on a given condition, or criteria you specify. Such as
MIF lets you sum in one range based on a specifc criteria you look for in another range, like how many Apples you have. Select cell D17 and
Conditional functions let you sum, average, count or get the min or max of a range based
MIF(C3:C14,C17,D3:D4)
at rangeondoa you
given condition,
want or criteria you specify. Such as, out of all the fruits in the list, how
to look at?
many are apples? Or, how many oranges are the Florida type?
at value (text or number) do you want to look for?
each match found, what range do you want to sum in?
MIFS is the sameSUMIF letsbut
as SUMIF, you sumyou
it lets in one range
use mul plebased onSo
criteria. a specifc criteria you
in this example, you look for for
can look in Fruit and Type, instead of just by Fruit. S
another range, like how many Apples you have. Select cell D17 and type
MIFS(H3:H14,F3:F14,F17,G3:G14,G17)
at range do you=SUMIF(C3:C14,C17,D3:D14).
want to sum? SUMIF is structured like this:
is the first range to look in for matches
is the criteria for the first matchWhat range do you For each match found, what
want to look at? range do you want to sum?
is the criteria for the second match

=SUMIF(C3:C14,C17,D3:D4)
e down for more detail

ditional functions - COUNTIF

UNTIF and COUNTIFS let you count values in aWhat
rangevalue
based on or
(text a criteria
number) you specify. They're a bit different from the other IF and IFS functio
ct cell D64 and type =COUNTIF(C50:C61,C64). doCOUNTIF
you wantistostructured
look for? like this:
UNTIF(C50:C61,C64)
at range do you want to look at?
at value (text orSUMIFS
number)isdothe
yousame
wantas
toSUMIF, but it lets you use multiple criteria. So in this
look for?
example, you can look for Fruit and Type, instead of just by Fruit. Select cell H17
and type =SUMIFS(H3:H14,F3:F14,F17,G3:G14,G17). SUMIFS is structured like
UNTIFS(F50:F61,F64,G50:G61,G64)
this:
is the first range to count
is the criteria for the first What
matchrange This is the This is the
is the second range to count do you want criteria for the criteria for the
is criteria for the second match
to sum? first match second match
e down for more detail

re conditional functions

=SUMIFS(H3:H14,F3:F14,F17,G3:G14,G17)
e down for more detail

MIF with a value argument

This isthan
e's an example of the SUMIF function using greater the (>)
firstto find all values greater
This is the
than a given amount:
range to look second range
m up some values based on this criterion: in for matches to look in for
ook through these cells... matches
nd if the value is greater than 50, sum it up.
TE: If you find you are making a lot of conditional formulas, you might find that a PivotTable is a better solution. See this PivotTable article
re information onDive down for more detail
the web Next
about the SUMIF function
about the SUMIFS function
about the COUNTIF function
about the COUNTIFS function
Conditional functions - COUNTIF
about the AVERAGEIF function
about the AVERAGEIFS function
about the MINIFS function
COUNTIF and COUNTIFS let you count values in a range based on a criteria you specify.
about the MAXIFS function
They're a bit different from the other IF and IFS functions, in that they only have a criteria
ate a drop-down list
range, and criteria. They don't evalute one range, then look in another to summarize.
e Excel training online

Select cell D64 and type =COUNTIF(C50:C61,C64). COUNTIF is structured like

this:
What range do you
want to look at?

=COUNTIF(C50:C61,C64)

What value (text or number)

do you want to look for?

COUNTIFS is the same as SUMIF, but it lets you use multiple criteria. So in this
example, you can look for Fruit and Type, instead of just by Fruit. Select cell H64
and type =COUNTIFS(F50:F61,F64,G50:G61,G64). COUNTIFS is structured like
this:

This is the first This is the second

range to count range to count

=COUNTIFS(F50:F61,F64,G50:G61,G64)

This is the criteria This is criteria for

for the first match the second match

Dive down for more detail Next

More conditional functions

More conditional functions
You've already seen SUMIF, SUMIFS, COUNTIF, and COUNTIFS. Now you can try on your
own with the other functions, such as AVERAGEIF/S, MAXIFS, MINIFS. They're all
structured the same way, so once you get one formula written, you can just replace the
function name with the one you want. We've written all the functions you'll need for cell
E106, so you can copy/paste these, or try to type them yourself for practice.

SUMIF =SUMIF(C92:C103,C106,E92:E103)
SUMIFS =SUMIFS(E92:E103,C92:C103,C106,D92:D103,D106)
AVERAGEIF =AVERAGEIF(C92:C103,C106,E92:E103)
AVERAGEIFS =AVERAGEIFS(E92:E103,C92:C103,C106,D92:D92,D106)
COUNTIF =COUNTIF(C92:C103,C106)
COUNTIFS =COUNTIFS(C92:C103,C106,D92:D103,D106)
MAXIFS =MAXIFS(E92:E103,C92:C103,C106,D92:D103,D106)
MINIFS =MINIFS(E92:E103,C92:C103,C106,D92:D103,D106)

Dive down for more detail

SUMIF with a value argument

Here's an example of the SUMIF function using greater than (>) to find all values greater
than a given amount:

Sum up some ....Look through ...and if the

values based these cells... value is greater
on this than 50, sum it
criterion: up.

=SUMIF(D118:D122,">=50")

NOTE: If you find you are making a lot of conditional formulas, you might find that a
PivotTable is a better solution. See this PivotTable article for more information.

More information on the web

All about the SUMIF function All about the SUMIFS function
All about the SUMIF function All about the SUMIFS function

All about the COUNTIF function All about the COUNTIFS function

All about the AVERAGEIF function All about the AVERAGEIFS function

All about the MINIFS function All about the MAXIFS function

Create a drop-down list Free Excel training online

Back to top Next step

 Fruit Amount Fruit Type Amount
Apples 50 Apples Fuji 50
Oranges 20 Oranges Florida 20
Bananas 60 Bananas Cavendish 60
Lemons 40 Lemons Rough 40
Apples 50 Apples Honeycrisp 50
Oranges 20 Oranges Navel 20
Bananas 60 Bananas Lady Finger 60
Lemons 40 Lemons Eureka 40
Apples 50 Apples Honeycrisp 50
Oranges 20 Oranges Navel 20
Bananas 60 Bananas Cavendish 60
Lemons 40 Lemons Eureka 40

Fruit SUMIF Fruit Type SUMIFS

Apples Oranges Florida

t from the other IF and IFS functions, in that they only have a criteria range, and criteria. They don't evalute
EXPERT TIP one range, then look in anoth
Each one of the Fruit and Type cells has a
drop-down list where you can select
different fruits. Try it, and watch the
formulas automatically update.

lution. See this PivotTable article for more information.

Fruit Amount Fruit Type Amount
Apples 50 Apples Fuji 50
Oranges 20 Oranges Florida 20
Bananas 60 Bananas Cavendish 60
Lemons 40 Lemons Rough 40
Apples 50 Apples Honeycrisp 50
Oranges 20 Oranges Navel 20
Bananas 60 Bananas Lady Finger 60
Lemons 40 Lemons Eureka 40
Apples 50 Apples Honeycrisp 50
Oranges 20 Oranges Navel 20
Bananas 60 Bananas Cavendish 60
Lemons 40 Lemons Eureka 40

Fruit COUNTIF Fruit Type COUNTIFS

Apples 3 Oranges Florida 1
Fruit Type Amount
Apples Fuji 50
Oranges Florida 20
Bananas Cavendish 60
Lemons Rough 40
Apples Honeycrisp 50
Oranges Navel 20
Bananas Lady Finger 60
Lemons Eureka 40
Apples Honeycrisp 50
Oranges Navel 20
Bananas Cavendish 60
Lemons Eureka 40

Fruit Type Try it

Lemons Eureka 80

Item Amount
Bread 50
Donuts 100
Cookies 40
Cakes 50
Pies 20
200
ange, then look in another to summarize.
uit and Type cells has a
ere you can select
y it, and watch the
ically update.
GOOD TO KNOW
Double-click this cell and you'll see that the formula is
different. Specifically, the sum criteria is ">=50" which
means greater than or equal to 50. There are other
operators you can use like "<=50" which is less than or
equal to 50. And there's "<>50" which is not equals
50.
 Let the Function Wizard guide you
Let the Function Wizard guide you
If you know the name of the func on you want, but you're not sure how to build it, you can use the Func on Wizard to help y
Select cell
Next, enterIf you know the name of the function you want, but you're not sure how to build it, you
CHECK THIS can use the Function Wizard to help you out.
GOOD TO KNOW
GOOD TO 1
Previous
Next
More information on the web
Overview of formulas in Excel
Excel functions2 (by category)
Excel functions (alphabetical)
Free Excel training online

More information on the web

 Let the Function Wizard guide you
he Function Wizard guide you
u know the name of the func on you want, but you're not sure how to build it, you can use the Func on Wizard to help you out.

If you know the name of the function you want, but you're not sure how to build it, you
can use the Function Wizard to help you out.
OD TO KNOWYou can type cell and range references, or select them with your mouse.
Select cell D10, then go to Formulas > Insert Function > type VLOOKUP in
the Search for a function box, and press GO. When you see VLOOKUP
highlighted, click OK at the bottom. When you select a function in the list,
e information onExcel will display its syntax.
the web
rview of formulas in Excel
Next, enter the function arguments in their respective text boxes. As you enter
l functions (by category)
each one, Excel will evaluate it, and show you its result, with the final result at
l functions (alphabetical)
the bottom. Press OK when you're done, and Excel will enter the formula for
Excel training online
you.

More information on the web

Overview of formulas in Excel

Excel functions (by category)

Excel functions (alphabetical)

Excel functions (alphabetical)

Free Excel training online

Wizard to help you out.

Fruit Amount
Apples 50
Oranges 20
Bananas 60
Lemons 40

Apples 50

CHECK THIS OUT

You should end up with
=VLOOKUP(C10,C5:D8,2,FALSE)

GOOD TO KNOW
You can type cell and range references, or select them
with your mouse.

GOOD TO KNOW
As you enter each argument's section, the argument's
description will be displayed toward the bottom of the form,
above the Formula result.
 Fixing formula errors
Fixing formula errors
At some point in me, you'll run across a formula that has an error, which Excel will display with #ErrorName. Errors can be he
Error checking - Go to Formulas > Error Checking. This will load a dialog that will tell you the general cause for your specific er
If you clickAt some
Help point
on this in time,
Error, a helpyou'll
topic run across
specific a formula
to the that has
error message anopen.
will error,If which Excel
you click willCalcula
Show displayon Steps, an Evalu
Each me with #ErrorName.
you click Errors
Evaluate, Excel can
will bethrough
step helpful,the
because
formulathey
onepoint
sec onout
at when
a me.something's not tell you why an err
It won't necessarily
working right, but they can be challenging to fix.
EXPERIMENTWhat's wrong here? Hint: We're trying to SUM up all the items.Fortunately, there are several options that
GOOD TO can help you track down the source of the error, and fix it.
Previous
Next 1
More information on the web
Detect errors in formulas
How to avoid broken formulas
Evaluate a nested formula one step at a time
Free Excel training online

3
More information on the web
 Fixing formula errors
ng formula errors
ome point in me, you'll run across a formula that has an error, which Excel will display with #ErrorName. Errors can be helpful, because th
r checking - Go to Formulas > Error Checking. This will load a dialog that will tell you the general cause for your specific error. In cell D9, th
u clickAt some
Help point
on this in time,
Error, a helpyou'll
topic run across
specific a formula
to the that has
error message anopen.
will error,If which Excel
you click willCalcula
Show displayon Steps, an Evaluate Formula dial
h me with #ErrorName.
you click Errors
Evaluate, Excel can
will bethrough
step helpful,the
because
formulathey
onepoint
sec onout
at when
a me.something's not tell you why an error occurs, but it
It won't necessarily
working right, but they can be challenging to fix. Fortunately,
What's wrong here? Hint: We're trying to SUM up all the items. there are several options that
can help you track down the source of the error, and fix it.

1 Error checking - Go to Formulas > Error Checking. This will load a dialog that
will tell you the general cause for your specific error. In cell D9, the #N/A error is
e information on the web
caused because there is no value matching "Apple". You can fix this by using a
ect errors in formulas
value that does exist, suppress the error with IFERROR, or ignore it and know it
w to avoid broken formulas
will go away when you do use a value that exists.
uate a nested formula one step at a time
Excel training online

2 If you click Help on this Error, a help topic specific to the error message will
open. If you click Show Calculation Steps, an Evaluate Formula dialog will load.

3 Each time you click Evaluate, Excel will step through the formula one section at a
time. It won't necessarily tell you why an error occurs, but it will point out where.
From there, look at the help topic to deduce what went wrong with your formula.
 time. It won't necessarily tell you why an error occurs, but it will point out where.
From there, look at the help topic to deduce what went wrong with your formula.

More information on the web

Detect errors in formulas

How to avoid broken formulas

Evaluate a nested formula one step at a time

Free Excel training online

Errors can be helpful, because they point out when something's not working right, but they can be challenging to fix. Fortunately, there a
Fruit Amount
Apples 50
Oranges 20
Bananas 60
Lemons 40

Apple #N/A

GOOD TO KNOW
Clicking Options will let you set the
rules for when errors in Excel are
displayed or ignored.

Fruit Amount
Apples 50
Oranges 20
Bananas 60
Lemons 40

Total #NAME?

EXPERIMENT
What's wrong here? Hint: We're trying to SUM
up all the items.
allenging to fix. Fortunately, there are several op ons that can help you track down the source of the error, and fix it.
or, and fix it.
More questions about Excel?
Press ALT+Q and type what you want to know.
Keep going. There is more to learn with Excel:
LinkedIn Learning: Video courses for all levels—from beginner to advanced. Take at your own pace.
Community: Ask ques ons and connect with other Excel fans.
What
Give us feedback on this tour
More questions about Excel?
Press ALT+Q and type what you want to know.
More questions about Excel?
Keep going. There is more to learn with Excel:
LinkedIn Learning: Video courses for all levels—from beginner to advanced. Take at your own pace.
Community: Ask ques ons and connect with other Excel fans.

Give us feedback on this tour

Click the Tell Me button and type what you want to know.

Keep going. There is more to learn with Excel:

LinkedIn Learning Community

Video courses for all Ask questions and
levels—from beginner to connect with other
advanced. Take at your Excel fans.
own pace.
Learn more Learn more

Give us feedback
 What else is new?
Office 365 subscribers
get continual updates
and new features.

Learn more

ve us feedback on this tour