Learn it fast with expert-taught software and skills training at lynda.com. Start your free trial

By Curt Frye | Tuesday, February 3, 2015

Summarize Data with Conditional Functions in Excel

Excel users are often faced with spreadsheets that summarize sales data for multiple areas, such as states within the U.S. or individual countries.

Functions such as SUM or AVERAGE let you summarize your data as a whole—but it can be difficult to find the totals, averages, or counts for subsets of that data. For example, suppose you want to find the total of all sales to Canada. To do that using a standard SUM formula, you would have to identify cells that contain values for all sales to Canada and then create a formula for just those cells.

Fortunately, there’s a set of conditional functions in Excel that let you specify which values should be included in a sum, average, or count calculation. Those functions are: SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF, and COUNTIFS.

Here’s how to take advantage of them:

By Curt Frye | Monday, October 13, 2014

Enhance Excel Lookup Using INDEX and MATCH

index_match05

Business users of Microsoft Excel take advantage of many of the program’s built-in functions. One of the most popular tools is the VLOOKUP function, which lets you search an Excel worksheet as if it were a database table.

By Scott Fegette | Wednesday, January 15, 2014

Excel’s VLOOKUP and HLOOKUP functions explained

Understanding HLOOKUP and VLOOKUP

Explore this course at lynda.com.

When looking up information in Microsoft Excel, you’ll regularly need to compare data against a table—and tables can be found in a variety of locations and formats. For example, in the image above you may need to find the appropriate tax rate in the table on the right for a given employee’s salary listed in the table on the left.

On the Formulas tab in the Excel ribbon, you’ll see a categories function called Look up and Reference. The two key functions for this type of task are VLOOKUP (V meaning vertical) and its companion function HLOOKUP (H meaning horizontal).

Why two functions instead of one? As shown in the image below, data tables can be found in horizontal and vertical orientations—so with two dedicated functions, you’re covered either way.

Get the latest news

  •   New course releases
  •   Pro tips and tricks
  •   News and updates
  
New releases submit clicked

You can change your email preferences at any time. We will never sell your email. More info

Featured articles

A lynda.com membership includes:

Unlimited access to our subscription library courses
Certificates of completion
New courses added every week (almost every day!)
Course history to track your progress
Downloadable practice files
Playlists and bookmarks to organize your learning
Start your free trial

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.