Hàm sumproduct tính tổng nhiều điều kiện cho phép kết hợp điều kiện theo ngày, theo chuỗi, theo khoảng giá trị mà không cần cột phụ. Cơ chế rất đơn giản: điều kiện trả về TRUE/FALSE được quy đổi 1/0, nhân với vùng số liệu rồi cộng lại.
Ví dụ nhanh: =SUMPRODUCT((B2:B100=”Hà Nội”)*(C2:C100=”SP A”)*D2:D100) để tính tổng doanh thu SP A tại Hà Nội. Xem hướng dẫn và ví dụ chi tiết dưới đây.
1. Công dụng của hàm SUMPRODUCT
Hàm SUMPRODUCT là hàm tính tổng của tích các phần tử tương ứng giữa (các) mảng. Hàm này có thể thay thế hàm sumif hoặc sumifs trong các phép tính tổng có điều kiện phức tạp.
Về nguyên tắc hoạt động: hàm sumproduct nhân tùng phân tử của các mảng dữ liệu với nhau, sau đó cộng tổng các tích vừa nhân lại.
Vì vậy, hàm sumproduct rất hữu ích khi cần tính tổng nhiều điều kiện. Ví dụ: Bạn có thể dùng hàm SUMPRODUCT để:
- Tính tổng doanh thu
- Tính doanh thu theo nhóm sản phẩm
- Tính tổng doanh thu theo nhóm sản phẩm và thời gian
2. Cấu trúc hàm SUMPRODUCT
Cú pháp hàm: =SUMPRODUCT(array1; [array2]; [array3]; …)
Trong đó:
- array1: đối số bắt buộc, là mảng đầu tiên mà bạn muốn nhân các thành phần của nó rồi cộng tổng.
- array2, array3,…: đối số tùy chọn, là các mảng tiếp theo mà bạn muốn nhân các thành phần của nó rồi cộng tổng.
Lưu ý:
- Các mảng array1, array2,… phải có cùng kích cỡ. Nếu không hàm sẽ trả về lỗi #VALUE!.
- Các giá trị trong array1, array2,… phải là số. Nếu không hàm sẽ trả về lỗi #VALUE!.
- Hàm SUMPRODUCT cho phép nhập tối đa 255 đối số.
3. Cách sử dụng hàm SUMPRODUCT tính tổng nhiều điều kiện
3.1. Cách sử dụng hàm SUMPODUCT cơ bản
Ví dụ ta có bảng dữ liệu số lượng và đơn giá bán như hình dưới. Yêu cầu tính tổng doanh thu của tất cả mặt hàng.
Như bình thường, ta sẽ lấy
- Số lượng x đơn giá = doanh thu
- Dùng hàm SUM cộng doanh thu của tất cả sản phẩm với nhau để ra tổng doanh thu
Với SUMPRODUCT, chỉ cần 1 công thức:
=SUMPRODUCT(B2:B7;C2:C7)
Ta có thể thấy, kết quả của hàm SUMPRODUCT giống như kết quả trước đó. Chỉ với một hàm duy nhất ta đã có thể tiết kiệm rất nhiều thời gian tính toán và giảm thiểu sai sót.
3.2. Cách dùng hàm sumproduct tính tổng với một điều kiện
Ví dụ, bảng báo cáo doanh thu của một cửa hàng bán thiết bị điện tử như dưới đây, bạn cần tính tổng số lượng bán ra và tổng doanh thu của nhóm sản phẩm điện thoại.
Áp dụng cú pháp hàm SUMPRODUCT: =SUMPRODUCT(array1; [array2]; [array3]; …)
- Tính tổng số lượng bán ra của nhóm sản phẩm điện thoại, tại ô C19, điền:
=SUMPRODUCT((C2:C15="Điện thoại")*(D2:D15))
Trong đó:
-
- C2:C15 = “Điện thoại” là vùng dữ liệu chứa nhóm sản phẩm điện thoại
- Dấu *: Dùng trong điều kiện và (Dấu + dùng trong điều kiện hoặc)
- D2:F15 Cột số lượng
- Tính tổng doanh thu của nhóm sản phẩm điện thoại, tại ô C20 điền
=SUMPRODUCT((C2:C15="Điện thoại")*(D2:D15)*(E2:E15))
Trong đó:
- C2:C15=”Điện thoại” cột nhóm sản phẩm lấy những dữ liệu thuộc nhóm Điện thoại
- D2:D15: Số lượng
- E2:E15 Đơn giá
- Dấu * Dùng trong điều kiện và
3.3. Tính tổng với 2 điều kiện cơ bản
Với nhiều điều kiện hơn, ta cũng dùng hàm sumproduct và áp dụng tương tự như một điều kiện. Nhưng cần lưu ý dấu */+. Trong đó
- Điều kiện đồng thời (and) dùng dấu *
- Điều kiện hoặc (or) dùng dấu +
Ví dụ: Tính tổng doanh thu của 2 nhóm sản phẩm là Điện thoại và màn hình
Áp dụng cấu trúc chuẩn của hàm SUMPRODUCT, tại ô C21 điền công thức sau:
=SUMPRODUCT(((C2:C15="Điện thoại")+(C2:C15="Màn hình"))*(D2:D15)*(E2:E15))
Trong đó:
- (C2:C15=”Điện thoại”) : Lấy những dữ liệu là “Điện thoại” thuộc cột nhóm sản phẩm
- Dấu + : Điều kiện hoặc
- (C2:C15=”Màn hình”) : Hoặc lấy dữ liệu là “Màn hình” trong cột nhóm sản phẩm
- Dấu * : (dùng trong điều kiện và)
- (D2:D15): Cột số lượng
- (E2:E15): Cột đơn giá
Sau khi ấn enter, hàm lập tức trả về kết quả 3.149.458.417 là tổng doanh thu của các nhóm sản phẩm Điện thoại và Màn hình
3.4. Hàm SUMPRODUCT tính tổng nhiều điều kiện
Với những phép tính gồm nhiều điều kiện phức tạp, ta cũng có thể áp dụng hàm SUMPRODUCT để tính.
Ví dụ: tính tổng doanh thu của nhóm sản phẩm Điện thoại và màn hình trong ngày 08/04/2024. Và Chỉ tính doanh thu nếu số lượng bán ra của sản phẩm lớn hơn 25.
Các bước tính như sau:
Bước 1: Xác định các điều kiện
| Điều kiện 1 | Điện thoại |
| Điều kiện 2 | Màn hình |
| Điều kiện 3 | 8/4/2024 |
| Điều kiện 4 | Số lượng lớn hơn 25 |
Bước 2: áp dụng cấu trúc hàm SUMPRODUCT tính tổng nhiều điều kiện
Áp dụng cú pháp hàm sumproduct, tại ô 22 ta điền công thức:
=SUMPRODUCT(((C2:C15="Điện thoại")+(C2:C15="Màn hình")*(F2:F15=D20)*((D2:D15)>25)*(D2:D15)*(E2:E15)))
Trong đó:
- (C2:C15=D18)+(C2:C15=D19) : lấy dữ liệu của các nhóm sản phẩm là Điện thoại hoặc
- (C2:C15=D18): So sánh nhóm sản phẩm với ô D18: Điện thoại, nếu đúng là Điện thoại trả về True, nếu sai trả về False. Excel quy đổi TRUE thành 1, False thành 0
- (C2:C15=D19): So sánh Nhóm sản phẩm với ô D19 (“Màn hình”)
- Dấu + biểu diễn OR: chỉ cần 1 trong 2 điều kiện đúng thì trả về giá trị 1, ngược lại 0
- (F2:F15=D20): Điều kiện Ngày bán bằng ô D20 (2024-04-08)
- ((D2:D15)>25): Điều kiện số lượng lớn hơn 25
- *(D2:D15): Lấy Số lượng ở từng dòng để nhân vào (chỉ các dòng đạt điều kiện
- *(E2:E15) :Lấy Đơn giá ở từng dòng; như vậy phần tích Số lượng * Đơn giá chính là thành tiền của dòng
- (*): Điều kiện và (AND) và đồng thời ép TRUE/FALSE > thành 1/0
Bước 3: Ấn Enter
=> Kết quả cuối cùng: Là tổng doanh thu của nhóm sản phẩm điện thoại và màn hình trong ngày 8/4/2024, trong điều kiện số lượng bán lớn hơn 25.
4. Một số lỗi thường gặp khi dùng hàm SUMPRODUCT tính tổng nhiều điều kiện
Khi sử dụng hàm SUMPRODUCT tính tổng nhiều điều kiện, bạn có thể gặp một số lỗi phổ biến như sau:
- #VALUE!: Do các mảng không cùng kích thước. Để khắc phục: phải đảm bảo các mảng dữ liệu có cùng số dòng, ví dụ B2:B20, C2:C20, D2:D20
- Sai kết quả: Định dạng dữ liệu sai, ví dụ ô chứa số nhưng định dạng text, cần phải chuyển về định dạng đúng
- Để thực hiện các phép toán số học khác trong hàm sumproduct, ta thay dấu (,;) thành các dấu (*, /, +, -)
Kết luận
Hàm SUMPRODUCT là công cụ mạnh mẽ, không chỉ để tính tổng nhiều điều kiện mà còn thay thế nhiều hàm khác trong Excel. So với SUMIFS, nó linh hoạt hơn, đặc biệt trong các trường hợp phức tạp như kết hợp OR/AND, điều kiện theo năm/tháng, hoặc tìm chuỗi con.
