Function SUM() with Excel

Somsak Chanaim

International College of Digital Innovation, CMU

July 2, 2025

Sales Data

Copy the table below into Excel starting from cell A1.

Name Category Sales (Baht)
Somchai Household 500
Somying Food 200
Somwang Household 700
Somjai Household 300
Somru Household 400
Somkhuan Food 800
Somrudee Food 600
Somrak Clothing 900
Somsak Clothing 400
Somporn Clothing 500
Somrak Clothing 300
Somjaidee Food 700
Somprasong Household 400
Somkiat Household 200
Somkid Food 600
Somphong Clothing 800
Sombat Household 500
Somruay Clothing 1000
Somsuay Food 300
Somngam Food 400

In Excel, the SUM function is the primary function used to add numerical values.

It can be used in various forms and has several related functions for summing values, which can be categorized based on usage as follows:

  • SUM()

  • SUMIF()

  • SUMIFS()

  • SUBTOTAL()

  • AGGREGATE()

  • DSUM()

1. SUM() Function (Basic Usage)

The SUM function is used to add numbers or a range of numeric values.

Syntax:

=SUM(number1, [number2], ...)
  • number1, number2, …: Numbers or ranges to be summed

Example: Sum all sales in column C

=SUM(C2:C21)

Result: Total sales = 11,700 Baht

2. SUMIF() Function (Conditional Sum with One Criterion)

The SUMIF function is used to sum values that meet a single condition.

Syntax:

=SUMIF(range, criteria, [sum_range])

  • range: The range of cells to evaluate the condition

  • criteria: The condition to check (e.g., “>10”, “=Somchai”)

  • sum_range (optional): The range of cells to sum (if different from range)

Example: Sum sales in the category “Household”

=SUMIF(B2:B21, "Household", C2:C21)

Result: Total sales in the “Household” category = 3,000 Baht

3. SUMIFS() Function (Conditional Sum with Multiple Criteria)

The SUMIFS function is used to sum values that meet multiple conditions.

Syntax:

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

  • sum_range: The range of cells to sum

  • criteria_range1, criteria1: The first range and its corresponding condition

  • (You can add more pairs for additional conditions)

Example: Sum sales in the “Household” category where sales are greater than 300 Baht

=SUMIFS(C2:C21, B2:B21, "Household", C2:C21, ">300")

Result: Total sales in the “Household” category (greater than 300 Baht) = 2,500 Baht

4. SUBTOTAL() Function (Flexible Summation)

The SUBTOTAL function is used to calculate values such as SUM, AVERAGE, or COUNT, while allowing you to ignore filtered or hidden rows.

Syntax:

=SUBTOTAL(function_num, range)
  • function_num: The code for the type of calculation (e.g., 9 for SUM, 1 for AVERAGE)

Example: Sum all sales (only visible values, excluding filtered rows)

=SUBTOTAL(9, C2:C21)

Result: If any rows are filtered out, the sum will automatically update to include only visible values.

Supported Calculation Types (function_num)

function_num Description Example Calculation
1 Average (AVERAGE) =SUBTOTAL(1, A1:A10)
2 Count numbers (COUNT) =SUBTOTAL(2, A1:A10)
3 Count non-empty values (COUNTA) =SUBTOTAL(3, A1:A10)
4 Maximum (MAX) =SUBTOTAL(4, A1:A10)
5 Minimum (MIN) =SUBTOTAL(5, A1:A10)
6 Product of values (PRODUCT) =SUBTOTAL(6, A1:A10)
7 Sample standard deviation (STDEV.S) =SUBTOTAL(7, A1:A10)
8 Population standard deviation (STDEV.P) =SUBTOTAL(8, A1:A10)
9 Sum (SUM) =SUBTOTAL(9, A1:A10)
10 Sample variance (VAR.S) =SUBTOTAL(10, A1:A10)
11 Population variance (VAR.P) =SUBTOTAL(11, A1:A10)

5. AGGREGATE() Function (Advanced Summation)

The AGGREGATE function is used to perform calculations while handling errors or ignoring filtered/hidden values.

Syntax:

=AGGREGATE(function_num, options, array, [k])

  • function_num: The code for the type of calculation (e.g., 9 for SUM)

  • options: How to handle hidden rows or errors

Example: Sum all sales while skipping cells with errors

=AGGREGATE(9, 6, C2:C21)

Result: Sums the values in range C2:C21, ignoring errors such as #VALUE!

Summary

The key summation functions in Excel are as follows:

Function Usage Description Example (Formula) Result
SUM Sum all values =SUM(C2:C21) 11,700 Baht
SUMIF Sum values based on a single condition =SUMIF(B2:B21, "Household", C2:C21) 3,000 Baht
SUMIFS Sum values based on multiple conditions =SUMIFS(C2:C21, B2:B21, "Household", C2:C21, ">300") 2,500 Baht
SUBTOTAL Sum only visible values (ignore filtered rows) =SUBTOTAL(9, C2:C21) Depends on filter
AGGREGATE Sum while ignoring errors =AGGREGATE(9, 6, C2:C21) 11,700 Baht
DSUM Sum values from a database based on criteria =DSUM(A1:C21, "Sales (Baht)", E1:E2) 3,600 Baht

Extra Questions

Simulated Data (Place in A1)

Salesperson Product Units
Alice Altis 3
Alice Camry 2
Alice Fortuner 1
Bob Altis 4
Bob Camry 0
Bob Fortuner 2
Ethan Altis 5
Ethan Camry 3
Ethan Fortuner 0
Alice Altis 1
Bob Camry 1
Ethan Fortuner 2

🏷Price List

Product Price (Baht)
Altis 900000
Camry 1200000
Fortuner 1300000

Q1: What is the total number of cars sold by all salespersons?

👉 Use: SUM

=SUM(C2:C13)

Q2: How many Camry cars were sold?

👉 Use: SUMIF

=SUMIF(B2:B13, "Camry", C2:C13)

Q3: How many Fortuner cars did Bob sell?

👉 Use: SUMIFS

=SUMIFS(C2:C13, A2:A13, "Bob", B2:B13, "Fortuner")

Q4: What is the total revenue (Baht) from Altis sales?

👉 Use: SUMIF + multiply by price

=SUMIF(B2:B13, "Altis", C2:C13) * 900000

Q5: Calculate total revenue from all cars using SUMPRODUCT

👉 Use: SUMPRODUCT with helper columns (Assume D2:D13 is a helper column with price per unit, using XLOOKUP(B2,F2:F4,G2:G4))

=SUMPRODUCT(C2:C13, D2:D13)

Q6: What is the total number of visible units (after applying filter)?

👉 Use: SUBTOTAL

=SUBTOTAL(9, C2:C13)

Q7: If some cells in Units column have errors (e.g., #N/A), calculate total units sold ignoring errors.

👉 Use: AGGREGATE

=AGGREGATE(9, 6, C2:C13)

Q8: Use DSUM to find total Altis units sold

👉 Setup criteria table (e.g., E1:E2)

Product
Altis 
=DSUM(A1:C13, "Units", E1:E2)