Webbo3 Data Analysis Bootcamp · Excel Module · Lesson 10

Excel Math and Statistical Functions: SUMIF, COUNTIF, AVERAGEIF, ROUND, INT, MOD, ABS, POWER, and SQRT

A hands on lesson on summing, counting, and averaging based on conditions, rounding numbers correctly, and the core math functions every real spreadsheet eventually needs.

SUM, COUNT, and AVERAGE from earlier in this bootcamp all calculate across an entire range, no questions asked. Real business questions are rarely that simple. You usually want a total for one specific region, a count of orders above a certain value, or an average score for one particular class. This lesson covers the conditional versions of those three functions, the rounding functions that control precision, and a handful of core math functions that show up constantly once you start building real formulas.

1. SUMIF and SUMIFS: Conditional Summing

SUMIF syntax: =SUMIF(range, criteria, [sum_range])

SUMIFS syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

SUMIF adds up values that meet exactly one condition. If column A holds regions and column C holds sales figures, =SUMIF(A2:A50,"Lagos",C2:C50) totals only the sales figures where the matching region cell reads "Lagos." SUMIFS does the same job but lets you stack multiple conditions together, all of which must be true at once for a row to count. =SUMIFS(C2:C50,A2:A50,"Lagos",B2:B50,"Q1") totals sales only where the region is Lagos and the quarter is Q1, both conditions required simultaneously.

The single detail that trips up almost everyone moving from one function to the other. SUMIF and SUMIFS put their arguments in a different order, and it is easy to get backwards. In SUMIF, sum_range comes last and is optional. In SUMIFS, sum_range comes first and is required. This means a formula like =SUMIF(A2:A50,"Lagos",C2:C50) cannot simply be rewritten as =SUMIFS(A2:A50,"Lagos",C2:C50) by adding an S, since SUMIFS expects the range you want to sum as its very first argument, not its third. Many experienced Excel users default to SUMIFS even for a single condition specifically to avoid having to remember which function puts sum_range where. Also worth knowing: SUMIFS only combines conditions with AND logic, never OR, so every condition you add narrows the result further rather than broadening it.

2. COUNTIF and COUNTIFS: Conditional Counting

COUNTIF syntax: =COUNTIF(range, criteria)

COUNTIFS syntax: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

These two work exactly like SUMIF and SUMIFS, except they count matching cells instead of summing a separate column. =COUNTIF(A2:A50,"Lagos") counts how many rows have "Lagos" in column A. COUNTIFS adds more conditions with AND logic the same way SUMIFS does: =COUNTIFS(A2:A50,"Lagos",D2:D50,">10000") counts only the rows where the region is Lagos and the sales figure in column D exceeds 10,000.

A genuinely useful detail: comparison operators and wildcards both work inside the criteria. Criteria like ">10000", "<>0" for not equal to zero, or ">="&E2 to compare against a value sitting in another cell, all work directly inside COUNTIF and COUNTIFS. When comparing against a cell reference rather than a hardcoded number, the operator and the reference must be joined with an ampersand inside quotes, exactly as shown. Wildcards work too: "N*" matches anything starting with N, and "?at" matches any three letter word ending in "at." If you genuinely need to search for a literal asterisk or question mark character, put a tilde directly before it.

3. AVERAGEIF and AVERAGEIFS

AVERAGEIF syntax: =AVERAGEIF(range, criteria, [average_range])

AVERAGEIFS syntax: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

These follow exactly the same pattern as the SUMIF and SUMIFS pair, with the identical argument order quirk: AVERAGEIF puts average_range last and optional, while AVERAGEIFS puts it first and required. =AVERAGEIF(B2:B40,"Class A",C2:C40) finds the average score in column C only for students whose class in column B reads "Class A." Just like AVERAGE itself, these conditional versions still ignore genuinely blank cells when calculating the average, rather than treating them as zero, so a student who has not yet submitted a score is excluded from the average entirely, not counted as scoring zero. Keep that distinction from the earlier lesson on basic formulas in mind here too, since it applies identically.

4. ROUND, ROUNDUP, ROUNDDOWN

Syntax: =ROUND(number, num_digits), =ROUNDUP(number, num_digits), =ROUNDDOWN(number, num_digits)

All three share the same two arguments, and the difference between them is purely about direction. ROUND follows ordinary rounding rules: a dropped digit of 5 or higher rounds up, anything below 5 rounds down. =ROUND(4.55,1) returns 4.6. ROUNDUP always rounds away from zero regardless of what the dropped digit actually is, so =ROUNDUP(4.51,1) still returns 4.6, and even =ROUNDUP(4.001,0) jumps all the way up to 5. ROUNDDOWN always rounds toward zero no matter what, so =ROUNDDOWN(4.99,0) returns 4, not 5.

A detail worth knowing for negative numbers specifically. "Away from zero" and "toward zero" matter more once negative numbers enter the picture. =ROUNDUP(-4.1,0) returns -5, not -4, because rounding away from zero on a negative number makes it more negative, not less. =ROUNDDOWN(-4.9,0) returns -4, not -5, because rounding toward zero on a negative number makes it less negative. This trips people up specifically in financial models tracking losses or deductions as negative values, where the intuitive expectation often runs backwards from what actually happens.

The num_digits argument can be negative too, and this solves a surprisingly common real problem. A positive num_digits rounds to that many decimal places. Zero rounds to a whole number. A negative num_digits rounds to the left of the decimal point instead, to the nearest ten, hundred, or thousand. =ROUND(1547,-2) returns 1500, rounding to the nearest hundred, and =ROUND(1547,-3) returns 2000, rounding to the nearest thousand. This single feature is the cleanest way to round large numbers, like revenue figures in a summary report, to a tidier, more presentable scale without manually dividing and multiplying.

5. INT, MOD, ABS, POWER, SQRT

INT syntax: =INT(number)

INT strips away the decimal portion of a number and returns only the whole number part, always rounding down toward negative infinity in the process. For positive numbers this behaves identically to ROUNDDOWN with zero digits, but for negative numbers the two genuinely diverge: =INT(-4.5) returns -5, rounding further away from zero, while =ROUNDDOWN(-4.5,0) returns -4, rounding toward zero instead. This is a real, easy-to-miss difference, not a minor footnote, so if your data ever includes negative numbers, double check which of the two you actually need.

MOD syntax: =MOD(number, divisor)

MOD returns the remainder left over after dividing number by divisor. =MOD(10,3) returns 1, since 3 goes into 10 three times with 1 left over. The single most common practical use of MOD is checking whether a number is even or odd: =IF(MOD(A2,2)=0,"Even","Odd") divides by 2 and checks whether anything is left over. MOD is also the standard trick for building zebra-striped row shading in conditional formatting, using a formula like =MOD(ROW(),2)=0 to detect alternating rows, which keeps working correctly even as rows are added or deleted later, unlike formatting applied manually row by row.

A genuinely surprising behaviour worth knowing. The result of MOD always carries the same sign as the divisor, not the number being divided. =MOD(-10,3) returns 2, not -1 as you might intuitively expect, because the divisor is positive. If divisor itself is 0, MOD returns a #DIV/0! error, the same error you would get from a plain division by zero.

ABS syntax: =ABS(number)

ABS strips away a negative sign entirely, returning the absolute value of whatever number you give it. =ABS(-25) and =ABS(25) both return 25. This is genuinely useful whenever you only care about the size of a difference, not its direction, for example measuring how far a forecast was from an actual result regardless of whether the forecast came in too high or too low: =ABS(forecast-actual).

POWER syntax: =POWER(number, power)

POWER raises a number to a given exponent. =POWER(2,3) returns 8, since 2 raised to the power of 3 is 2 times 2 times 2. The caret symbol, ^, does exactly the same job as a shorter operator, so =2^3 returns the identical result. Fractional exponents calculate roots: =POWER(27,1/3) returns 3, the cube root of 27, which means Excel has no need for a separate cube root or fourth root function, since any root can be expressed as a fractional power of POWER or the caret operator. One genuine syntax trap with the caret specifically: Excel evaluates the exponent before applying a leading negative sign, so =-5^2 actually returns -25, not 25, because it is read as the negative of 5 squared, not negative 5 squared. Wrapping the negative number in parentheses, =(-5)^2, fixes this and correctly returns 25.

SQRT syntax: =SQRT(number)

SQRT returns the positive square root of a number. =SQRT(81) returns 9. The exact same calculation can also be written as =81^0.5 or =POWER(81,0.5), since taking a square root is mathematically identical to raising a number to the power of one half.

A genuine limitation worth knowing before it confuses you. SQRT returns a #NUM! error if you give it a negative number, because the square root of a negative number does not exist within the set of real numbers that Excel works with by default. If you specifically need to find the square root of a number's magnitude regardless of its sign, wrap it in ABS first, for example =SQRT(ABS(A2)), which calculates the square root of the absolute value rather than erroring out on a negative input.

Using AI to Move Faster in Excel

This lesson is full of small, specific quirks: argument order swaps, sign behaviour with negative numbers, rounding direction differences that only matter at the edges. AI inside Excel earns its place here precisely because it can hold all of those small rules in mind at once, faster than checking back through notes mid-task.

1. Describe the condition, let Copilot pick between the single and multiple criteria version.
Rather than working out upfront whether you need SUMIF or SUMIFS for a given task, describe the actual business question, for example "Total the sales for the Lagos region during Q1 only." Since that genuinely needs two conditions at once, Copilot should reach for SUMIFS with the correct argument order automatically, sidestepping the exact mix-up this lesson flagged as the most common mistake when moving between the two functions.

2. Ask Copilot to explain a rounding result that looks wrong but is not.
If a ROUNDUP or ROUNDDOWN result on a negative number looks backwards from what you expected, ask directly: "Why does ROUNDUP give -5 here instead of -4?" A correct answer should walk through the away-from-zero versus toward-zero logic covered in this lesson, which is often the fastest way to build real intuition for a rule that otherwise feels arbitrary.

3. Use natural language to build a zebra-striping or even/odd rule without remembering the MOD syntax.
Conditional formatting rules built on MOD are genuinely useful but easy to forget the exact syntax for under time pressure. Describe the visual outcome directly, for example "Highlight every other row in this table so it's easier to scan," and let Copilot generate the correct MOD-based formula rather than reconstructing it from memory each time.

4. Test the edge cases yourself, especially anything involving negative numbers or zero.
Every function in this lesson has at least one behaviour that only shows up at an edge: a negative number, a zero divisor, a blank cell inside an AVERAGEIF range. Whichever tool wrote the formula, deliberately test it against a negative input, a zero, and a genuinely blank cell before trusting it across a full column, since these are exactly the inputs where the quiet rules in this lesson actually matter.

A habit worth carrying forward from this lesson: whenever a formula involves rounding, remainders, or conditional totals, ask yourself specifically whether your data could ever include a negative number, a zero, or a genuinely blank cell, and if so, what that specific function does in that specific case. Math functions feel safer than logical or lookup functions because they rarely throw a dramatic error, but the quiet sign and rounding-direction rules in this lesson are exactly the kind of thing that produces a subtly wrong number nobody notices until much later.

Next lesson: working with data ranges, sorting, filtering, and conditional formatting.