Hàm SUMPRODUCT trong Excel: công cụ tính toán đa năng ít người biết

SUMPRODUCT là gì? Vì sao nên biết hàm này?

SUMPRODUCT là hàm nhân từng cặp phần tử tương ứng giữa hai (hoặc nhiều) vùng dữ liệu, rồi cộng tất cả kết quả lại thành một số duy nhất. Nghe có vẻ đơn giản, nhưng chính cơ chế "nhân theo cặp rồi cộng" này khiến SUMPRODUCT giải quyết được rất nhiều bài toán mà SUMIFS, COUNTIFS còn bó tay — đặc biệt là khi cần nhiều điều kiện phức tạp cùng lúc, hoặc điều kiện là phép so sánh giữa hai cột với nhau.

Điểm mạnh nhất: SUMPRODUCT hoạt động như một "công thức mảng" (array formula) nhưng gõ bình thường, không cần bấm tổ hợp Ctrl + Shift + Enter như các công thức mảng cũ ({=...}). Đây là lý do nhiều người dùng Excel lâu năm vẫn xem SUMPRODUCT là hàm "cứu cánh" khi các hàm điều kiện thông thường không đủ linh hoạt.

Ví dụ dữ liệu dùng xuyên suốt bài — bảng doanh số nhân viên theo tháng:

	A	B	C	D
1	Nhân viên	Phòng ban	Doanh số	Chỉ tiêu
2	An	Kinh doanh	15,000,000	12,000,000
3	Bình	Kinh doanh	9,000,000	12,000,000
4	Cường	Kỹ thuật	8,000,000	10,000,000
5	Dung	Kinh doanh	18,000,000	12,000,000
6	Em	Kỹ thuật	11,000,000	10,000,000

Cú pháp SUMPRODUCT

=SUMPRODUCT(mảng1, [mảng2], [mảng3], ...)

Cách hoạt động cốt lõi: Excel lấy phần tử đầu tiên của mỗi mảng nhân với nhau, phần tử thứ hai của mỗi mảng nhân với nhau, cứ thế đến hết, rồi cộng tất cả các tích lại. Các mảng truyền vào bắt buộc phải cùng kích thước (cùng số dòng, cùng số cột), nếu không sẽ báo lỗi #VALUE!.

Ví dụ đơn giản nhất — không có điều kiện gì, chỉ nhân rồi cộng:

=SUMPRODUCT(C2:C6, D2:D6)

Công thức này tính (15tr×12tr) + (9tr×12tr) + (8tr×10tr) + (18tr×12tr) + (11tr×10tr) — dùng khi cần tính tổng giá trị nhân đơn giá với số lượng cho nhiều dòng cùng lúc, thay vì viết cột phụ rồi mới SUM.

Ví dụ 1: Tính tổng có 2 điều kiện trở lên (thay SUMIFS)

Tính tổng doanh số của phòng "Kinh doanh" và có doanh số vượt chỉ tiêu:

=SUMPRODUCT((B2:B6="Kinh doanh") * (C2:C6>D2:D6) * C2:C6)

Cách đọc công thức: (B2:B6="Kinh doanh") trả về mảng {TRUE;TRUE;FALSE;TRUE;FALSE}, (C2:C6>D2:D6) trả về mảng so sánh doanh số với chỉ tiêu theo từng dòng — đây chính là điều SUMIFS không làm được, vì SUMIFS chỉ so sánh với một giá trị cố định, không so sánh động giữa hai cột. Khi nhân hai mảng TRUE/FALSE này (Excel tự hiểu TRUE = 1, FALSE = 0), dòng nào thỏa cả hai điều kiện mới giữ giá trị 1, còn lại là 0; nhân tiếp với C2:C6 rồi cộng lại ra đúng tổng doanh số của những dòng thỏa cả hai điều kiện — ở đây là An và Dung.

Ví dụ 2: Đếm có điều kiện phức tạp (thay COUNTIFS)

Đếm số nhân viên phòng "Kỹ thuật" có doanh số trên 10,000,000:

=SUMPRODUCT((B2:B6="Kỹ thuật") * (C2:C6>10000000))

Không nhân thêm mảng thứ ba nào cả — bỏ trống phần "nhân với giá trị cần cộng" thì SUMPRODUCT sẽ cộng thẳng các số 0/1, tương đương phép đếm. Kết quả trả về 1 (chỉ Em thỏa cả hai điều kiện, Cường có doanh số 8,000,000 chưa vượt 10,000,000).

Ví dụ 3: Điều kiện dạng OR (hoặc)

SUMIFS xử lý điều kiện "và" (AND) tốt, nhưng điều kiện "hoặc" (OR) giữa nhiều giá trị lại khó viết gọn. Tính tổng doanh số của nhân viên tên "An" hoặc "Bình":

=SUMPRODUCT(((A2:A6="An") + (A2:A6="Bình")) * C2:C6)

Ở đây dùng dấu + (cộng) thay vì * (nhân) giữa hai điều kiện tên — vì phép cộng giữa hai mảng TRUE/FALSE cho ra 1 ở dòng thỏa ít nhất một trong hai điều kiện (giống logic OR), trong khi phép nhân chỉ giữ 1 khi thỏa cả hai cùng lúc (giống logic AND). Đây là điểm dễ nhầm nhất khi mới học SUMPRODUCT: * = và, + = hoặc.

Ví dụ 4: Tính trung bình có điều kiện phức tạp

Kết hợp SUMPRODUCT với phép chia để ra trung bình doanh số của phòng "Kinh doanh":

=SUMPRODUCT((B2:B6="Kinh doanh") * C2:C6) / SUMPRODUCT((B2:B6="Kinh doanh")*1)

Tử số là tổng doanh số phòng Kinh doanh (giống Ví dụ 1 nhưng chỉ 1 điều kiện), mẫu số đếm số dòng thỏa điều kiện đó (nhân với 1 để ép mảng TRUE/FALSE thành số trước khi SUMPRODUCT cộng lại). Cách này hữu ích khi cần trung bình có điều kiện mà AVERAGEIFS không đáp ứng được (ví dụ điều kiện là so sánh động giữa hai cột như Ví dụ 1).

So sánh nhanh SUMPRODUCT với SUMIFS/COUNTIFS

	SUMIFS / COUNTIFS	SUMPRODUCT
Điều kiện đơn giản (so với giá trị cố định)	Rất gọn, dễ đọc	Dài hơn, không cần thiết dùng cho việc đơn giản
So sánh động giữa hai cột (ví dụ cột A > cột B)	Không làm được	Làm được
Điều kiện dạng OR (hoặc)	Phải viết nhiều SUMIFS rồi cộng lại	Viết gọn trong 1 công thức bằng dấu +
Tốc độ tính với bảng dữ liệu rất lớn	Nhanh hơn	Chậm hơn nếu tham chiếu cả cột (ví dụ C:C)
Độ khó khi mới học	Dễ	Cần hiểu cơ chế mảng TRUE/FALSE

Nguyên tắc chọn: điều kiện đơn giản, so với giá trị cố định → dùng SUMIFS/COUNTIFS cho gọn và nhanh. Điều kiện phức tạp, so sánh động giữa các cột, hoặc cần logic OR → chuyển sang SUMPRODUCT.

Lỗi thường gặp

Lỗi #VALUE! Nguyên nhân phổ biến nhất: các mảng truyền vào không cùng kích thước — ví dụ B2:B6 có 5 dòng nhưng C2:C10 có 9 dòng. Luôn kiểm tra lại các vùng tham chiếu có cùng số dòng, cùng điểm bắt đầu và kết thúc.

Công thức chạy rất chậm, làm treo file Do tham chiếu nguyên cột như B:B, C:C thay vì chỉ định vùng cụ thể như B2:B1000. SUMPRODUCT phải xử lý từng dòng trong mảng, tham chiếu cả cột (hơn 1 triệu dòng) khiến Excel tính toán cực kỳ nặng dù dữ liệu thật chỉ có vài trăm dòng.

Kết quả ra số 0 dù chắc chắn có dữ liệu thỏa điều kiện Kiểm tra lại kiểu dữ liệu: nếu cột điều kiện đang là số nhưng được lưu dưới dạng văn bản (text), hoặc có khoảng trắng thừa trong ô, phép so sánh = sẽ không khớp. Có thể kiểm tra nhanh bằng cách bôi đen ô và xem Excel căn số sang phải (là số) hay sang trái (là text).

Nhầm giữa dùng * và + giữa các điều kiện Như Ví dụ 3 đã nói: * cho logic AND (phải thỏa tất cả), + cho logic OR (thỏa một trong các điều kiện). Viết nhầm dấu sẽ cho kết quả sai mà không báo lỗi gì, nên cần kiểm tra lại kết quả bằng cách đếm thủ công vài dòng đầu.

Khi nào nên dùng SUMPRODUCT?

• Cần tính tổng/đếm với điều kiện so sánh động giữa hai cột (ví dụ: "doanh số vượt chỉ tiêu", "giá bán thấp hơn giá vốn").
• Cần điều kiện dạng OR mà không muốn viết nhiều công thức SUMIFS rồi cộng lại.
• Bảng dữ liệu không quá lớn (vài trăm đến vài nghìn dòng) — nếu dữ liệu hàng chục nghìn dòng trở lên, ưu tiên SUMIFS/COUNTIFS hoặc Pivot Table để đảm bảo tốc độ.
• Điều kiện đơn giản, chỉ so với giá trị cố định → vẫn nên dùng SUMIFS/COUNTIFS cho công thức ngắn gọn, dễ người khác đọc hiểu lại.

Học Excel thực chiến tại TP Vinh

SUMPRODUCT là một trong những hàm nâng cao giúp xử lý các bài toán tính tổng, đếm điều kiện mà hàm thông thường không làm được. Nếu bạn muốn học Excel bài bản từ các hàm cơ bản đến kỹ thuật xử lý dữ liệu nâng cao, tham khảo khóa học Excel tại Điền Nguyễn AZ.

📞 Hotline / Zalo: 0362 465 361

👉 Xem chi tiết: Khóa học Excel thực tế tại TP Vinh