Excel · Lesson 4 of 8

IF, AND, OR, IFS & COUNTIF

Making decisions in formulas and counting or summing with conditions.

IF(test, value_if_true, value_if_false) — the core decision function.
AND(a, b) / OR(a, b) — combine tests inside IF.
IFS(test1, result1, test2, result2, …) — cleaner than nested IFs (Excel 2019+). Use TRUE as the last test for “otherwise”.
IFERROR(formula, fallback) — show something friendly instead of #N/A or #DIV/0!.
COUNTIF(range, criteria), SUMIF(range, criteria, sum_range), and the multi-condition COUNTIFS / SUMIFS.

Text criteria go in quotes: "Kochi"; comparisons too: ">=60".

Sample sheet & formulas

Type these in Excel or Google Sheets

     A        B         C       D
1    Name     City      Score   Result
2    Anu      Kochi     82
3    Rahul    Thrissur  58
4    Meera    Kochi     67
5    Akhil    Kollam    45

D2:  =IFS(C2>=80,"Distinction", C2>=60,"Pass", TRUE,"Retry")   fill down
E2:  =IF(AND(B2="Kochi", C2>=60), "Kochi shortlist", "")        fill down

G1:  =COUNTIF(C2:C5, ">=60")
G2:  =AVERAGEIF(B2:B5, "Kochi", C2:C5)
G3:  =COUNTIFS(B2:B5, "Kochi", C2:C5, ">=80")
G4:  =IFERROR(C2/0, "n/a")

Results

D2..D5 = Distinction, Retry, Pass, Retry
E2..E5 = Kochi shortlist, (blank), Kochi shortlist, (blank)
G1 = 2
G2 = 74.5
G3 = 1
G4 = n/a

Works in Microsoft Excel 365/2021 and, for almost every formula, in Google Sheets. Data is fictional.

Key points

IF(test, yes, no).
IFS replaces nested IFs; end with TRUE for 'else'.
COUNTIFS/SUMIFS handle several conditions.

Quick check

Which function counts cells meeting two conditions?

COUNTIF COUNTIFS COUNTA IFS

Practice task

For a sales list with Region and Amount, calculate total sales for “North” above ₹10,000 using SUMIFS.

Interview corner

How would you flag duplicate entries in a column?

Use =COUNTIF($A$2:$A$100, A2)>1 in a helper column, or Conditional Formatting → Highlight Duplicate Values.

Why use IFERROR carefully?

It hides every error, including real mistakes. Prefer IFNA for lookups so other errors still show.