Cách dùng hàm SUMPRODUCT trong Excel: tính tổng tích và đếm theo điều kiện

SUMPRODUCT là hàm Excel nhân các phần tử tương ứng của hai hay nhiều dải ô, rồi cộng tất cả kết quả lại. Nhờ vậy bạn tính được tổng doanh thu (số lượng nhân đơn giá) chỉ bằng một công thức, mà không cần thêm cột phụ. Hàm còn dùng được để tính tổng hoặc đếm theo nhiều điều kiện cùng lúc và tính trung bình có trọng số.

Bài này giải thích cú pháp, các ví dụ thường gặp ở văn phòng, cửa hàng và những lỗi dễ mắc phải. Công thức trong bài dùng dấu phẩy , để ngăn cách đối số. Nếu máy bạn cài theo kiểu dùng dấu chấm phẩy ;, hãy thay dấu , bằng ;. Hàm SUMPRODUCT có sẵn trong hầu hết các phiên bản Excel, kể cả bản cũ.

Cú pháp hàm SUMPRODUCT

=SUMPRODUCT(array1, [array2], [array3], ...)

• array1, array2...: các dải ô có cùng kích thước (cùng số hàng, số cột).
• Hàm nhân các ô cùng vị trí giữa các dải, rồi cộng các tích lại.
• Nếu chỉ có một dải, hàm trả về tổng các ô trong dải đó.

Ví dụ bảng bán hàng: cột B là số lượng, cột C là đơn giá, dữ liệu từ hàng 2 đến hàng 10.

Ví dụ 1: Tính tổng doanh thu

Thay vì tạo cột Thành tiền rồi cộng lại, bạn gõ:

=SUMPRODUCT(B2:B10, C2:C10)

Excel sẽ tính B2C2 + B3C3 + ... + B10*C10. Kết quả chính là tổng doanh thu.

Các bước thực hiện:

1. Chọn một ô trống để chứa kết quả, ví dụ E2.
2. Gõ công thức trên rồi nhấn Enter.
3. Kiểm tra lại bằng cách tính vài dòng thủ công để chắc công thức đúng.

Ví dụ 2: Tổng theo một điều kiện

Giả sử cột A là khu vực, bạn muốn tổng doanh thu của khu vực 'Thái Nguyên':

=SUMPRODUCT((A2:A10="Thái Nguyên")*B2:B10*C2:C10)

Giải thích: phần (A2:A10="Thái Nguyên") trả về TRUE hoặc FALSE cho từng dòng. Khi nhân với số, TRUE thành 1 còn FALSE thành 0, nên chỉ các dòng đúng điều kiện được cộng vào.

Ví dụ 3: Tổng theo nhiều điều kiện

Điều kiện VÀ (AND): nhân các điều kiện với nhau.

=SUMPRODUCT((A2:A10="Thái Nguyên")*(D2:D10="Laptop")*B2:B10*C2:C10)

Công thức này tính doanh thu của sản phẩm 'Laptop' tại 'Thái Nguyên', trong đó cột D là nhóm hàng.

Điều kiện HOẶC (OR): cộng các điều kiện lại.

=SUMPRODUCT(((A2:A10="Thái Nguyên")+(A2:A10="Hà Nội"))*B2:B10*C2:C10)

Công thức này tính doanh thu của hai khu vực. Với dữ liệu mà một dòng không thể thuộc cả hai điều kiện thì cách cộng này cho kết quả đúng.

Ví dụ 4: Đếm số dòng theo điều kiện

Để đếm số đơn của 'Thái Nguyên' có số lượng lớn hơn 5:

=SUMPRODUCT(--(A2:A10="Thái Nguyên"), --(B2:B10>5))

Hai dấu trừ -- đổi TRUE/FALSE thành 1/0 để hàm cộng lại được. Với các trường hợp đơn giản hơn, bạn có thể dùng hàm COUNTIFS, nhưng SUMPRODUCT linh hoạt hơn khi điều kiện phức tạp.

Ví dụ 5: Trung bình có trọng số

Tính điểm trung bình với cột B là điểm, cột C là hệ số:

=SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6)

Tử số là tổng điểm nhân hệ số, mẫu số là tổng hệ số. Đây là cách tính điểm tổng kết rất hay gặp.

Ví dụ 6: Tính theo tháng hoặc năm

Nếu cột A là ngày bán, muốn tổng doanh thu tháng 3:

=SUMPRODUCT((MONTH(A2:A10)=3)*B2:B10*C2:C10)

Lưu ý ngày phải là dữ liệu dạng ngày thực sự của Excel, không phải văn bản.

Những lỗi thường gặp

1. #VALUE!: hai dải có kích thước khác nhau, ví dụ B2:B10 và C2:C11. Hãy kiểm tra cho các dải bằng nhau.
2. Kết quả bằng 0: điều kiện không khớp do thừa dấu cách, sai chính tả hoặc số bị lưu dạng văn bản. Thử dùng hàm TRIM để loại khoảng trắng thừa.
3. **Dùng dấu * với ô chứa chữ:** nếu dải số có ô chứa văn bản và bạn dùng phép nhân, Excel báo #VALUE!. Khi truyền các dải bằng dấu phẩy, ô văn bản được coi là 0.
4. Chạy chậm: dùng dải quá lớn (hàng chục nghìn dòng, nhiều điều kiện) sẽ làm file chậm. Hãy giới hạn dải đúng phạm vi dữ liệu.
5. Dải toàn cột như A:A: có thể làm chậm hoặc báo lỗi trong SUMPRODUCT. Nên dùng dải cụ thể hoặc dùng Bảng (Table).

Khi nào dùng SUMPRODUCT, khi nào dùng hàm khác?

• SUMIFS, COUNTIFS: đơn giản và nhanh hơn khi chỉ cần tổng hoặc đếm theo điều kiện trên một cột kết quả có sẵn.
• SUMPRODUCT: phù hợp khi cần nhân hai cột rồi cộng (không có cột thành tiền), hoặc điều kiện phức tạp, điều kiện theo tháng, theo năm.
• XLOOKUP, VLOOKUP: dùng để tra cứu giá trị, xem cách dùng hàm VLOOKUP và XLOOKUP.
• Nếu bạn mới làm quen với Excel, hãy xem các hàm Excel cơ bản dân văn phòng cần biết trước.

Với file có nhiều dữ liệu, bạn cũng nên biết cách lọc và sắp xếp dữ liệu trong Excel để kiểm tra kết quả nhanh.

Cần hỗ trợ thêm?

Nếu file Excel của bạn chạy nặng và chậm, có thể máy cần thêm RAM hoặc ổ SSD. Cường Tech có thể kiểm tra miễn phí và tư vấn nâng cấp, xem thêm cấu hình PC kế toán chạy MISA, Excel nặng mượt. Bạn cũng có thể gọi hotline/Zalo 0943.847.333, hoặc dùng Build PC để chọn linh kiện tương thích.