Module 3 · Formulas for Analysis · Logical and conditional functions

Conditional functions and lookups — IF, SUMIFS, COUNTIFS, XLOOKUP and INDEX/MATCH

The formulas analysts use every day to classify, summarise by condition, combine tables and clean dates and text.

Beginner60 min

Recommended first

This lesson builds on: Spreadsheet foundations — structure, tables, references and validation.

Learning objectives

Write nested and combined logical tests with IF, AND, OR and IFS
Summarise with SUMIFS, COUNTIFS and AVERAGEIFS using multiple criteria
Look up values with XLOOKUP, VLOOKUP and INDEX/MATCH and handle missing matches
Use core date and text functions to prepare data

The sample table

Every example below uses this small table (named Sales, rows 2–9):

	A: Date	B: Store	C: Category	D: Units	E: Revenue
2	2025-01-05	Baku	Grocery	12	96
3	2025-01-06	Ganja	Electronics	2	310
4	2025-01-06	Baku	Electronics	1	180
5	2025-02-02	Baku	Grocery	20	150
6	2025-02-03	Ganja	Grocery	8	64
7	2025-02-10	Baku	Beauty	5	75
8	2025-02-11	Ganja	Electronics	3	420
9	2025-03-01	Baku	Grocery	15	120

Total revenue = 96 + 310 + 180 + 150 + 64 + 75 + 420 + 120 = 1,415.

IF and logical tests

=IF(test, value_if_true, value_if_false)

Flag large sales: =IF(E2>=200, "Large", "Standard") → row 3 (310) and row 8 (420) are "Large".

Combine tests with AND / OR:

=IF(AND(B2="Baku", C2="Grocery"), "Baku grocery", "Other")
=IF(OR(C2="Electronics", E2>100), "Review", "")

For several bands, IFS (Excel 2019+/365, Google Sheets) is clearer than nested IFs:

=IFS(E2>=300, "A", E2>=100, "B", TRUE, "C")

Like SQL's CASE, IFS returns the first true condition; TRUE acts as "else".

SUMIFS, COUNTIFS, AVERAGEIFS

These aggregate only rows that meet all criteria. Note the argument order: the sum range comes first in SUMIFS.

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
=COUNTIFS(criteria_range1, criteria1, …)
=AVERAGEIFS(average_range, criteria_range1, criteria1, …)

Question	Formula	Result
Baku revenue	`=SUMIFS(E2:E9, B2:B9, "Baku")`	96+180+150+75+120 = 621
Baku grocery revenue	`=SUMIFS(E2:E9, B2:B9, "Baku", C2:C9, "Grocery")`	96+150+120 = 366
Sales rows ≥ 100 revenue	`=COUNTIFS(E2:E9, ">=100")`	310,180,150,420,120 → 5
February revenue	`=SUMIFS(E2:E9, A2:A9, ">="&DATE(2025,2,1), A2:A9, "<"&DATE(2025,3,1))`	150+64+75+420 = 709
Average Ganja revenue	`=AVERAGEIFS(E2:E9, B2:B9, "Ganja")`	(310+64+420)/3 = 264.67

Criteria with operators are text: ">=100". To use a cell value, concatenate: ">="&H1. The date range uses the same half-open logic you learned in SQL.

SUMIF, COUNTIF and AVERAGEIF (singular) accept one criterion — and SUMIF puts the sum range last. Prefer the -IFS versions for consistency.

IFERROR

=IFERROR(formula, value_if_error) replaces any error (#DIV/0!, #N/A …) with a fallback. Revenue per unit: =IFERROR(E2/D2, 0).

IFERROR hiding real problems

Wrapping every formula in IFERROR(…, 0) turns a broken lookup into a believable zero. Use it only for errors you expect (e.g. a genuinely missing price), and prefer IFNA for lookups so other errors still show.

Lookups

A second table Stores (H1:J3) holds store attributes:

H: Store	I: Region	J: Manager
Baku	East	Aylin
Ganja	West	Kamran

XLOOKUP (Excel 365/2021, Google Sheets)

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Region for row 2: =XLOOKUP(B2, $H$2:$H$3, $I$2:$I$3, "Unknown") → East.

XLOOKUP defaults to exact match, can look left, returns whole rows/columns, and has a built-in "not found" value.

VLOOKUP (all versions)

=VLOOKUP(lookup_value, table, column_index, FALSE)

=VLOOKUP(B2, $H$2:$J$3, 2, FALSE) → East. Three traps:

The fourth argument must be FALSE (exact match). If omitted, VLOOKUP uses approximate match on sorted data and silently returns wrong values.
It can only look to the right of the first column.
column_index is a hard-coded number — inserting a column breaks it.

INDEX / MATCH (all versions, flexible)

MATCH finds the position; INDEX returns the value at that position:

=INDEX($I$2:$I$3, MATCH(B2, $H$2:$H$3, 0))

MATCH(…, 0) = exact match. INDEX/MATCH can look left and survives column insertions. Two-way lookup: =INDEX(J2:J3, MATCH("Ganja", H2:H3, 0)) → Kamran.

Lookup keys must match exactly

"Baku " (trailing space) ≠ "Baku", and the number 123 ≠ the text "123". When lookups fail on values that look identical, TRIM the keys and check types with ISNUMBER/ISTEXT.

Date functions

Function	Example	Result
`YEAR`, `MONTH`, `DAY`	`=MONTH(A5)`	2
`EOMONTH(date, n)`	`=EOMONTH(A2, 0)`	2025-01-31
`DATE(y, m, d)`	`=DATE(2025, 2, 1)`	2025-02-01
`TEXT(date, "yyyy-mm")`	`=TEXT(A5, "yyyy-mm")`	"2025-02"
`NETWORKDAYS(start, end)`	`=NETWORKDAYS(A2, A9)`	working days between
`TODAY()`	volatile — changes daily	avoid in reports that must be reproducible

Month bucket for grouping: =EOMONTH(A2, -1) + 1 returns the first day of the month as a real date (better than text for sorting and charts).

Text functions

Need	Formula
Remove extra spaces	`=TRIM(B2)`
Proper case	`=PROPER(B2)`
Join	`=B2 & " – " & C2` or `=TEXTJOIN(", ", TRUE, range)`
Left/right/middle	`=LEFT(code, 3)`, `=MID(email, FIND("@", email)+1, 100)`
Text → number	`=VALUE(" 42 ")` or `--TRIM(x)`
Split (365)	`=TEXTSPLIT(A2, "-")`

Conditional calculations without helper columns

SUMPRODUCT multiplies arrays and sums — a powerful conditional calculator. Electronics revenue without a helper column:

=SUMPRODUCT((C2:C9="Electronics") * E2:E9)   → 310 + 180 + 420 = 910

Each comparison yields TRUE/FALSE (1/0); multiplying by revenue keeps only matching rows.

Practice

Using the lesson's Sales table (rows 2–9: Baku Grocery 96, Ganja Electronics 310, Baku Electronics 180, Baku Grocery 150, Ganja Grocery 64, Baku Beauty 75, Ganja Electronics 420, Baku Grocery 120; dates in A, store in B, category in C, units in D, revenue in E), what does =SUMIFS(E2:E9, B2:B9, "Ganja", C2:C9, "Electronics") return?

=VLOOKUP(A2, Prices!A:C, 3) sometimes returns a price for the wrong product. What is the most likely cause?

Store names are in H2:H3, managers in J2:J3. Write an INDEX/MATCH formula that returns the manager for the store in B2 (exact match). Use absolute references for the ranges.

Summary

IF/IFS classify; combine tests with AND/OR; order conditions carefully.
SUMIFS/COUNTIFS/AVERAGEIFS aggregate by multiple criteria; operators are text (">="&cell).
Prefer XLOOKUP or INDEX/MATCH; if you use VLOOKUP, always set exact match (FALSE).
Use IFERROR/IFNA sparingly; clean keys with TRIM; keep dates as real dates.

← Previous

Spreadsheet foundations — structure, tables, references and validation

PivotTables, cleaning, reconciliation and variance analysis