Bạn đang đọc: Hàm SUMPRODUCT trong Excel – Cách dùng và ví dụ minh họa
Hàm SUMPRODUCT là sự kết hợp của hàm SUM (tính tổng) và hàm PRODUCT (tính tích). Nó giúp bạn nhân tích của từng cặp phần tử rồi cộng tổng chúng lại với nhau.
Vừa nghe qua có vẻ là một hàm rất hữu ích để sử dụng trong việc cần tính tổng doanh thu các mặt hàng tùy đơn giá. Nhưng hàm SUMPRODUCT còn có thể được sử dụng linh hoạt hơn thế.
1. Công dụng của hàm SUMPRODUCT
Một trường hợp đơn giản mà ta thường hay gặp trong đời sống. Khi đi mua sắm với những các loại mặt hàng cùng đơn giá và số lượng khác nhau.
Nếu muốn tính tổng số tiền phải chi trả, cơ bản ta sẽ làm theo 2 bước sau:
– Tính số tiền phải trả cho từng món hàng: lấy tích của Đơn giá với Số lượng.
– Tính toàn bộ số tiền phải trả cho tất cả các món hàng: cộng tổng các tích vừa tính được.
Với phép tính tích của từng món hàng, ta có thể dùng hàm PRODUCT hoặc sử dụng phép tính Đơn giá*Số lượng.
Với phép tính tổng tiền các món hàng, ta có thể dùng hàm SUM.
Nhưng như các bạn thấy đấy, nếu sử dụng cách như trên ta phải đi qua hai bước tính toán. Nếu thực sự không cần thiết với phép tính trung gian lấy tích từng món hàng kia, bạn có thể tính trực tiếp tất cả số tiền phải chi trả bằng hàm SUMPROUDCT.
Công dụng của SUMPRODUCT là sự kết hợp giữa hai hàm tính tích PRODUCT và hàm tính tổng SUM.
2. Cách dùng hàm SUMPRODUCT
Hàm SUMPRODUCT có công thức như sau:
=SUMPRODUCT(array1, array2, array3,…)
Trong đó các array1, 2, 3… là các mảng tham số với array1 chứa các phần tử đầu tiên của phép tính nhân, array 2 chứa các phần tử thứ 2 của phép tính nhân, array 3 chứa các phần tử thứ 3 của phép tính nhân…v.v…
Phần tử đứng đầu của array1 sẽ nhân với phần tử đứng đầu của array2 và nhân với phần tử đứng đầu của array3, cứ lần lượt như thế cho tới hết. Và cuối cùng chúng sẽ được cộng tổng hết lại.
Chính vì điều này nên khi nhập dữ liệu mảng cho hàm SUMPRODUCT, bạn cần phải chú ý các mảng phải có kích cỡ bằng nhau (cùng số hàng và cùng số cột), nếu không hàm sẽ trả về kết quả lỗi: #VALUE!
Tìm hiểu thêm: Hàm YIELDDISC – Hàm trả về lợi tức hàng năm của một chứng khoán chiết khấu trong Excel
Ngoài ra:
Hàm SUMPRODUCT sẽ coi những ô dữ liệu trống hoặc không chứa dữ liệu con số là 0 khi thực hiện phép tính.
3. Một số biến thể về cách dùng hàm SUMPRODUCT
3.1. Hàm SUMPRODUCT 1 điều kiện
Hàm SUMPRODUCT còn có thể sử dụng như một hàm đếm số theo điều kiện.
Với ví dụ sau đây:
Đây là bảng thống kê tiền đầu tư cho các dự án của một công ty và số tiền mà công ty đó thu về được. Vấn đề được đặt ra ở đây là có bao nhiêu dự án đã kiếm được lời cho công ty (doanh thu > vốn), bao nhiêu dự án bị lỗ (doanh thu
Hàm SUMPRODUCT sẽ rất hân hạnh được giúp đỡ bạn trong bài toán này. Lúc này, công thức của hàm sẽ biến thành:
=SUMPRODUCT(–(
Trong đó, thay vì làm phép tính nhân giữa các phần tử tương ứng của mảng thì hàm SUMPRODUCT lúc này sẽ so sánh các phần tử đó với nhau với điều kiện tương tác. Nếu các phần tử đó đúng với điều kiện thì hàm SUMPRODUCT sẽ trả về số 1 còn sai thì sẽ trả về số 0. Sau đó, tất cả các số 1 đó sẽ được cộng lại với nhau để cho ra kết quả cuối cùng.
3.2. Hàm SUMPRODUCT nhiều điều kiện
Bạn cũng có thể sử dụng nhiều điều kiện cùng một lúc cho hàm SUMPRODUCT.
Cùng với ví dụ trên, với điều kiện là tìm số dự án đầu tư có lãi và vốn đầu tư trên 10 tỷ. Ta có hai điều kiện ở đây là Vốn đầu tư 10.
Khác biệt giữa hai phương thức trên đó là dấu phẩy nằm giữa hai vùng điều kiện.
– Khi có dấu phẩy nghĩa là bạn đưa ra điều kiện “và”, cần phải đáp ứng cả hai điều kiện mới cho ra điều kiện đúng.
– Bỏ dấu phẩy đi nghĩa là bạn đưa ra điều kiện “hoặc”, chỉ cần đáp ứng một trong hai điều kiện thì đã là điều kiện đúng.
3.3. Hàm SUMPRODUCT để tính tổng các phần tử phù hợp điều kiện
Ngoài việc đếm số các phần tử trong mảng phù hợp với điều kiện, hàm SUMPRODUCT còn có thể giúp bạn cộng tổng các phần tử thuộc mảng mà phù hợp với điều kiện.
Để hiểu rõ hơn hãy theo dõi ví dụ dưới đây:
>>>>>Xem thêm: Hàm LEN, LENB – Hàm trả về độ dài chuỗi văn bản theo số ký tự hay số byte trong Excel
Tính tổng số vốn đổ vào các dự án lỗ vốn. Để làm được phép tính này, ta cần phải đưa ra điều kiện Vốn > Doanh thu, sau đó tính tổng những dòng Vốn đầu tư phù hợp với điều kiện Vốn > Doanh thu.
Công thức của bài toán này là:
=SUMPRODUCT(–(
Trong đó, phần trước dấu phẩy thì giống với trong phần hàm SUMPRODUCT điều kiện 3.2 và 3.1. Nhưng khác với hai cách dùng ở phần 3.2 và 3.1, thay vì đếm số lượng các phần tử phù hợp với điều kiện thì hàm SUMPRODUCT sẽ cộng tổng các phần tử ở trong mảng chứa dữ liệu tính tổng nằm ở dòng đáp ứng đúng với điều kiện.
Sau khi đọc xong bài viết này, các bạn đã có thêm nhiều hiểu biết về hàm SUMPRODUCT chưa? Hãy cùng comment ở dưới để thảo luận thêm nhé.
Theo dõi excel.net.vn để biết thêm nhiều bài viết bổ ích về Excel!
