Business Data Analytics & Advanced Excel · Beginner level

Beginner level · Module 2: Excel Analysis Functions and Charts · Lesson 1 of 3

Conditional Totals and Counts

Answer 'how much' and 'how many' questions by branch, product and month with conditional functions.

In this lesson you will

  • Use SUMIFS, COUNTIFS and AVERAGEIFS
  • Build a summary table driven by criteria cells

Key concepts

SUMIFSCOUNTIFSAVERAGEIFScriteria cells

The lesson

SUMIFS totals values meeting several conditions, COUNTIFS counts records and AVERAGEIFS averages them: =SUMIFS(Sales[Amount], Sales[Branch], A2, Sales[Month], B1).

Put branches down the side and months across the top, and point the criteria at those cells. One formula, copied across the table, fills the whole summary.

Check summaries: the table's grand total must equal the total of all data.

Step by step

  1. Total sales by branch with SUMIFS.
  2. Build a branch-by-month table.
  3. Count transactions by product.
  4. Check the grand total.

Practice activity

Build the quarter's summary tables.

Expected result: Summary tables whose totals reconcile with the data.

Check your understanding

  1. Which function counts records meeting conditions?

    Show answer

    COUNTIFS.

  2. How do you check a summary table?

    Show answer

    Its grand total must equal the total of all the data.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.