Tính tổng nhiều điều kiện với hàm SUMPRODUCT trong Excel

Hàm SUMPRODUCT trong Excel là gì? Đây là hàm kết hợp giữa hàm tính tổng - Sum và hàm tính nhân - Product. Vậy cụ thể cách dùng hàm SUMPRODUCT có điều kiện như thế nào. Theo dõi bài viết ngay dưới đây để hiểu rõ câu trả lời nhé!  

Hàm SUMPRODUCT là gì?

Hàm SUMPRODUCT là hàm tính tổng có nhiều điều kiện trong Excel, được kết hợp giữa hàm SUM và hàm PRODUCT . Về cơ bản, nó thực hiện phép nhân giữa các phần tử tương ứng của hai hay nhiều mảng, sau đó tính tổng của các kết quả đó.

Ví dụ: Khi đi mua hàng tại siêu thị, bạn sẽ mua một loạt sản phẩm. Để có thể tính được số tiền cần chi ra cho danh sách đó, bạn cần thực hiện 2 bước:

Bước 1: Mỗi sản phẩm, lấy Đơn giá x Số lượng = A

Bước 2: Đem tất cả các A cộng lại với nhau, ta sẽ có được hóa đơn.

Nếu dùng hàm này, bạn sẽ được kết quả nhanh gọn và trực tiếp hơn cách làm gồm hai bước phía trên.

Ý nghĩa hàm Sumproduct trong Excel ? 

Hàm Sumproduct có khả năng xử lý các mảng và các phép toán nhanh chóng. Ngoài việc tính tổng với nhiều điều kiện, hàm Sumproduct còn có những tính năng khác như:

• Chọn lọc các giá trị.
• Tính toán ở các phạm vi ngắt quãng.
• Kết hợp cùng các hàm khác để xử lý dữ liệu.

Công thức hàm SUMPRODUCT là gì? 

Công thức hàm: = SUMPRODUCT(array1; array2; array3;...)

Trong đó:

• array1: là một mảng đối số bắt buộc phải có nếu muốn nhân các thành phần của nó rồi cộng tổng lại. 
• array2; array3;...: đây là các phạm vi mảng tự chọn từ 2 đến 225, mảng này chứa các thành phần mà bạn muốn nhân rồi cộng tổng.

Lưu ý khi dùng hàm SUMPRODUCT trong Excel

Để sử dụng hàm SUMPRODUCT trong Excel hiệu quả nhất, bạn cần lưu ý các điều sau:

• Trong hàm, tránh dùng các giá trị: hàm logic, ô trống, chữ,...
• Các mảng đối nhau trong công thức phải có cùng số lượng hàng và cột.
• Nếu bất kỳ đối số mảng nào chứa các giá trị không phải là số (ví dụ: văn bản), nó được coi là không. 

Hàm MID trong Excel dùng để làm gì? Hướng dẫn cách sử dụng hàm MID hiệu quả, xem ngay!

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

Dưới đây là các ví dụ tham khảo giúp bạn hiểu rõ hơn về cách dùng hàm Sumproduct. 

Sử dụng hàm SUMPRODUCT cơ bản

Cách sử dụng hàm Sumproduct ở mức cơ bản khá đơn giản, trong công thức bao gồm 2 mảng số liệu.

Ví dụ minh họa: Ta có bảng số liệu sau. Yêu cầu tính tổng thành tiền của tất cả sản phẩm trong bảng dưới đây.

Cách thực hiện:

Bước 1: Ta chọn 2 đối mảng cần tính là Số lượng và Đơn giá. Ta được công thức: =SUMPRODUCT(D4:D12,E4:E12). Cột D4:D12 là Đơn giá và E4:E12 là cột Số lượng. 

Bước 2: Khi dữ liệu được nhập đúng thì kết quả sẽ hiển thị kết quả, nếu sai sẽ báo lỗi. 

Vậy thành tiền của tất cả sản phẩm là 49.800đ. 

Sử dụng hàm SUMPRODUCT có một điều kiện

Với hàm Sumproduct có điều kiện, cách hoạt động tương tự như hàm SUMIF nhưng công thức sẽ đơn giản và dễ hiểu hơn.

Ví dụ minh họa: Tính số lượng các sản phẩm được phân loại là Gia dụng.

Bước 1: Điều kiện “Gia dụng” nằm trong mảng Loại, kết hợp 2 đối mảng ta có công thức: =SUMPRODUCT((D6:D14=D6)*H6:H14).

Ở công thức này, ta lọc giá trị của 2 cột Loại và Số lượng như ví dụ trên. Đề bài yêu cầu mặt hàng “Gia dụng” nên chỉ cần chọn thêm ô D6.

Bước 2: Nhấn “Enter”, màn hình hiển thị kết quả.

Vậy số lượng của các sản phẩm được phân loại Gia dụng là 20.

Sử dụng hàm SUMPRODUCT nhiều điều kiện

Nếu bài toán có từ 2 điều kiện trở lên, ta có thể dùng hàm Sumproduct có điều kiện một cách độc lập hoặc kết hợp với các hàm khác để cho ra kết quả. 2 ví dụ minh họa dưới đây sẽ giúp bạn dễ hiểu hơn.

Ví dụ 1: Tính số lượng của các sản phẩm Nội thất có quà tặng kèm là Thảm.

Bước 1: Ta có 2 điều kiện cần xét, làm tương tự như phép toán có 2 điều kiện. Ta được công thức: =SUMPRODUCT((D6:D14=D7)*(E6:E14=E8)*H6:H14).

Bước 2: Nhấn “Enter”, màn hình hiển thị kết quả.

Vậy 17 sản phẩm Nội thất có quà tặng kèm là Thảm.

Ví dụ 2: Tính số lượng Phụ kiện hoặc Gia dụng có quà tặng kèm là Voucher.

Bước 1: Ở ví dụ này, ta sẽ kết hợp 2 hàm Sumproduct và OR và được công thức =SUMPRODUCT(OR((D6:D14=D10),(D6:D14=D11))*(E6:E14=E11)*(H6:H14)). 

Bước 2: Nhấn “Enter”, màn hình hiển thị kết quả.

Vậy 34 sản phẩm Phụ kiện hoặc Gia dụng có quà tặng kèm là Thảm.

Một số lỗi thường gặp khi sử dụng hàm SUMPRODUCT 

Lỗi #VALUE

Lỗi #Value xảy ra khi 2 mảng dữ liệu không có cùng kích thước. Cụ thể trong ảnh bên dưới, cột Đơn giá có 5 giá trị và cột Số lượng có 3 giá trị. Bạn chỉ cần sửa lại 1 trong 2 mảng để cả hai có cùng kích thước thì hàm sẽ tương ứng.

Lỗi kết quả bằng 0

Lỗi này xuất hiện khi mảng được chọn có chứa các giá trị mà hàm không tính toán được. Cụ thể đó là: hàm logic, văn bản, giá trị trống.

Ví dụ: Trong bảng này, mảng giá trị C4:C8 là văn bản nên kết quả là 0.

Để khắc, phục, bạn chỉ cần thay đổi mảng giá trị chứa văn bản thành mảng giá trị chứa số thì hàm sẽ hiển thị được phép toán.

Kết luận

Với những thông tin trên, hy vọng bạn đã hiểu được cách dùng hàm SUMPRODUCT trong Excel. Đồng thời, tận dụng hàm này trong công việc một cách hiệu quả nhất. Đừng quên đón đọc nhiều thủ thuật văn phòng mới nhất trên website Điện Thoại Vui nhé.