Hàm SUMPRODUCT trong Excel: Tính tổng tích các giá trị tương ứng

Hàm SUMPRODUCT là một hàm tính cực kỳ hữu ích khi bạn phải xử lý nhiều con số dữ liệu trong Microsoft Excel. Dưới đây là những điều bạn cần biết về hàm SUMPRODUCT trong Excel.

Microsoft Excel là một phần mềm bảng tính tuyệt vời. Lợi ích của nó không ai có thể phủ nhận, nhất là khi nhắc tới các hàm tính. Ở bài viết này, hãy cùng Quantrimang.com tìm hiểu về hàm SUMPRODUCT trong Excel.

Đúng như tên gọi, hàm SUMPRODUCT là sự kết hợp của hàm SUM và PRODUCT trong Excel. Hàm này sẽ trả về tổng số tích của các phạm vi hay mảng tương ứng. Toán tử mặc định ở đây là phép nhân nhưng nó cũng có thể cộng, trừ và chia.

Hàm SUMPRODUCT là gì?

Hàm SUMPRODUCT trong Excel dùng để tính tổng tích các phần tử số trong một mảng hoặc nhiều mảng khác nhau, khác với hàm PRODUCT dùng để tính tích các giá trị.

Mảng trên Excel sẽ là 1 dãy những số liệu với các giá trị liên tiếp mà người dùng khoanh vùng trên bảng dữ liệu. Hàm SUMPRODUCT có trên tất cả các phiên bản Excel, với cách dùng tương tự như nhau. Cách dùng chi tiết hàm SUMPRODUCT trên Excel sẽ được Quản trị mạng hướng dẫn trong bài dưới đây.

Cú pháp hàm SUMPRODUCT

Hàm SUMPRODUCT có cú pháp là =SUMPRODUCT(array1, [array2], [array3], …).

Trong đó:

• Array1: đối số mảng đầu tiên mà người dùng muốn nhân các số liệu rồi tính tổng. Đối số này bắt buộc phải có.
• Array2, Array3: là đối số mảng có thể tùy chọn có hoặc không, trong phạm vi từ 2 đến 255.

Các đối số trong mỗi mảng đều phải có cùng kích thước, cùng số phần tử. Trong trường hợp các phần tử ở các mảng không phải dạng số thì hàm SUMPRODUCT sẽ mặc định coi đó là số 0.

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

• Các đối số mảng phải có cùng số chiều. Nếu không, SUMPRODUCT trả về giá trị #VALUE. Ví dụ, =SUMPRODUCT(C2:C10,D2:D5) sẽ trả về một lỗi bởi các phạm vi không cùng kích thước.
• SUMPRODUCT xử lý các mục mảng không phải số nếu chúng là các số 0.
• Để có kết quả tốt nhất, SUMPRODUCT không được dùng với các tham chiếu cột đầy đủ. Xem xét =SUMPRODUCT(A:A,B:B), hàm này sẽ nhân 1,048,576 ô trong cột A với 1.048.576 ô trong cột B trước khi cộng chúng.

Dùng hàm SUMPRODUCT cơ bản trong Excel

Chúng ta sẽ tính số lượng doanh thu bán sách của 1 cửa hàng trong bảng ví dụ dưới đây. Trong đó, bảng đã cho số lượng từng loại sách và giá thành của từng loại tính theo đơn vị nghìn đồng.

Chúng ta sẽ nhập công thức tính là =SUMPRODUCT(C2:C4,D2:D4) rồi nhấn Enter để thực hiện công thức tính.

Ngay sau đó người dùng sẽ có kết quả doanh thu của cửa hàng khi bán tổng số lượng sách đã cho bên trên.

Dùng hàm SUMPRODUCT có điều kiện

Trong Excel không có hàm SUMPRODUCT kết hợp với hàm IF, nhưng bạn có thể thay đổi lại công thức để tính toán được tổng tích sản phẩm kèm theo điều kiện.

1. Tính tổng các giá trị giống nhau

Chúng ta cần tính giá tiền của sản phẩm Sách Tiếng Việt trong bảng dữ liệu.

Dựa vào công thức hàm SUMPRODUCT, chúng ta có công thức theo yêu cầu là =SUMPRODUCT(B2:B6*C2:C6*(A2:A6="Sách Tiếng Việt")), nghĩa là với sản phẩm Sách Tiếng Việt thì Excel sẽ nhân đơn giá với số lượng tương ứng. Nếu gặp các giá trị khác thì sẽ nhân đơn giá với 0.

Kết quả bạn có được tổng giá tiền của sản phẩm Sách Tiếng Việt dưới đây.

2. Tính tổng giá tiền sách số lượng nhỏ hơn 30

Khi gặp số lượng sách nào nhỏ hơn 30 thì Excel nhân với giá tiền tương ứng, còn nếu lớn hơn 30 thì nhân với 0.

Công thức cụ thể cho điều kiện trên là =SUMPRODUCT((B2:B6<=30)*(B2:B6)*(C2:C6))

Kết quả chúng ta có được tổng giá tiền của những số lượng sách <30.

Một số lỗi của hàm SUMPRODUCT trong Excel

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

Lỗi kết quả bằng 0 do nhiều nguyên nhân, có thể do mảng được chọn có chứa các giá trị mà hàm không tính toán được như hàm logic, văn bản, giá trị trống,...

Khi đó người dùng cần thay đổi mảng giá trị chứa văn bản thành mảng giá trị chứa số.

Lỗi #VALUE

Lỗi xảy ra khi các đối số mảng của bạn không có cùng kích thước.

Chúng ta sẽ cần kiểm tra lại và sửa đối số mảng trong công thức về cùng kích thước.