19.1 SUMIF and SUMIFS — Conditional Summing

SUMIF sums values in a range that meet a single condition. SUMIFS sums values meeting multiple conditions simultaneously. These are the workhorses of financial reporting and data analysis.

SUMIF Syntax

• range: where to look for the condition (the column to check)
• criteria: what to match — text, number, comparison, wildcard, or cell reference
• [sum_range]: optional — the column to sum. If omitted, sums the range itself.

SUMIF Examples

SUMIFS Syntax

SUMIFS Examples

Criteria Formats for SUMIF / SUMIFS

19.2 Text Functions

Text functions extract, transform, and combine text values in cells. They are essential for cleaning imported data, reformatting names and codes, building dynamic labels, and preparing data for reports.

LEFT, RIGHT, MID — Extracting Parts of Text

LEN — Length of Text

TRIM — Remove Extra Spaces

UPPER, LOWER, PROPER — Converting Case

FIND and SEARCH — Locating Text Within Text

Both return the position number of the first character of the found text, or a #VALUE! error if not found. Use with IFERROR to handle the not-found case:

SUBSTITUTE — Replace Text

CONCATENATE (&) and TEXTJOIN — Joining Text

Joining text from multiple cells is done with the & operator or the TEXTJOIN/CONCAT functions:

VALUE and TEXT — Converting Between Numbers and Text

19.3 Essential Mathematical Functions

ROUND, ROUNDUP, ROUNDDOWN

INT and TRUNC — Whole Number Functions

MOD — Remainder After Division

ABS — Absolute Value

CEILING and FLOOR — Rounding to Multiples

POWER and SQRT

19.4 SUMPRODUCT — The Multi-Purpose Power Function

SUMPRODUCT multiplies corresponding elements of two or more arrays and then sums the results. It is one of Excel's most versatile functions — used for weighted averages, conditional sums without helper columns, cross-tabulation, and counting with complex criteria without Ctrl+Shift+Enter.

Syntax

Basic SUMPRODUCT — Weighted Total

SUMPRODUCT as a Conditional Sum (No SUMIFS Needed)

SUMPRODUCT for Counting

SUMPRODUCT vs SUMIFS

19.5 LARGE, SMALL and RANK — Ranking Functions

LARGE and SMALL

RANK

19.6 Practical SA Formula Combinations

Cleaning Imported Employee Data

Invoice and Financial Report Formulas

Payroll Calculations

19.7 Quick Self-Check

Q1: Your sales data has Region in column B and Revenue in column C. You need: (a) total revenue for Gauteng, (b) total revenue for Gauteng where individual revenue exceeds R50,000. Write both formulas.

✓ (a) =SUMIF(B2:B100,"Gauteng",C2:C100) — sums all C values where B equals "Gauteng". (b) =SUMIFS(C2:C100,B2:B100,"Gauteng",C2:C100,">50000") — sums C values where B = "Gauteng" AND C > 50,000. Note the SUMIFS argument order: sum_range first, then each criteria pair. The criteria ">50000" must be in quotes because it contains a comparison operator.

Q2: A cell A2 contains " John Smith " (with leading and trailing spaces and a double space in the middle). Write a formula that returns "John Smith" with proper capitalisation and all extra spaces removed, and a second formula that creates the email address john.smith@skailit.co.za from the same cell.

✓ Cleaned name: =PROPER(TRIM(A2)). TRIM removes all extra spaces first, then PROPER capitalises each word's first letter. Result: "John Smith". Email address: =LOWER(SUBSTITUTE(TRIM(A2)," ",".")&"@skailit.co.za"). TRIM removes spaces, SUBSTITUTE replaces the remaining single space with a period, LOWER converts to lowercase, and & joins the email domain. Result: "john.smith@skailit.co.za".

Q3: You have invoice values in column B (B2:B100) and need the total value including 15% VAT, rounded to the nearest cent. Write the formula.

✓ =ROUND(SUM(B2:B100)*1.15,2). SUM adds all invoice values, *1.15 adds 15% VAT to the total, and ROUND with 2 decimal places rounds to the nearest cent. It is important to use ROUND here rather than just formatting — ROUND changes the actual stored value, which matters for financial accuracy. Alternative if VAT is applied per invoice then summed: =SUMPRODUCT(ROUND(B2:B100*1.15,2)) ensures rounding happens per invoice rather than on the total, which can differ by a few cents in accounting contexts.

Q4: You have a list of 50 salesperson names in A2:A51 and their sales totals in B2:B51. Write a formula to rank the salesperson in B2 from highest to lowest (1 = top performer), and write a formula to find the 2nd highest sales figure in the list.

✓ Rank formula: =RANK(B2,$B$2:$B$51,0). The $B$2:$B$51 must be absolute (locked) so the reference range does not shift as the formula is copied down the column. The 0 as the third argument ranks from largest to smallest (1 = highest sales). Copy this formula from B2 down to B51 for all salesperson ranks. 2nd highest: =LARGE($B$2:$B$51,2). LARGE returns the k-th largest value; k=2 gives the 2nd largest. The absolute reference ensures the range stays fixed.

Q5: What does =SUMPRODUCT((B2:B100="Gauteng")*C2:C100) calculate, and how does it differ from =SUMIF(B2:B100,"Gauteng",C2:C100)?

✓ Both formulas calculate the exact same result: the sum of all values in C2:C100 where the corresponding cell in B2:B100 equals "Gauteng". SUMPRODUCT works by: (1) creating an array of TRUE/FALSE values from (B2:B100="Gauteng") — TRUE where the region is Gauteng; (2) multiplying each TRUE/FALSE by the corresponding C value (TRUE=1, FALSE=0), so non-Gauteng rows become 0; (3) summing the resulting array. The functional difference: SUMPRODUCT is more flexible — you can embed calculated conditions and array operations that SUMIF cannot handle. SUMIF is faster and simpler for straightforward criteria. Use SUMIF for single-condition sums; use SUMPRODUCT when you need complex array logic, weighted calculations, or distinct counts that SUMIF/SUMIFS cannot express.

Q6: A column of data should have values between 1 and 100, but some entries are outside that range. Write a SUMPRODUCT formula to count how many cells in B2:B100 are either less than 1 or greater than 100 (i.e., invalid).

✓ =SUMPRODUCT(((B2:B100<1)+(B2:B100>100)>0)*1). This creates two arrays: one for values below 1 and one for values above 100. Adding them together with + gives >0 wherever either condition is true (handles the OR logic). The >0 converts any positive number back to TRUE/FALSE, and *1 converts TRUE/FALSE to 1/0. SUMPRODUCT then sums the 1s to give the total count of invalid entries. Alternative using a simpler approach: =SUMPRODUCT(--(B2:B100<1))+SUMPRODUCT(--(B2:B100>100)) — two separate SUMPRODUCT formulas added together, counting below-1 entries plus above-100 entries separately.

✓ Module 19 Complete — You Have Learned:

• SUMIF — syntax (range, criteria, sum_range); 6 examples including wildcards and not-equal; note that sum_range is optional (sums range itself if omitted)
• SUMIFS — syntax (sum_range first, then criteria pairs); 4 multi-condition examples including date ranges; note sum_range comes first (opposite of SUMIF)
• Criteria formats — 7 formats (exact text, exact number, comparison in quotes, cell reference with operator &, wildcards *, starts-with, date with DATE())
• Text functions: LEFT/RIGHT/MID (extract); LEN (length, used in ID validation); TRIM (remove extra spaces from imports); UPPER/LOWER/PROPER (case conversion, SA names note); FIND (case-sensitive position) vs SEARCH (case-insensitive + wildcards); SUBSTITUTE (replace text); & operator and TEXTJOIN (join ranges with delimiter, skip blanks); VALUE (text to number) and TEXT (number to formatted string)
• ROUND/ROUNDUP/ROUNDDOWN — syntax; num_digits (positive = decimals, 0 = whole, negative = tens/hundreds); CEILING/FLOOR (round to multiples); SA payroll rounding use cases
• INT (round down to integer, goes negative for negative numbers) vs TRUNC (truncate toward zero); MOD (remainder, odd/even detection, overnight time, milestone check); ABS (absolute value, variance magnitude); POWER and SQRT
• SUMPRODUCT — multiplies arrays element-by-element then sums; weighted total without helper column; conditional sum with (condition)*range; counting with --(condition); distinct count with 1/COUNTIF; SUMPRODUCT vs SUMIFS comparison table
• LARGE/SMALL (k-th largest/smallest; top 3 average with array constant {1,2,3}); RANK (highest=1 with order 0; lowest=1 with order 1; tied values get same rank; RANK.EQ and RANK.AVG variants)
• Practical SA combinations — cleaning imported names (PROPER+TRIM); email generation (LOWER+SUBSTITUTE+&); split first/last name (LEFT/MID+FIND); invoice labels with TEXT; overdue sum with SUMIFS and TODAY(); employee reference codes; payslip references; payroll distinct staff count above average