Hàm SUMPRODUCT trong Excel Hàm tính tổng của các tích trong Excel

Hàm SUMPRODUCT là hàm Excel kết hợp tính tổng và tính nhân. Hãy cùng Pgdphurieng.edu.vn học cách dùng hàm SUMPRODUCT trong Microsoft Excel nhé!

Hướng dẫn dùng hàm SUMPRODUCT trong Excel

Hàm SUMPRODUCT trong Excel nhân các phạm vi hoặc mảng lại với nhau và trả về tổng các sản phẩm. Nghe có vẻ vô vị nhưng SUMPRODUCT là một hàm cực kỳ linh hoạt, có thể được dùng để đếm và tính tổng giống như COUNTIFS hoặc SUMIFS nhưng linh động hơn. Các hàm Excel khác có thể dễ dàng được dùng trong SUMPRODUCT để mở rộng thêm chức năng.

• Mục đích sử dụng: Nhân, rồi tính tổng các mảng.
• Giá trị trả về: Kết quả của các mảng được nhân và tính tổng.
• Công thức: =SUMPRODUCT (array1, [array2], …). Trong đó:
  • Array1 – Mảng hoặc dãy ô đầu tiên để nhân, rồi cộng.
  • Array2 – (Tùy chọn) Mảng hoặc dãy ô thứ hai cần nhân, sau đó cộng.
• Hàm SUMPRODUCT hỗ trợ Excel 2003 trở lên

Lưu ý khi sử dụng hàm SUMPRODUCT trong Excel

Hàm SUMPRODUCT nhân các dãy ô lại với nhau và trả về tổng số sản phẩm. Nếu chỉ tính một mảng, SUMPRODUCT sẽ chỉ tính tổng các mục trong mảng đó. Tối đa tới 30 dãy hoặc mảng ô.

Khi dùng hàm SUMPRODUCT lần đầu tiên, bạn có thể thấy nó phức tạp, nhạt nhẽo, thậm chí vô nghĩa. Thế nhưng, hàm Excel này cực kỳ linh hoạt với nhiều cách dùng khác nhau. Bởi nó có khả năng xử lý tuyệt vời các mảng. Bạn có thể dùng nó để tính các phạm vi trong ô theo cách thông minh và tinh tế.

Ví dụ về cách dùng hàm SUMPRODUCT trong Excel

Ví dụ hàm SUMPRODUCT “cổ điển” minh họa cách bạn có thể tính tổng trực tiếp mà không cần một cột trợ giúp. Ví dụ, trong worksheet bên dưới, bạn có thể dùng SUMPRODUCT để tính tổng tất cả các số trong cột F mà không cần dùng cột đó:

Để thực hiện phép tính này, SUMPRODUCT dùng các giá trị ở cột D và E trực tiếp như sau:

=SUMPRODUCT(D5:D14,E5:E14) // kết quả: 1612

Kết quả giống như khi bạn tính tổng tất cả các giá trị ở cột F. Công thức được tính như sau:

=SUMPRODUCT(D5:D14,E5:E14)
=SUMPRODUCT({10;6;14;9;11;10;8;9;11;10},{15;18;15;16;18;18;15;16;18;16})
=SUMPRODUCT({150;108;210;144;198;180;120;144;198;160})
=1612

Cách dùng hàm SUMPRODUCT này có thể hữu ích, nhất là khi không có chỗ trống cho cột trợ giúp cùng với một phép tính trung gian. Tuy nhiên, cách dùng phổ biến nhất cho hàm Excel này trong thực tế là áp dụng logic điều kiện ở các tình huống đòi hỏi linh hoạt nhiều hơn các hàm như SUMIFS và COUNTIFS có thể cung cấp.

SUMPRODUCT tính tổng và đếm theo điều kiện

Giả sử bạn có một số dữ liệu theo thứ tự ở A2:B6 với State – Khu vực ở cột A, Sales – Doanh số ở cột B:

Dùng SUMPRODUCT, bạn có thể đếm tổng doanh số ở Texas (TX) như sau:

=SUMPRODUCT(--(A2:A6="TX"))

Bạn có thể tính tổng doanh số bán hàng ở Texas theo công thức:

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

Lưu ý: Hai dấu trừ liên tiếp “—” là mẹo được dùng trong công thức Excel nâng cao để buộc các giá trị TRUE và FALSE thành 1 và 0.

Đối với ví dụ tính tổng ở trên, bảng dưới đây minh họa cho 2 mảng sau khi được xử lý lần đầu bởi hàm SUMPRODUCT:

Bỏ qua các ô trống

Để bỏ qua các ô trống khi dùng SUMPRODUCT, bạn có thể sử dụng một biểu thức như range<>. Ở ví dụ bên dưới, công thức tại F5 & 6 đều bỏ qua các ô ở cột C không chứa giá trị.

=SUMPRODUCT(--(C5:C15<>"")) // đếm
=SUMPRODUCT(--(C5:C15<>"")*D5:D15) // tính tổng

Tóm lại, những điều cần nhớ khi dùng hàm SUMPRODUCT trong Excel:

• SUMPRODUCT xem các mục không phải số trong mảng bằng 0.
• Các đối số mảng phải cùng kích cỡ. Nếu không, hàm SUMPRODUCT sẽ tạo một giá trị lỗi #VALUE.
• Các kiểm tra logic bên trong mảng sẽ tạo giá trị TRUE và FALSE. Ở hầu hết trường hợp, bạn sẽ muốn buộc chúng thành số 1 và 0.
• Hàm SUMPRODUCT thường có thể sử dụng trực tiếp kết quả của các hàm khác.