Conditional Aggregation and First KPIs
Swipe to show menu
The workbook now contains transaction data, Revenue calculations, and performance classifications. In this chapter, build the first KPI summary table using conditional aggregation formulas.
SUMIFS Structure
=SUMIFS(sum_range, criteria_range1, criteria1)
Formula used in this chapter:
=SUMIFS(Sales_Data!H:H, Sales_Data!E:E, A2)
Sales_Data!H:H: values being summed;Sales_Data!E:E: criteria range;A2: condition being evaluated.
COUNTIFS Structure
=COUNTIFS(criteria_range1, criteria1)
Formula used in this chapter:
=COUNTIFS(Sales_Data!E:E, A2)
Sales_Data!E:E: criteria range;A2: condition being evaluated.
Step 1 Create the Summary Sheet
Create a new worksheet named:
Summary
Step 2 Build the KPI Table
Inside row 1, create the following headers:
| Cell | Value |
|---|---|
| A1 | Region |
| B1 | Total Revenue |
| C1 | Order Count |
Inside A2:A5, add:
North
South
East
West
Step 3 Calculate Total Revenue by Region
Inside B2, type:
=SUMIFS(Sales_Data!H:H, Sales_Data!E:E, A2)
Press Enter and copy the formula down to B5.
Format the results using the Accounting format.
Step 4 Count Orders by Region
Inside C2, type:
=COUNTIFS(Sales_Data!E:E, A2)
Press Enter and copy the formula down to C5.
Step 5 Format the Table
Apply basic formatting to improve readability:
- Bold the headers;
- Add borders;
- Apply a background color to the header row.
1. What is the main advantage of using SUMIFS instead of manually filtering and summing data?
2. What does Sales_Data!H:H represent?
3. What is COUNTIFS used for in this lesson?
Everything was clear?
Thanks for your feedback!
Section 1. Chapter 4
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
Section 1. Chapter 4
