Hàm SUMPRODUCT trong Excel - Cách dùng và ví dụ minh họa

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.

Hóa đơn mua hàng

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ổng tiền các món hàng thì dùng hàm SUM

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.

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,…)

Công thức Hàm SUMPRODUCT

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.

Các mảng tham số

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!

Kết quả lỗi #VALUE! khi các mảng không cùng kích cỡ

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.

Hàm SUMPRODUCT coi những ô dữ liệu trống hoặc không chứa dữ liệu con số là 0

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:

Hàm SUMPRODUCT 1 điều kiện

Đâ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 < vốn) và có có bao nhiêu dự án hòa vốn (doanh thu = vốn).

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(--(<mảng 1><điều kiện tương tác><mảng 2>))

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ư < Doanh thu và Vốn >10.

Điều kiện Vốn đầu tư < Doanh thu và Vốn > 10 tỷ

Với ví dụ trên, hàm SUMPRODUCT sẽ tìm các kết quả thỏa mãn cả tất cả các điều kiện được đưa ra. Khác với cách dùng sẽ chia sẻ dưới đây, kết quả chỉ cần thỏa mãn với một trong số các điều kiện.

Kết quả chỉ cần thỏa mãn với một trong số các điều kiện

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:

Hàm SUMPRODUCT để tính tổng các phần tử phù hợp điều kiện

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(--(<mảng 1><điều kiện tương tác><mảng 2>),<mảng chứa dữ liệu tính tổng>)

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!

Viết bình luận