Chapter 11

1. Chapter 11 Creating Formulas that Count and Sum Microsoft Excel 2003

2. Using Mathematical and Statistical Functions • Excel provides a collection of mathematical, trigonometric, and statistical functions, extending beyond the SUM, AVERAGE, MIN, MAX, and COUNT functions. • Counting formula – returns the number of cells in a specified range that meet certain criteria • Summing formula – returns a sum of the values of the cells in a range that meet certain criteria. • See Table 11-1 for a list of Excel’s Counting and Summing functions. • Use AutoCalculate feature on the toolbar to get a quick count or sum.

3. Basic Counting Formulas • To count the total number of cells, use the ROWS function and the COLUMNS function (uses a named range): =ROWS(Data)*COLUMNS(Data) • To count blank cells, use the COUNTBLANK function: • Also counts cells containing a formula that returns an empty string. • Does not count zero values =COUNTBLANK(Data)

4. Basic Counting Formulas • To count non-blank cells, use the COUNTA function • Counts cells that contain values, text, or logical values (true or false) =COUNTA(Data) • To count numeric cells, use the COUNT function • Counts cells that contain numeric values (includes dates and times) =COUNT(Data)

5. Basic Counting Formulas • To count nontext cells, use the ISNONTEXT function • Returns true if its argument refers to any nontext cell (including a blank cell) • The array formula below returns the count of the number of cells not containing text. {=SUM(IF(ISNONTEXT(Data),1))} • To count text cells, use the ISTEXT function • Returns true if its argument refers to any text cell • The array formula below returns the count of the number of cells that containing text. • {=SUM(IF(ISTEXT(Data),1))}

6. Basic Counting Formulas • To count logical values, use the ISLOGICAL function • Returns true if its argument refers to any logical values • The array formula below returns the count of the number of cells containing logical values (true or false). {=SUM(IF(ISLOGICAL(Data),1))} • To count error values in a range, use the ISERROR, ISERR, OR ISNA functions • See Pg 226-227

7. COUNTIF Function • Used for single-criterion counting formulas • 2 arguments • The range that contains the values that determine whether to include a particular cell in the count • The logical criteria that determine whether to include a particular cell in the count. • Can use constants, expressions, functions, cell references, and wildcard characters. • See Pg 228 for examples

8. Counting Cells using Multiple Criteria • Using AND criteria • Counts cells if all specified conditions are met. =COUNTIF(Data, “>0”) - COUNTIF(Data, “>12”) {=SUM((Data>0) * (Data<=12))} • To avoid using an array formula, use the SUMPRODUCT function =SUMPRODUCT((Month=“January”)*(Rep=“Jones”)*(Amount>1000))

9. Counting Cells using Multiple Criteria • Using OR criteria • Use multiple COUNTIF functions • Counts cells if any specified conditions are met. =COUNTIF(Data, 1) + COUNTIF(Data, 3) {=SUM(COUNTIF(Data, {1,3}))} • If you are basing your criteria on cells other than the ones being counted, use {=SUM(IF((Month=“January”)+(Rep=“Jones),1))} • Combining AND & OR Criteria {=SUM(IF((Month=“Jan”)+(Rep=“Jones)*(Amt>10),1))}

10. Other Counting Functions • To count the most frequently occurring entry, use the MODE function • Works only with numeric values =MODE(Data) • To count the most frequently occurring entry (text or values), use this formula that uses the MAX function {=MAX(COUNTIF(Data, Data))}

11. Other Counting Functions • To count occurrences of specific text in an entire cell (using named ranges) =COUNTIF(Data,Text) • Case-sensitive {=SUM(IF(EXACT(Data, Text),1))} • To count occurrences of specific text in partial cell contents =COUNTIF(Data,”*”&Text&”*”)

12. Frequency Distributions • A Frequency Distribution shows the frequency of each value in a range. • Use the FREQUENCY function • Create your own formulas • Use the Analysis ToolPak add-in. • FREQUENCY function • Returns an array so must use it in an array formula entered into a multicell range. • See Pg 236-237

13. Frequency Distributions • Using formulas • Use an array formula that counts the number of values that meet a specified criteria • See Pg 237-238 • Use the Analysis ToolPak • Use the Histogram option to create a frequency distribution. • See Pg 238-239

14. Summing Formulas • Summing all cells in a range • Using a named range =SUM(Data) • Using cell references for arguments =SUM(A1:A9, C1:C9, E1:E9) • Using complete rows or columns =SUM(A:A)

15. Cumulative Sums (Running Totals) • Use a mixed reference – the first cell in the range reference always refers to the same row. • When the formula is copied down the column, the range argument adjusts so that the sum always starts with row 2 and ends with the current row. =SUM(B$2:B2) • Can use the IF function to hide the cumulative sums for rows in which data has not been entered. =IF(b2<>””, SUM(B$2:B2), “”)

16. Conditional Sums • With a conditional sum, values in a range that meet one or more conditions are included in the sum. • Use the SUMIF function with its 3 arguments • the range containing the values that determine whether to include a particular cell in the sum. • The criteria using an expression that determines whether to include a particular cell in the sum. • The sum_range (optional) which contains the cells you want to sum (if omitted, uses the range of the first argument) =SUMIF(Data,”>0”)

17. Other Conditional Sum Formulas • Pg 243-244 – Summing only negative values • Pg 244 – Summing values based on a different range • Pg 245 – Summing values based on a text comparison • Pg 243-244 – Summing values based on a date comparison • Pgs 246-247 – Using AND & OR Criteria

18. The Conditional Sum Wizard • Excel ships with an add-in called the Conditional Sum Wizard. • Select Tools  Conditional Sum • Can specify various conditions for your summing • Wizard creates the formula for you (always an array formula) • Can use an AND condition but not an OR condition.