Nếu bạn là một người thường xuyên sử dụng Excel thì chắc hẳn đã nghe qua về hàm Sumproduct tính tổng nhiều điều kiện. Tuy nhiên bạn chưa biết cách sử dụng và áp dụng linh hoạt hàm sao cho hợp lý. Hãy cùng khám phá bài viết dưới đây để giúp bạn hiểu rõ chi tiết hơn về hàm Sumproduct cũng như những kiến thức học Excel online chỉ có trên Box.
Xem thêm: Cách dùng hàm Sumifs trong Excel tính tổng nhiều điều kiện
Xem thêm: Hướng dẫn cách dùng hàm MOD trong Excel đơn giản, nhanh chóng
Mục lục bài viết
Hàm SUMPRODUCT tính tổng nhiều điều kiện là gì?
Hàm Sumproduct tính tổng nhiều điều kiện là một hàm cơ bản trong Excel. Hàm này dùng để tính tích các phạm vị dữ liệu hoặc mảng dữ liệu với nhau. Hàm trả về kết quả là tổng số sản phẩm mà người dùng mong muốn tìm kiếm.
Tưởng rằng là một hàm cơ bản, đơn giản nhưng hàm Sumproduct lại sở hữu chức năng cực kỳ linh hoạt để đếm hoặc tính tổng dữ liệu chi tiết hơn. Đối với những người sử dụng chuyên nghiệp Excel thì thường sử dụng hàm Sumproduct này nhiều hơn bởi tính hữu ích, chuẩn xác và nhanh gọn của nó.
Công thức tính hàm SUMPRODUCT tính tổng nhiều điều kiện
Công thức hàm Sumproduct:
= SUMPRODUCT (array1, [array2], [array3],…)
Trong đó:
- array1: Mảng dữ liệu bắt buộc phải có đầu tiên để hàm thực hiện nhân các thành phần đó rồi tính tổng lại
- [array2], [array3],…: phạm vi dữ liệu chứa phần từ mà bạn muốn thực hiện phép tính nhân để tính tổng
Lưu ý:
- Trong công thức hàm Sumproduct yêu cầu tối thiểu là phải có một mảng. Đối với trường hợp này hàm Sumproduct sẽ cộng tất cả các phần tử trong mảng dữ liệu để trả về kết quả.
- Công thức hàm Sumproduct chỉ cho phép tối đa 255 mảng dữ liệu (Trong phiên bản Excel 2016, Excel 2013, Excel 2010 và Excel 2007) và các phiên bản trước đó chỉ giới hạn 30 mảng dữ liệu.
- Hàm Sumproduct không yêu cầu sử dụng phím tắt mảng Ctrl + Shift + Enter.
- Hàm Sumproduct chỉ nhận giá trị trong cùng cột hoặc hàng. Nếu công thức dữ liệu gộp cả cột và hàng thì kết quả trả về sẽ là #VALUE!. Đây là kết quả báo lỗi công thức
- Với các ô dữ liệu là ký tự đặc biệt, văn bản,… (ngoài số liệu) thì hàm Sumproduct sẽ coi đó là số 0 để thực hiện phép tính.
- Đối với các bài kiểm tra logic sử dụng hàm Sumproduct thì kết quả trả về sẽ là TRUE và FALSE. Đối với hầu hết các trường hợp áp dụng hàm bạn cần chuyển đổi chúng thành 1 và 0 để sử dụng công thức tính toán từ đơn nguyên kép hợp lý
- Hàm không hỗ trợ các ký tự đại diện
Ví dụ minh hoạ:
Cách sử dụng hàm SUMPRODUCT tính tổng nhiều điều kiện
Ta có ví dụ minh hoạ dưới đây để giúp bạn hiểu chi tiết hơn về cách sử dụng hàm Sumproduct tính tổng nhiều điều kiện:
Sử dụng hàm Sumproduct với một tiêu chí duy nhất
Bạn muốn sử dụng hàm Sumproduct để thực hiện tính tổng tất cả các số liệu cho tháng sáu
Nhập công thức sau
=Sumproduct((C9:N14)*(C8:N8=6))
Trong đó:
- C9:N14: Cộng tất cả dữ liệu trong vùng dữ liệu từ C9 đến N14
- C8:N8=6: Kiểm tra dữ liệu từng tháng có kết quả nào bằng 6 không.
Kết quả trả về gồm có 1 – True và 0 – False ở hàng thứ 16. Đối với cột H có kết quả True kết quả là 1. Nhân Array1 với Array2 là thực hiện nhân các số thực với 0 hoặc 1. Ở ví dụ này ta thực hiện công thức nhân các cộng các số liệu từ C đến G và từ I đến N nhân với 0, còn cột H nhân với 1. Duy nhất số liệu còn lại được nhân với 1 ở tháng thứ 6 thì được cộng với nhau để nhân với 1 cho ra tổng số doanh thu của tháng 6
Sử dụng hàm Sumproduct làm tiêu chí cho các cột
Thêm vào một phạm vi dữ liệu lớn hơn vào bảng dữ liệu và bạn muốn biết doanh số bán hàng ở quý 2 là bao nhiêu từ tháng 4 đến tháng 6. Thực hiện áp dụng hàm với công thức sau đây:
=SUMPRODUCT(( C9:N14) * (C8:N*>=4) * (C8:N8<=6))
Quý 2 gồm 3 tháng 4,5,6 vì thế bạn chỉ cần tổng dữ liệu trong 3 cột dữ liệu của 3 tháng. Tuy nhiên ở công thức này bạn không cần chỉ định rõ ràng phạm vi ô là F9 đến H14. Mà là chọn vùng dữ liệu trong các tháng lớn hơn hoặc bằng 4 và nhỏ hơn hoặc bằng 6
Bạn cần thêm tháng giới hạn thấp hơn trong ô J4 và giới hạn số liệu trong ô J5. Thực hiện thay đổi công thức theo giới hạn mới thay đổi này. Bạn được công thức như sau:
=SUMPRODUCT ((C9:N14) * (C8:N8>= J14) * (C8:N8<=J5))
Sử dụng hàm Sumproduct làm tiêu chí cho cột và hàng
Công thức này được sử dụng trong ví dụ này để tính doanh thu quý 2 của Lisa. Bạn thực hiện áp dụng công thức sau:
=SUMPRODUCT ((C9:N14) * (C8:N8 >=J4) * (C8:N<=J5) * (B9:B14=M4))
Hàm sẽ tính tổng doanh thu trong 3 tháng 4,5,6 của Lisa bằng cách thực hiện phép nhân các ô F11, G11, H11 với 1 còn lại toàn bộ các số liệu các ô khác nhân với 0 rồi sau đó cộng toàn bộ kết quả trong vùng dữ liệu lại để ra doanh thu của Lisa trong quý 2.
Tổng kết
Hy vọng với những chia sẻ vừa rồi đã giúp bạn hiểu rõ hơn về hàm Sumproduct tính tổng nhiều điều kiện để áp dụng linh động trong quá trình sử dụng Excel. Chúc bạn thành công
