Excel Lesson 13Using Powerful Excel Functions Microsoft Office 2010 Advanced Cable / Morrison
Objectives • Use the COUNT and COUNTA functions. • Utilize the COUNTBLANK function to count blank cells. • Use the Subtotal function with filtered data. • Create a named range. 2 2
Objectives (continued) • Use a named range in a function. • Use a SUMIF function. • Search for data using VLOOKUP. • Search for data using HLOOKUP.
Vocabulary • condition • criteria • function number • lookup table 4 4
Introduction • Excel offers powerful functions to count, sum, and locate specific information. • COUNT function • COUNTA function • COUNTBLANK function • Subtotal function • SUMIF function • VLOOKUP function • HLOOKUP function
Using COUNTA versus COUNT • To find the total number of cells that have numeric data, use the COUNT function. COUNT function counts numeric data
Using COUNTA versus COUNT (continued) • To count cells with text, use the COUNTA function. • The COUNTA function counts cells if they contain text or a combination of text and numbers. COUNTA function counts cells with text
Using COUNTBLANK • The COUNTBLANK function counts the number of cells that do not have any numbers or text entered in them. • You may want to count blank cells to see how many cells do not contain data.
Using COUNTBLANK (continued) • COUNTBLANK function counts cells without data
Counting Filtered Cells Using theSubtotal Function • The Subtotal function can be used to count, sum, or average filtered data. • The Subtotal function is =SUBTOTAL (function number, data range). • The function numberis a number that represents a calculation.
Counting Filtered Cells Using theSubtotal Function (continued) • SUBTOTAL function counts displayed cells
Creating Named Ranges • You can give arange a meaningful name. • Named ranges can be used in functions. • Especially helpful if you need to use a range in several functions • In a range name, use an underscore (_), rather than a space, to separate words. • Excel does not accept spaces in named ranges.
Creating Named Ranges (continued) • Name Box with name entered
Using a Named Range in a Function • Using named ranges in functions is convenient. • You can enter the range name instead of the cell names. • To select and use a named range: • Click the Name Box down arrow. • Select the named range that you want to include in a function.
Using a Named Range in a Function (continued) • Function with named range
Using the SUMIF Function • The SUMIF function will only total data that meets a certain condition. • A conditionis a requirement that needs to be met. • Criteriaare the conditions you are searching for in a data range. • The SUMIF function is =SUMIF(range,criteria,sum_range).
Using the SUMIF Function (continued) • Example of SUMIF Function
Creating a VLOOKUP Function • LOOKUP functions allow you to find data that already exists. • A lookup tableis the range of data you create in either a vertical or horizontal format, which Excel will use to match against the criteria. • The VLOOKUP function is used when the lookup table is listed vertically in columns.
Creating a VLOOKUP Function (continued) • Example of VLOOKUP function
Using the HLOOKUP Function • Use the HLOOKUP function when the data in the lookup table is listed horizontally in rows. • To use the HLOOKUP function, enter the following information into the Function Argument dialog box: • Lookup_value • Table_array • Row_index_num
Using the HLOOKUP Function (continued) • Example of HLOOKUP function
Using the HLOOKUP Function (continued) • Function Arguments for HLOOKUP dialog box
Summary In this lesson, you learned: • The COUNT function counts cells with numeric data and the COUNTA function counts cells with text or a combination of text and numbers. • You can use COUNTBLANK to count cells without any data.
Summary (continued) • The SUBTOTAL function can be used to count filtered data. • You can name a range of data. • A named range can be used in a function. • The SUMIF function totals data that meet a certain condition.
Summary (continued) • You can use the VLOOKUP function to find data that is displayed vertically in a lookup table. • You can use the HLOOKUP function to find data that is displayed horizontally in a lookup table.