Excel Functions — AKTU Exam Study

Guide
SUM · MIN · MAX · COUNT · AVERAGE · IF · SUMIF · COUNTIF

PART 1: Mathematical Functions — The Big 5

Adds Numbers
SUM

SUM Adds Numbers

Adds ALL numbers in a selected range. The most commonly used function in Excel!

Syntax: =SUM(A1:A10) or =SUM(10, 20, 30)

=SUM(10, 20, 30) Result: 60

=SUM(A1:A5) where A1:A5 = 5,3,8,1,7 Result: 24

Tip: Use colon (:) for a range like A1:A10. Use comma (,) for separate cells.

MIN Smallest Value

Finds the SMALLEST (minimum) number in a range. Ignores text and blank cells.

Syntax: =MIN(A1:A10)

=MIN(5, 3, 8, 1, 7) Result: 1 (smallest)

=MIN(A1:A5) where values = 90,45,78,32,60 Result: 32

Tip: Think: MIN = Minimum = Lowest marks in the class result.

MAX Largest Value

Finds the LARGEST (maximum) number in a range. It is the opposite of MIN.

Syntax: =MAX(A1:A10)

=MAX(5, 3, 8, 1, 7) Result: 8 (largest)

=MAX(A1:A5) where values = 90,45,78,32,60 Result: 90

Tip: Think: MAX = Maximum = Highest score / Topper's marks.

 COUNT Counts Numbers

Counts how many cells contain NUMBERS only. Blank cells and text cells are skipped.

Syntax: =COUNT(A1:A10)

Data: 5, 'hello', 30, (blank), 50 →

=COUNT(A1:A5) Result: 3 (only numbers counted)

=COUNT(B1:B10) where B has 6 numbers Result: 6

Tip: COUNT only counts numbers. Use COUNTA to count all non-empty cells (numbers + text).

AVERAGE Calculates Mean

Calculates the AVERAGE (mean) of numbers. It's the same as SUM divided by COUNT, done
automatically.

Syntax: =AVERAGE(A1:A10)

=AVERAGE(10, 20, 30) Result: 20 (because 60 / 3 = 20)

=AVERAGE(A1:A5) where values =

40,50,60,70,80 Result: 60

Tip: Average = Total sum / Number of items. Excel does this calculation for you automatically.

PART 2: Conditional & Logical Functions — IF, SUMIF, COUNTIF

IF Decision Making

Checks a condition. If TRUE → gives one result. If FALSE → gives another result. Like a real-life
'if-else' decision!

Syntax: =IF(condition, value_if_true, value_if_false)

Result: "Pass" (because 75 >= 40

=IF(A1>=40, "Pass", "Fail") when A1 = 75 is TRUE)

Result: "Fail" (because 30 >= 40

=IF(A1>=40, "Pass", "Fail") when A1 = 30 is FALSE)

Tip: Remember 3 parts: (1) Condition, (2) Result if TRUE, (3) Result if FALSE. Always 2 commas!
 SUMIF Conditional Sum

Adds numbers ONLY if they meet a condition. It is like SUM + IF combined into one powerful function!

Syntax: =SUMIF(range, criteria, sum_range)

Result: Sum of B values where A >

=SUMIF(A1:A5, ">50", B1:B5) 50

Result: Sum of B values where A

=SUMIF(A1:A5, "Mango", B1:B5) says 'Mango'

Tip: 3 parts: (1) Where to CHECK condition, (2) The condition itself, (3) What to ADD UP.

COUNTIF Conditional Count

Counts cells ONLY if they meet a condition. It is like COUNT + IF combined! Only 2 arguments, not 3!

Syntax: =COUNTIF(range, criteria)

Result: Count of cells where value

=COUNTIF(A1:A10, ">60") > 60

Result: Count of cells that say

=COUNTIF(B1:B10, "Pass") 'Pass'

Tip: Only 2 parts: (1) The range to check, (2) The condition. No separate sum_range needed!

Operators used inside conditions (criteria)

Operator Meaning Example

> Greater than ">50"

< Less than "<30"

= Equal to "=100" or "Pass"

<> Not equal to "<>Fail"

>= Greater than or equal to ">=40"

<= Less than or equal to "<=100"

QUICK CHEATSHEET — Read This Before the Exam!

Function What it does Syntax Args

SUM Adds all numbers =SUM(A1:A10) 1+

MIN Finds smallest value =MIN(A1:A10) 1+

MAX Finds largest value =MAX(A1:A10) 1+

COUNT Counts number cells =COUNT(A1:A10) 1+

AVERAGE Calculates mean =AVERAGE(A1:A10) 1+

IF Condition → true/false result =IF(cond, true, false) 3

SUMIF Sum matching cells only =SUMIF(range, crit, sum_range) 3

COUNTIF Count matching cells only =COUNTIF(range, criteria) 2

Key Differences to Remember in Exam:

• SUM adds all numbers | SUMIF adds only numbers that match the condition
• COUNT counts all numbers | COUNTIF counts only cells matching the condition
• IF has 3 arguments | COUNTIF has 2 arguments | SUMIF has 3 arguments
• Text conditions go in "double quotes" — number conditions may not need quotes
• SUMIF checks one range but can add a DIFFERENT range (sum_range)

AKTU Exam Guide — Excel Functions | Good Luck!