Topic 02

Advanced Formulas

Move from simple calculations to conditional ones. COUNTIF and SUMIF let you ask Excel a question with a condition — count or sum only the rows that match. By the end of this topic you'll build a full staff summary and complete a two-file lab.

2 functions Training file: StaffData.xlsx Lab file: profit.xlsx

Training File — StaffData.xlsx

Download StaffData.xlsx

Step 1 — The Table Check

Open StaffData.xlsx. Before writing a single formula — do we already have a Table here?

👀

What to look for

A proper Excel Table has alternating row colours, filter arrows on headers, and the Table Design tab appearing when you click inside the data. If none of these are visible, it's just a plain range — convert it.

The file contains 741 staff members. Convert it to a Table and name it staff:

Click anywhere inside the data range.

Press Ctrl + T → confirm range → check "My table has headers" → OK.

Table Design tab → Table Name → type staff → Enter.

Step 2 — Meet COUNTIF & SUMIF

Both functions add a condition to what you already know — count or sum only the rows that match.

COUNTIF Count rows that match a condition

Takes a range to search and a condition to match. Returns how many cells satisfy it.

= COUNTIF(range, criteria)

rangeThe column to search — staff[Building]

criteriaWhat to match — text in quotes "Al Gharrafa", or a cell reference A2 to copy the formula down.

Example — count staff in Al Gharrafa (via cell reference):

= COUNTIF(staff[Building], A2)

SUMIF Sum rows that match a condition

Same idea as COUNTIF but adds a third argument — the column to sum when the condition is met.

= SUMIF(range, criteria, sum_range)

rangeColumn to check — staff[Building]

criteriaValue to match — building name or cell reference.

sum_rangeColumn to add up — staff[Salary]

Example — total salary for the building in A2:

= SUMIF(staff[Building], A2, staff[Salary])

💡

Key difference

COUNTIF has 2 arguments — it only needs to know what to count. SUMIF has 3 — it also needs to know what to add.

Step 3 — Build the Building Summary

Create a summary next to the staff data. List the 6 buildings, then use COUNTIF and SUMIF to fill in headcount and total salary for each. Enter your results below to check.

📌

Lock the range, not the criteria

Table column references like staff[Building] stay fixed automatically when copied. Only the criteria cell (A2) needs to move — no dollar signs needed on those arguments.

Building Summary — Your Results Numbers only, no commas needed

Building	How many	How much (salary)
Ain Khaled
Al Gharrafa
Doha
Duhil
Lusail
Wakra
Grand Total

Topic 01 — Basic Rules

Topic 03 — Conditional Formatting