Buzz
Hàm SUMPRODUCT là một hàm trong Excel kết hợp tính tổng và tính nhân. Hãy cùng Mytour tìm hiểu cách sử dụng hàm này trong Microsoft Excel nhé!
Hướng dẫn cách sử 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à tính tổng các sản phẩm. Mặc dù có vẻ như một hàm đơn giản, nhưng SUMPRODUCT thực sự rất linh hoạt và có nhiều ứng dụng khác nhau.
- Mục đích sử dụng: Nhân các mảng rồi tính tổng.
- Giá trị trả về: Tổng của các sản phẩm của các mảng được nhân.
- Công thức: =SUMPRODUCT (mảng1, [mảng2], ...). Trong đó:
- Mảng1 - Mảng hoặc dãy ô đầu tiên cần nhân và tính tổng.
- Mảng2 - (Tùy chọn) Mảng hoặc dãy ô thứ hai cần nhân và tính tổng.
- Hàm SUMPRODUCT được hỗ trợ từ Excel 2003 trở lên.
Những điều cầ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à tính tổng số sản phẩm. Nếu chỉ có một mảng, SUMPRODUCT sẽ tính tổng các mục trong mảng đó. Tối đa có thể sử dụng 30 dãy hoặc mảng ô.
Ban đầu, có thể bạn sẽ thấy hàm SUMPRODUCT phức tạp và không có ý nghĩa. Tuy nhiên, đây là một hàm rất linh hoạt và có nhiều cách sử dụng khác nhau. SUMPRODUCT có thể xử lý các mảng một cách thông minh và tinh tế.
Ví dụ về cách sử dụng hàm SUMPRODUCT trong Excel
Ví dụ về hàm SUMPRODUCT “cổ điển” minh họa cách tính tổng trực tiếp mà không cần một cột trợ giúp. Ví dụ, bạn có thể sử dụng SUMPRODUCT để tính tổng tất cả các số trong cột F mà không cần sử dụng cột đó:
Để tính tổng và nhân các giá trị, hàm SUMPRODUCT sử dụng các giá trị từ cột D và E trực tiếp như sau:
=SUMPRODUCT(D5:D14, E5:E14) // kết quả: 1612Kế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) // kết quả: 1612Cách sử dụng hàm SUMPRODUCT này có thể hữu ích, đặc biệt khi không có cột trợ giúp và phép tính trung gian. Tuy nhiên, cách thường dùng nhất là áp dụng logic điều kiện trong các tình huống linh hoạt hơn so với SUMIFS và COUNTIFS.
Hàm SUMPRODUCT tính tổng và đếm theo điều kiện
Giả sử bạn có dữ liệu theo thứ tự ở A2:B6 với Khu vực ở cột A và Doanh số ở cột B:
| A | B | |
| 1 | State | Sales |
| 2 | UT | 75 |
| 3 | CO | 100 |
| 4 | TX | 125 |
| 5 | CO | 125 |
| 6 | TX | 150 |
Sử 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 sau:
=SUMPRODUCT(--(A2:A6='TX'), B2:B6)Chú ý: Hai dấu trừ liên tiếp “--” là mẹo trong công thức Excel để chuyển đổi 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:
| array1 | array2 |
| FALSE | 75 |
| FALSE | 100 |
| TRUE | 125 |
| FALSE | 125 |
| TRUE | 150 |
Bỏ qua các ô trống
Để loại bỏ các ô trống khi sử dụng SUMPRODUCT, bạn có thể dùng một biểu thức như range<>. Trong ví dụ dưới đây, công thức ở F5 & 6 đều loại bỏ 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 ghi nhớ khi sử dụng hàm SUMPRODUCT trong Excel:
- SUMPRODUCT xem các mục không phải số trong mảng như số 0.
- Các đối số mảng phải có cùng kích thước. Nếu không, hàm SUMPRODUCT sẽ tạo ra một giá trị lỗi #VALUE.
- Các kiểm tra logic bên trong mảng sẽ tạo ra giá trị TRUE và FALSE. Thông thường, bạn muốn chuyển 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.
