Cách sử dụng hàm SUMPRODUCT trong Excel chi tiết, dễ hiểu

Để rút ngắn các thao tác tính toán và xử lý dữ liệu trong Excel, hàm SUMPRODUCT là một trong những công cụ mang lại hiệu quả rõ rệt. Hàm cho phép bạn tính tổng theo nhiều điều kiện mà không cần tạo thêm cột phụ, giúp bảng tính rõ ràng và dễ quản lý hơn. Bài viết dưới đây sẽ hướng dẫn bạn cách sử dụng hàm SUMPRODUCT chi tiết kèm ví dụ để dễ hiểu.

Hướng dẫn sử dụng hàm SUMPRODUCT trong Excel

Hàm SUMPRODUCT là hàm dùng để tính tổng theo nhiều điều kiện trong Excel. Cách hoạt động của hàm là nhân từng phần tử tương ứng giữa hai hoặc nhiều mảng dữ liệu, sau đó cộng tất cả các kết quả lại để cho ra tổng cuối cùng.

Ví dụ: Bạn có một bảng gồm nhiều sản phẩm kèm theo số lượng và đơn giá. Khi muốn tính tổng tiền, bạn thường phải nhân Số lượng × Đơn giá cho từng sản phẩm rồi cộng lại. Hàm SUMPRODUCT cho phép bạn thực hiện cả hai bước này cùng lúc: Nhân từng cặp giá trị tương ứng và tính tổng ngay lập tức.

Ví dụ về hàm SUMPRODUCT

Cú pháp: =SUMPRODUCT(array1; array2; array3; …)

Giải thích:

• array1: Đây là mảng bắt buộc, chứa các giá trị mà hàm sẽ lấy để nhân với những mảng tương ứng khác trước khi cộng tổng.
• array2, array3,…: Là các mảng bổ sung (tối đa đến 225 mảng). Các mảng này cung cấp những giá trị tương ứng để thực hiện phép nhân và sau đó cộng tổng kết quả.

Ví dụ: Giả sử bạn cần tìm tổng số lượng của các mặt hàng Nội thất đồng thời có quà tặng là Thảm trong bảng dữ liệu.

Phân tích điều kiện: Bài toán có 2 điều kiện cần kiểm tra:

• Cột Loại phải là Nội thất
• Cột Quà tặng phải là Thảm

Số lượng cần cộng nằm ở cột H.

Để xử lý hai điều kiện cùng lúc, ta sử dụng hàm SUMPRODUCT kết hợp các phép so sánh logic.

Bước 1: Nhập hàm

Tại ô cần tính tổng, bạn nhập công thức: 

=SUMPRODUCT((D6:D14=D7)*(E6:E14=E8)*H6:H14)

Giải thích:

• (D6:D14 = D7): Kiểm tra từng dòng xem loại sản phẩm có trùng với “Nội thất” không.
• (E6:E14 = E8): Kiểm tra quà tặng của từng dòng có phải “Thảm” không.
• H6:H14: Lấy số lượng tương ứng của những dòng thỏa cả hai điều kiện.

Hai mảng điều kiện đúng/sai được nhân với nhau để tạo thành mảng lọc chính xác, sau đó nhân tiếp với cột số lượng và cộng tổng.

Cách thiết lập công thức SUMPRODUCT với hai điều kiện

Bước 2: Nhấn Enter

Excel sẽ xử lý toàn bộ điều kiện và trả về kết quả ngay. Trong ví dụ này kết quả sẽ là 17. 

Kết quả sau khi nhấn Enter

Ví dụ: Tính tổng số lượng của những sản phẩm thuộc loại “B”, có giá lớn hơn 100 và được bán trong tháng 6. 

Bước 1: Tại ô cần hiển thị kết quả, nhập công thức:

=SUMPRODUCT((A2:A11="B")*(C2:C11>100)*(MONTH(D2:D11)=6)*B2:B11)

Giải thích:

• A2:A11="B": Chọn các dòng có sản phẩm loại B.
• C2:C11>100: Lọc những sản phẩm có giá vượt 100.
• MONTH(D2:D11)=6: Giữ lại các dòng có ngày bán thuộc tháng 6.
• B2:B11: Mảng chứa số lượng sẽ được cộng tổng khi thỏa mãn toàn bộ điều kiện.

Nhập công thức SUMPRODUCT theo nhiều điều kiện

Bước 2: Nhấn Enter để nhận kết quả. Kết quả của ví dụ này là 175.

Kết quả tính tổng nhiều điều kiện bằng SUMPRODUCT

Ví dụ: Tính số lượng sản phẩm thuộc nhóm Phụ kiện hoặc Gia dụng và có quà tặng kèm là Voucher

Bước 1: Để thực hiện, bạn có thể kết hợp SUMPRODUCT với OR để tạo điều kiện lọc hai loại sản phẩm cùng lúc. Công thức được sử dụng là: 

=SUMPRODUCT(((D6:D14=D10)+(D6:D14=D11))*(E6:E14=E11)*H6:H14)

Giải thích:

• ((D6:D14=D10)+(D6:D14=D11)): Sử dụng + để tạo điều kiện OR cho phép tính tổng nếu sản phẩm thuộc Phụ kiện hoặc Gia dụng.
• (E6:E14=E11): Lọc các sản phẩm có quà tặng là Thảm.
• H6:H14: Lấy giá trị số lượng tương ứng.

Lọc hai loại sản phẩm bằng OR

Bước 2: Nhấn Enter, Excel sẽ tính toán và trả về kết quả. Trong ví dụ này kết quả trả về 34

Kết quả tổng số lượng là 34

Khi làm việc với hàm SUMPRODUCT, bạn nên để ý một vài điểm đơn giản sau để tránh lỗi trong quá trình tính toán:

• Hạn chế đưa vào công thức các giá trị như chữ, ký tự hay ô trống vì chúng có thể làm sai kết quả.
• Các mảng dùng trong hàm phải có cùng số hàng và cột để Excel xử lý đúng.
• Nếu trong một mảng có ô chứa văn bản, Excel sẽ tự xem giá trị đó bằng 0 khi thực hiện phép tính.

Lỗi #VALUE! thường xuất hiện khi các vùng dữ liệu trong hàm SUMPRODUCT không có cùng số ô.

Ví dụ, bạn chọn:

• Cột Đơn giá gồm 5 dòng
• Cột Số lượng chỉ có 3 dòng

Khi đó, SUMPRODUCT không thể “ghép cặp” từng ô để nhân, nên trả về lỗi #VALUE!.

Để khắc phục, hãy kiểm tra lại các mảng trong công thức và chỉnh phạm vi sao cho tất cả mảng đều có cùng kích thước.

Ví dụ thông báo lỗi #VALUE! khi sử dụng hàm SUMPRODUCT

Trường hợp không báo lỗi nhưng kết quả lại bằng 0 thường do trong mảng có nhiều giá trị mà hàm không thể dùng để tính toán, chẳng hạn như:

• Ô chứa văn bản
• Ô trống
• Giá trị là hàm logic (TRUE/FALSE)

Chẳng hạn, nếu vùng dữ liệu C4:C8 toàn là chữ (không phải số), khi đưa vào SUMPRODUCT, Excel sẽ xem các giá trị đó là 0, nên tổng cuối cùng cũng bằng 0.

Mảng chứa chữ nên SUMPRODUCT trả về 0

Hàm SUMPRODUCT là công cụ mạnh giúp bạn xử lý các phép tính nhiều điều kiện mà không cần tạo cột phụ, từ đó rút ngắn thời gian và tăng hiệu quả làm việc. Đừng quên theo dõi chuyên mục Kinh nghiệm của Siêu Thị Điện Máy - Nội Thất Chợ Lớn để cập nhật thêm các mẹo Excel và thủ thuật văn phòng hữu ích.

Nếu bạn thường xuyên làm việc với Excel, một chiếc máy tính bảng hoặc điện thoại cấu hình mạnh sẽ giúp thao tác nhanh và linh hoạt hơn. Khám phá ngay các sản phẩm chính hãng, ưu đãi hấp dẫn tại Siêu Thị Điện Máy - Nội Thất Chợ Lớn để tối ưu công việc mỗi ngày.