• BUS 115 - Business Applications: Excel in Practice
  • Acknowledgements
  • Excel Overview
  • Time Saver
  • Auto Fill and Fill Series
  • Cell Referencing
  • Absolute and Relative Referencing
  • Navigation Shortcuts
  • Managing Cells
  • Sorting and Filtering
  • Hiding and Grouping
  • Paste Special
  • Named Ranges
  • Ranking
  • Rounding
  • Sum and Count
  • Managing Worksheets
  • Lookup Functions
  • Data Validation
  • IF Statements
  • Formula Errors
  • Text Functions
  • Text to Columns
  • Logic Functions 2
  • Date and Time
  • Conditional Formatting 1
  • Standards of Professionalism
  • Spreadsheet Formatting and Design
  • Basic Charts
  • Dashboards
  • Conditional Formatting 2
  • Spreadsheet Protection
  • Basic Pivot Tables
  • Advanced Pivot Tables and Slicers
  • Logic Functions 3
  • Advanced Lookup Functions
  • Loan Amortization Schedule
  • Advanced Charts
  • Macros 1
  • Macros 2
  • Macros 3
  • How to Use Search Engines
  • Sandbox
  • Author Biographies
  • Why the Standards of Professionalism?
  • Learning New Functions
  • Download
  • Translations
  • Sum and Count

    In this chapter, you will learn how to summarize data with the sum and count functions. We have a table of data of some employees at a business. We have the name of the employee, their position, age, college GPA, and current salary. (See Figure 6.1)

    BUS115_image_6.1.jpg
    Figure 6.1
     

    Practice Spreadsheet

    Use this workbook for the chapter.

    Note: This is the same workbook used in the Managing Worksheets chapter.

    Sum Function

    Let’s look at the salary column. Let’s suppose that we want to calculate the sum total of all the employees’ salaries. We can do this with the Sum function.

    1. Select an empty cell and type an equals symbol ( = ) to start the formula.
    2. Type sum and an open (left) parentheses ( ( ).
    3. Select all of the data to sum (F3 to F17 in Figure 6.2).
    4. Type a close (right) parenthesis ( ) ) to close the formula data reference.
    5. Press Enter to complete the cell’s formula (=sum(F3:F17)).
      1. The total comes to $749,000.
      2. If one of the salaries changes, then the sum total will automatically reflect that change. For example, if the F3 cell’s data changes from $36,000 to $38,000, the sum total will reflect the change: $751,000.
        BUS115_image_6.2.jpg
        Figure 6.2
         

    Count Function

    Let’s suppose that we want to count the number of salaries in the list. We can do it with the Count function. The Count function counts numerical data only; it will not recognize text.

    1. Select an empty cell and type an equals symbol ( = ) to start the formula.
    2. Type count and an open (left) parenthesis ( ( ).
    3. Select all of the data to count (F3 to F17 in Figure 6.3).
    4. Type a close (right) parenthesis ( ) ) to close the formula data reference.
    5. Press Enter to complete the cell’s formula (=count(F3:F17)).
      1. The total count of salaries in Figure 6.3 is 15.
      2. If one or more of the counted data is deleted, the count will recognize that change. For example, if the salary data for Carl in cell F5 is deleted, the total count will change from 15 to 14.
        BUS115_image_6.3.jpg
        Figure 6.3
         

    CountA Function

    Another count function is the CountA Function. This function works the same as the Count function, except it is used to count non-numerical data—textual data such as names or positions.

    1. Select an empty cell and type an equals symbol ( = ) to start the formula.
    2. Type counta and an open (left) parenthesis ( ( ).
    3. Select all of the data to count (B3 to B17 in Figure 6.4).
    4. Type a close (right) parenthesis ( ) ) to close the formula data reference.
    5. Press Enter to complete the cell’s formula (=counta(B3:B17)).
      1. The total count of employees is 15.
      2. If another CountA is inserted for the Positions column, the total count of positions comes to 11. The difference between the results reveals 4 employees do not have positions.
        BUS115_image_6.4.jpg
        Figure 6.5
         

    CountBlank Function

    The CountBlank function can tell us the number of blank cells in a data set. In this case, we can verify the number of employees without positions from the CountA function example.

    1. Select an empty cell and type an equals symbol ( = ) to start the formula.
    2. Type countblank and an open (left) parenthesis ( ( ).
    3. Select all of the data to count (C3 to C17 in Figure 6.5).
    4. Type a close (right) parenthesis ( ) ) to close the formula data reference.
    5. Press Enter to complete the cell’s formula (=countblank(C3:C17)).
      1. The total count of blank cells is 4 in the Positions data set.
        BUS115_image_6.5.jpg
        Figure 6.5
         

    Supplemental Resource

    This content is provided to you freely by BYU-I Books.

    Access it online or download it at https://books.byui.edu/bus_115_business_app/sum_and_count.