Hàm SUMPRODUCT tính tổng nhiều điều kiện trong Excel
Trong bài viết hôm nay, Kienit.com sẽ chia sẻ với các bạn về cách dùng hàm SUMPRODUCT tính tổng nhiều điều kiện trong Excel. Đây là một hàm rất tiện dụng trong việc tính toán doanh thu các mặt hàng.
1/ Hàm SUMPRODUCT là gì?
SUMPRODUCT trong Excel sự kết hợp giữa hàm SUM và hàm PRODUCT dùng để tính tổng tích các phần tử số trong một mảng hoặc nhiều mảng khác nhau.
Hàm SUMPRODUCT được sử dụng trên mọi phiên bản Excel. Trên thực tế thì đây là hàm được sử dụng phổ biến.
2/ Cách dùng hàm SUMPRODUCT
Cú pháp là =SUMPRODUCT(array1, [array2], [array3], …).
Trong đó:
Array1: đối số mảng đầu tiên mà người dùng muốn nhân các số liệu rồi tính tổng. Đối số này bắt buộc phải có.
Array2, Array3: là đối số mảng có thể tùy chọn có hoặc không.
Lưu ý:
Số lượng mảng trong công thức hàm SUMPRODUCT tối thiểu là 1. Trong trường hợp này, công thức SUMPRODUCT chỉ đơn giản là cộng tất cả các phần tử mảng và trả về tổng.
Khi dùng Excel 2007 hoặc các phiên bảng 2010, 2013, 2016 thì giới hạn mảng tối đa là 255 còn với Excel 2003 là tối đa chứa 30 mảng.
Mặc dù là SUMPRODUCT hàm có chưa mảng nhưng bạn không cần dùng phím tắt Ctrl + Shift + Enter.
Các đối số trong mảng phải cùng số hàng số cột. Nếu bạn tính tổng theo điều kiện nhưng số hàng và cột không bằng nhau thì hàm SUMPRODUCT sẽ báo lỗi #VALUE!
Các ô dữ liệu nếu không có con số cụ thể thì sẽ coi đó là số 0.
Để hiểu sâu hơn về cách sử dụng hàm SUMPRODUCT thì mời bạn xem các ví dụ dưới đây.
Cách dùng hàm cơ bản
Nếu như bạn nào chưa biết SUMPRODUCT là hàm gì thì sẽ thực hiện tính toán mua hàng bằng cách thủ công như sau
Tính tổng hàng cơ bản
Bạn sẽ nhân đơn giá với số lượng để ra được giá từng món hàng sau đó cộng lại tất cả giá các mặt hàng bằng hàm SUM hoặc dùng phím tắt tính tổng Alt +=. Còn nếu như bạn sử dụng hàm SUMPRODUCT thì sẽ hơn rất nhiều
Công thức là: =SUMPRODUCT(D4:D8,E4:E8)
Ví dụ cách sử dụng hàm SUMPRODUCT
Bởi vì số cột 2 bên mảng đều nhau nên hàm sẽ lấy giá nhân với số lượng và sau đó cộng lại hết các giá trị: (100*12000) + (54*56000) + (38*400) + (94*9000) +(61*44000) = 7769200
Lỗi #VALUE! trong hàm SUMPRODUCT
Một lỗi rất phổ biến trong trường hợp dùng hàm SUMPRODUCT đó chính là các bạn lấy bảng không cùng số lượng hàng và cột dẫn tới bị lỗi #VALUE!
Lỗi #VALUE!
Nếu như trong trường hợp phần tử có giá trị bằng 0 trong mảng được chọn thì hàm vẫn thực hiện đến kết quả. Hàm sẽ thực hiện công thức toán: (100*12000) +(54*56000) + (38*400) + (94*9000) + (0*44000) = 5085200
Trường hợp phần tử có giá trị bằng 0 trong mảng
Hàm SUMPRODUCT có điều kiện
Trong ví dụ này, mình sẽ có 3 loại điều kiện (<, = , >)
Vốn nhỏ hơn doanh thu: =SUMPRODUCT(–(D4:D8<E4:E8))
Vốn bằng doanh thu: =SUMPRODUCT(–(D5:D9=E5:E9))
Vốn lớn hơn doanh thu: =SUMPRODUCT(–(D6:D10>E6:E10))
Cách dùng hàm SUMPRODUCT có điều kiện
Thay vì hàm thực hiện phép tính nhân giữa các phần tử với nhau thì khi có điều kiện, hàm Excel này sẽ so sánh các phần tử đó dựa trên điều kiện. Hàm sẽ đếm có bao nhiêu kết quả thỏa điều kiện để đưa ra kết quả cuối cùng.
Lưu ý: Khi dùng hàm PRODUCT có điều kiện thì bạn cần thêm dấu “–” trước mảng, như vậy hàm mới thực hiện được, còn không sẽ ra giá trị = 0.
Hàm SUMPRODUCT tính tổng nhiều điều kiện
Nếu như nhiều điện thì sẽ có thêm dấu “–” vào trước mỗi điều kiện.
Điều kiện trong ví dụ này: Điều kiện:Tổng dự án có vốn > 100 và vốn < 10
Công thức: SUMPRODUCT(–(D4:D8>100),–(D4:D8<10))
Trong công thức có dấu phẩy là toán tử AND
Công thức: SUMPRODUCT(–(D4:D8>100)–(D4:D8<10))
Trong công thức có dấu phẩy là toán tử OR
Bạn thấy qua 2 ví dụ trên có sự khác nhau ở công thức cho ra kết quả khác nhau đúng không? Điều tạo nên sự khác biệt ở đây đó chính là dấu phẩy.
- Nếu có dấu phẩy đó là điều kiện “VÀ” (AND).Kết quả bằng 0 cho thấy không có dòng nảo thỏa cả 2 điều kiện.
- Nếu không có dấu phảy thì là điều kiện “HOẶC” (OR). Kết quả bằng 1 có nghĩa có 1 dòng thỏa 1 trong 2 điều kiện đã đưa ra (là dự án B).
Vì vậy khi sử dụng hàm SUMPRODUCT tính tổng nhiều điều kiện bạn cần để ý đến dấu phẩy.
Tính tổng các phần tử hợp điều kiện
Một ứng dụng khác của hàm này đó chính là cộng tổng các phần tử thuộc mảng đã thỏa điều kiện. Để hiểu rõ hơn mời bạn xem qua ví dụ sau đây.
Điều kiện: Điều kiện:Tổng dự án có vốn < 100 và vốn > 10
Công thức: =SUMPRODUCT(–(D4:D8<100),–(D4:D8>10),D4:D8)
Tính tổng các phần tử hợp điều kiện
Trong đó:
–(D4:D8<100),–(D4:D8>10) sẽ tìm các giá trị thỏa điều kiện. Ở đây có 4 kết quả (50, 11, 22, 44).
D4:D8 sẽ cộng 4 giá trị đã thỏa lại để ra kết quả cuối cùng là 127.
Nếu như các bạn muốn tìm hiểu các kiến thức nâng cao hơn thì hãy tham khảo thêm 11 ví dụ hay về hàm SUMPRODUCT trong Exel tại đây. Nguồn bằng tiếng Anh nên vừa học các bạn vừa dịch ra tiếng Việt để dễ hiểu hơn.
Trên đây là toàn bộ những kiến thức về cách sử dụng hàm SUMPRODUCT trong Excel. Còn rất nhiều kiến thức hay ho, bạn hãy truy cập vào chuyên mục học Excel để cập nhật các kiến thức mới mỗi ngày. Chúc các bạn sẽ thành công.
Bài viết xem thêm:
Phím tắt AUTOSUM giúp bạn tính tổng nhanh đến 90%
Hàm SUMIFS là gì? Cách dùng hàm tính tổng nhiều điều kiện
Hướng dẫn cách sử dụng hàm SUMIF từ cơ bản đến nâng cao
Hàm SUMIF là gì? Cấu trúc và cách sử dụng hàm SUMIF trong excel
Hàm SUM trong excel là gì? Cấu trúc và các ví dụ về hàm SUM
What is your reaction?
