O'Reilly logo

Excel 2013 Formulas by John Walkenbach

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 7: Counting and Summing Techniques

In This Chapter

• Counting and summing cells

• Counting and summing records in databases and pivot tables

• Basic counting formulas

• Advanced counting formulas

• Formulas for common summing tasks

• Conditional summing formulas using a single criterion

• Conditional summing formulas using multiple criteria

• Using VBA for counting and summing tasks

Many of the most frequently asked spreadsheet questions involve counting and summing values and other worksheet elements. It seems that people are always looking for formulas to count or sum various items in a worksheet. If I've done my job, this chapter will answer the vast majority of such questions.

Counting and Summing Worksheet Cells

Generally, a counting formula returns the number of cells in a specified range that meet certain criteria. A summing formula returns the sum of the values of the cells in a range that meet certain criteria. The range that you want counted or summed may or may not consist of a worksheet database or table.

Table 7-1 lists the worksheet functions that come into play when creating counting and summing formulas. If none of the functions in Table 7-1 can solve your problem, an array formula can likely come to the rescue.

Table 7-1: Excel Counting and Summing Functions

Function

Description

AGGREGATE

A versatile function that can be used for counting and summing, with options to ignore hidden cells, error values, and nested SUBTOTAL or AGGREGATE functions. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required