SUM, AVERAGE, IF, and friends

Why it matters

Most questions people ask of a table are simple: How much in total? What is typical? How many are over the limit? How much did each category cost? A dozen built-in functions answer nearly all of them, and they work the same way in Google Sheets, LibreOffice Calc and Excel.

How a function works

A function has a name, brackets, and arguments separated by commas:

=SUM(F2:F11)
=ROUND(F2, 1)
=IF(F2>100, "Review", "OK")

Start typing =SU and the tool suggests functions and shows which argument you are on. In some LibreOffice language settings the separator is a semicolon: =ROUND(F2; 1). If a comma gives an error, try ;.

The everyday summary functions

Using the Orders sheet (totals in F2:F11):

COUNT ignores text, so a number accidentally stored as text silently drops out of COUNT, SUM and AVERAGE. If COUNT is smaller than COUNTA on a numeric column, you have text pretending to be numbers.

ROUND changes the value; number formatting changes only the display. Round when the rounded value will be used in further calculations or compared.

IF: make a decision per row

IF(condition, value_if_true, value_if_false). Conditions use =, <> (not equal), <, >, <=, >=.

=IF(F2>100, "Review", "OK")
=IF(C2="Safety", F2, 0)

Text in a formula goes in double quotes. Comparisons with text are not case-sensitive in these tools.

Combine conditions with AND and OR:

=IF(AND(C2="Consumables", D2>=20), "Bulk", "")

Nested IFs (IF inside IF) work for three or four outcomes. Beyond that, use the IFS function or a lookup table (next module).

Conditional totals: SUMIF and COUNTIF

These answer "how much / how many for this group" without filtering:

=COUNTIF(C2:C11, "Safety")          4 orders
=SUMIF(C2:C11, "Safety", F2:F11)    271  total spent on Safety
=COUNTIF(F2:F11, ">100")            3 orders over 100
=AVERAGEIF(C2:C11, "Office", F2:F11)

SUMIF(range_to_test, criterion, range_to_add). Put the criterion in a cell and reference it, so one formula can be filled down a list of categories:

J2: Safety       K2: =SUMIF($C$2:$C$11, J2, $F$2:$F$11)
J3: Consumables  K3: (filled down)
J4: Office

With several conditions, use SUMIFS and COUNTIFS. Note that SUMIFS puts the sum range first: =SUMIFS(F2:F11, C2:C11, "Safety", D2:D11, ">=10").

Worked example: a summary block

Next to the Orders table, build a small summary so a reader gets the answer without scrolling:

Category totals with SUMIF: Safety 271.00, Consumables 349.80, Office 161.00. They add up to 781.80, which is a quick check that no row was missed.

Common mistakes

• Ranges of different sizes in SUMIF (C2:C11 with F2:F10). Keep them aligned.
• Writing ">100" without quotes. The criterion is text: ">100", or ">"&J1 to join it with a cell value.
• Summing a column that includes its own total row, so everything counts twice.
• Using AVERAGE on data with typos stored as text. Compare COUNT with COUNTA.

Quick recap

• SUM, AVERAGE, MEDIAN, MIN, MAX, COUNT, COUNTA, ROUND summarise a column.
• IF decides per row; combine with AND/OR.
• SUMIF/COUNTIF (and the -IFS versions) total or count one group at a time.
• Cross-check: group totals should add up to the grand total.

Try it

Open the Do tab to build a summary block for your Orders sheet.