A function is a short name for a ready-made operation. You type its name, give it what it needs in brackets, and it hands you a result. The skill is not in memorising names but in knowing which one fits the question in front of you.
1 Counting and summing functions
| Function | What it does | Example |
|---|---|---|
| SUM | Adds the numbers in a range | =SUM(B2:B50) |
| AVERAGE | The arithmetic mean | =AVERAGE(B2:B50) |
| COUNT | Counts the cells containing numbers | =COUNT(B2:B50) |
| COUNTA | Counts non-empty cells whatever they hold | =COUNTA(A2:A50) |
| MAX / MIN | The largest and the smallest | =MAX(B2:B50) |
| ROUND | Rounds to a number of decimal places | =ROUND(B2,2) |
Formatting does not change the number
If you display 1,234.567 to two decimal places you will see 1,234.57, but Excel sums the full value — so halalas drift in the totals. If you want the number genuinely rounded, use ROUND, not formatting alone. This matters on invoices and tax returns.
2 IF — the condition
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Days outstanding | Status |
| 2 | Al Waha | 120 | Overdue |
| 3 | Al Nakheel | 45 | Within terms |
| 4 | Al Rimal | 91 | Overdue |
And if you want three bands, instead of nesting IF inside IF use IFS:
Mind the order: IFS stops at the first condition that is met, so start with the most severe. Had you started with B2>90, nothing would ever reach “Doubtful”.
3 Conditional summing and counting
| Function | The question it answers |
|---|---|
| SUMIF | What are total sales for the Riyadh branch? |
| COUNTIF | How many invoices are overdue? |
| SUMIFS | What were Riyadh’s Q1 sales to customer “A”? |
| COUNTIFS | How many overdue invoices exceed 50,000? |
| AVERAGEIF | What is the average invoice value at the Jeddah branch? |
If one row held “Riyadh ” with a trailing space, the criterion would skip it silently and the total would be 60,000 short with no error message at all — which is why we clean data with TRIM.
| A | B | C | |
|---|---|---|---|
| 1 | Branch | Quarter | Amount |
| 2 | Riyadh | Q1 | 120,000 |
| 3 | Jeddah | Q1 | 80,000 |
| 4 | Riyadh | Q2 | 95,000 |
| 5 | Riyadh | Q1 | 60,000 |
Total for Riyadh overall:
Total for Riyadh in Q1 only:
Note the difference in order: in SUMIF the criteria range comes first and then the sum range; in SUMIFS the sum range comes first. Getting them the wrong way round is one of the commonest beginner mistakes.
A stray space defeats the criterion
“Riyadh ” with a trailing space is not “Riyadh” as far as Excel is concerned, so the criterion neither sums it nor counts it. Clean your data with TRIM, and prefer picking values from a drop-down list rather than typing them by hand in every row.
4 Text and date functions you will need
Lesson summary
- SUM, AVERAGE and COUNT underpin any analysis.
- Formatting prettifies a number; ROUND actually changes it.
- IF for a single condition and IFS for several bands — ordered from the most severe.
- SUMIFS takes the sum range first; SUMIF takes the criteria range first.
- Stray spaces defeat criteria silently — clean with TRIM.
5 Test your understanding
Three quick questions
Pick the answer you believe is correct and you will see the result immediately.
1. You want to count the non-empty cells in a column of customer names. The function is:
COUNT counts numbers only; COUNTA counts any content — and names are text.
2. Which formula sums Jeddah’s sales in Q2?
Two conditions ⇒ SUMIFS, starting with the sum range and then pairs of (criteria range, criterion).
3. An invoice total differs by a halala from the system even though the figures “look” identical. Most likely:
Displaying two decimal places does not change the stored value, and the sum is taken on the full value.