IF, AND, OR, IFS and counting with conditions

Logical functions let a spreadsheet make decisions: pass or fail, paid or owing, in stock or reorder. They're behind most school mark sheets, fee trackers and stock sheets.

IF

=IF(condition, value_if_true, value_if_false)

Comparison operators: =, <> (not equal), >, <, >=, <=. Text in formulas goes in double quotes.

More examples:

=IF(D2>0, "Owing", "Cleared")                 fee balance status
=IF(C2<10, "Reorder", "")                     stock alert (blank if fine)
=IF(B2>=100000, B2*5%, 0)                     commission only above a target

Several conditions: AND, OR, NOT

=IF(AND(B2>=50, C2>=50), "Promoted", "Repeat")        both subjects passed
=IF(OR(D2="Paid", E2="Scholarship"), "Allowed", "See bursar")
=IF(NOT(F2="Suspended"), "Active", "Suspended")

Grading: nested IF vs IFS

Nested IF (works in every version):

=IF(B2>=80,"A",IF(B2>=65,"B",IF(B2>=50,"C",IF(B2>=40,"D","E"))))

IFS (Excel 2019/365 and Google Sheets) is easier to read:

=IFS(B2>=80,"A", B2>=65,"B", B2>=50,"C", B2>=40,"D", TRUE,"E")

Checks run in order; TRUE at the end acts as "everything else".

Even cleaner for many bands: put the grade table in cells and use XLOOKUP or VLOOKUP with approximate match (see the Lookups lesson). Changing a cut-off then means editing one cell, not every formula.

Counting and adding with conditions

Criteria with a cell: =COUNTIF(B2:B41, ">="&H1) uses the value in H1.

Handling errors: IFERROR

=IFERROR(C2/B2, 0)                    0 instead of #DIV/0!
=IFERROR(XLOOKUP(A2, IDs, Names), "Not found")

Use it carefully: hiding errors can also hide real mistakes.

Worked example: a fee tracker

Summary cells:

Cleared students:   =COUNTIF(E2:E41,"Cleared")
Total collected:    =SUM(C2:C41)
Total outstanding:  =SUMIF(D2:D41,">0")
Collection rate:    =SUM(C2:C41)/SUM(B2:B41)    (format as %)

Add conditional formatting (a later lesson) to colour "Not paid" red.

Why logical functions matter

Logic turns a spreadsheet from a list of numbers into a tool that makes decisions: pass or fail, paid or owing, in stock or reorder, on time or late, bonus or no bonus. Teachers grade with IF, accountants flag overdue invoices, shopkeepers decide when to restock, HR checks eligibility for allowances. Combined with counting functions, logic answers questions like "how many students passed Maths?" or "how much did the Nakuru branch sell in March?"

Comparison operators

Text comparisons ignore case ("paid" equals "Paid"), but extra spaces matter: "Paid " with a trailing space is not equal to "Paid". Clean data with TRIM first.

IF with calculations

IF can return calculations, not just words:

=IF(B2>=10000, B2*0.05, 0)                 5% bonus only on sales of 10,000 or more
=IF(C2="Yes", B2*0.16, 0)                  VAT only when the item is VATable
=IF(D2="", "", D2-C2)                      leave blank until a date is entered
=IF(E2<=TODAY(), "Overdue", "Due in " & E2-TODAY() & " days")

The third formula is a common trick: it prevents ugly results (like negative numbers) in rows that haven't been filled yet.

Real example: stock reorder alerts

A better version distinguishes out of stock:

=IF(B2=0, "OUT OF STOCK", IF(B2<=C2, "Reorder", "OK"))

Add conditional formatting to colour "OUT OF STOCK" red and "Reorder" orange, and the sheet becomes a dashboard.

AND/OR in real decisions

=IF(AND(B2>=50, C2>=50), "Promoted", "Repeat")              passed both subjects
=IF(OR(D2="Teacher", D2="Nurse"), "Eligible", "Not eligible")  either job qualifies
=IF(AND(E2>=18, E2<=35, F2="Kenyan"), "Youth fund eligible", "No")
=IF(NOT(G2="Paid"), "Send reminder", "")

Example eligibility rules here are illustrations; always use the actual rules of the programme you're working with.

SWITCH: matching exact values

When you compare one cell to many fixed values, SWITCH is cleaner than nested IFs:

=SWITCH(B2, "NBO", "Nairobi", "MSA", "Mombasa", "KSM", "Kisumu", "Unknown branch")
=SWITCH(WEEKDAY(A2,2), 6, "Weekend", 7, "Weekend", "Weekday")

Counting and summing with conditions: more examples

Suppose a sales table has Branch in column B, Product in C, Amount in D and Date in E:

=COUNTIF(B:B, "Nakuru")                               number of Nakuru sales
=COUNTIF(D:D, ">5000")                                sales above 5,000
=COUNTIF(C:C, "*unga*")                               product name contains "unga" (* is a wildcard)
=SUMIF(B:B, "Nakuru", D:D)                            total Nakuru sales
=SUMIFS(D:D, B:B, "Nakuru", C:C, "Sugar")             Nakuru sugar sales
=SUMIFS(D:D, E:E, ">="&DATE(2026,3,1), E:E, "<"&DATE(2026,4,1))   March sales
=AVERAGEIFS(D:D, B:B, "Thika")                        average Thika sale
=COUNTIFS(B:B, H2, C:C, I2)                           criteria taken from cells H2 and I2
=MAXIFS(D:D, B:B, "Eldoret")                          largest Eldoret sale

Notice ">="&DATE(...): comparison operators go inside quotes and are joined to values with &. Using cells for criteria (H2, I2) makes a flexible report where the user just types a branch name.

A branch summary report

Fill the formulas down. Check: the total of the summary must equal =SUM(D:D). Cross-checking totals catches missing branches and typos in branch names.

Error handling beyond IFERROR

=IFERROR(B2/C2, 0)                        any error becomes 0
=IFNA(XLOOKUP(A2, Codes, Names), "Not found")   only #N/A is replaced
=IF(C2=0, "-", B2/C2)                     prevent the error in the first place

Use IFERROR carefully: it hides every error, including real mistakes like a misspelled range name. Prefer IFNA for lookups, or check the cause directly.

Common mistakes

Practice

1. Build a fee tracker with Paid, Balance and a Status column showing "Cleared", "Partial" or "Not paid".
2. Make a branch summary with COUNTIF and SUMIF where the branch names are typed in cells.
3. Use SUMIFS to total sales for one product in one month.
4. Create a stock sheet that shows "OUT OF STOCK", "Reorder" or "OK".
5. Write a formula that gives a 5% bonus to staff who sold over 100,000 AND had zero complaints.