SUMPRODUCT
SUMPRODUCT(vùng_1, [vùng_2], ...)SUMPRODUCT((điều_kiện_1) * (điều_kiện_2) * vùng_số)Nhân từng cặp phần tử của các vùng rồi cộng tất cả lại. Việc thường ngày: tính thành tiền của cả bảng bằng MỘT công thức thay vì tạo cột phụ đơn giá nhân số lượng. Ngoài ra nó còn là công cụ cộng và đếm có điều kiện linh hoạt hơn hẳn SUMIFS, làm được cả điều kiện HOẶC và điều kiện tính toán ngay tại chỗ.
Các đối số
| Đối số | Bắt buộc? | Ý nghĩa |
|---|---|---|
| vùng_1 | Bắt buộc | Vùng đầu tiên. Nếu chỉ có một vùng thì hàm cộng đơn thuần như SUM. |
| vùng_2 | Tuỳ chọn | Các vùng tiếp theo. Mọi vùng phải CÙNG SỐ DÒNG VÀ SỐ CỘT, lệch một dòng là #VALUE! ngay. |
Trả về gì
Một con số. Ô CHỮ và ô trống trong vùng được coi là 0 chứ không gây lỗi.
Ví dụ
=SUMPRODUCT(C2:C500, D2:D500)Tổng thành tiền cả bảng: số lượng nhân đơn giá từng dòng rồi cộng
Kết quả: 1.245.800.000
=SUMPRODUCT((B2:B500="Hà Nội") * (MONTH(A2:A500)=1) * E2:E500)Doanh thu tháng 1 của Hà Nội, điều kiện tháng tính thẳng từ cột ngày mà không cần cột phụ
Kết quả: 94.300.000
=SUMPRODUCT(((B2:B500="Hà Nội") + (B2:B500="Hải Phòng") > 0) * E2:E500)Doanh thu của hai chi nhánh gộp lại — điều kiện HOẶC, thứ SUMIFS không viết được
Kết quả: 268.900.000
Lưu ý khi dùng
- Dấu nhân giữa các điều kiện là VÀ, dấu cộng là HOẶC. Dùng dấu cộng thì nhớ bọc so sánh lớn hơn 0 để một dòng thoả hai điều kiện không bị cộng hai lần.
- Đếm số dòng thoả điều kiện thì bỏ vùng số đi: SUMPRODUCT((B2:B500="Hà Nội") * (E2:E500>10000000)) ra số dòng.
- Chạy được với file nguồn ĐANG ĐÓNG, khác SUMIF và COUNTIF. Đây là lý do nhiều bảng tổng hợp liên file dùng SUMPRODUCT.
- Đừng trỏ cả cột kiểu A:A. Hàm tính trên toàn bộ hơn một triệu dòng cho mỗi ô công thức, bảng vài chục công thức là file bắt đầu ì.
Bẫy hay dính
Viết dấu PHẨY thay vì dấu nhân khi ghép điều kiện logic: SUMPRODUCT((B2:B500="Hà Nội"), E2:E500) trả về 0. Lý do là mảng TRUE FALSE đứng riêng một đối số được coi là giá trị không phải số nên bị tính thành 0. Công thức không lỗi, chỉ ra 0, và người làm thường kết luận nhầm là kỳ này không phát sinh. Chữa: đổi phẩy thành dấu nhân, hoặc thêm hai dấu trừ trước mỗi mảng điều kiện.