Cách Dùng Hàm SUMPRODUCT Tính Tổng Nhiều điều Kiện Trong Excel

Có thể bạn quan tâm

Đăng nhập Đăng ký Kích hoạt khóa học Hội viên Doanh nghiệp Danh mục Ngoại ngữ Marketing Tin học văn phòng Kinh doanh và khởi nghiệp Tài chính & Kế toán Sales Bán hàng Thiết kế & Xây dựng Kỹ năng Công nghệ Sức khoẻ & Làm đẹp Phong cách sống Tình yêu - Hôn nhân & gia đình Sách hay nên đọc Câu chuyện khách hàng Tải app

Hỗ trợ

Hotline: 090 488 6095 Email: cskh@unica.vn Close menu Blog Tin học văn phòng Cách dùng hàm SUMPRODUCT tính tổng nhiều điều kiện trong Excel Mục lục

Khi bạn nghe tên hàm SUMPRODUCT lần đầu tiên, nó có vẻ giống như một công thức vô dụng thực hiện một tổng số thông thường của hoạt động sản phẩm. Nhưng với cách hướng dẫn dưới đây thì thực sự hàm sumproduct tính tổng nhiều điều kiện chứ không hề vô dụng chút nào.

Video hướng dẫn sử dùng hàm sumproduct

Khái niệm hàm sumproduct là gì?

Sumproduct là hàm gì? Hàm sumproduct tính tổng nhiều điều kiện là một hàm tính toán trong các bảng tính trong học Excel. Nó thực hiện phép nhân giữa các phần tử tương ứng của hai hoặc nhiều mảng và sau đó tính tổng của các kết quả nhân đó.

Hàm SUMPRODUCT thường được sử dụng để tính tổng các giá trị

Ý nghĩa hàm sumproduct trong excel

Hàm sumproduct tính tổng nhiều điều kiện trong Excel có ý nghĩa quan trọng trong việc thực hiện các tính toán phức tạp. Cụ thể, ý nghĩa của hàm SUMPRODUCT là:

Tính tổng các giá trị nhân

Hàm sumproduct tính tổng nhiều điều kiện cho phép tính tổng các kết quả nhân của các phần tử tương ứng trong các mảng khác nhau. Tính năng này sẽ giúp bạn tính tổng các giá trị nhân trong một dãy số hoặc trong các phép tính tương tự như nhân ma trận.

Áp dụng các tiêu chí lọc dữ liệu

Hàm tính tổng nhiều điều kiện sumproduct trong Excel có thể được sử dụng để chọn lọc các tiêu chí lọc dữ liệu và tính tổng các giá trị phù hợp với các điều kiện được chỉ định. Bằng cách sử dụng các biểu thức logic như "", "=", "=", "", bạn có thể lọc dữ liệu và tính tổng các giá trị thỏa mãn các tiêu chí mình đặt ra.

Hàm SUMPRODUCT trong Excel được sử dụng để chọn lọc các tiêu chí lọc dữ liệu

Tính toán trên các phạm vi không liên tục

Một điểm mạnh của hàm sumproduct tính tổng nhiều điều kiện là cho phép bạn thực hiện tính toán trên các phạm vi không liên tục trong một bảng tính. Bạn có thể chỉ định các phạm vi không liên tục và hàm SUMPRODUCT sẽ tính tổng các giá trị tương ứng trong các phạm vi mà bạn khoanh vùng.

>>> Xem thêm: Cách sử dụng hàm SUMIFS trong Excel tính tổng nhiều điều kiện có ví dụ

Kết hợp với các hàm khác cùng SUMPRODUCT

Không chỉ dùng riêng lẻ mà hàm tổng có điều kiện SUMPRODUCT có thể kết hợp với các hàm khác trong Excel như IF, SUMIF, COUNTIF và nhiều hàm khác để thực hiện các tính toán phức tạp. Bằng cách kết hợp các hàm này, bạn có thể tạo ra các công thức mạnh mẽ để phân tích dữ liệu và thực hiện các tính toán đa dạng.

Kết hợp SUMPRODUCT với các hàm khác

Tóm lại, hàm cộng có nhiều điều kiện trong excel sumproduct là một công cụ quan trọng trong Excel để tính toán, lọc dữ liệu và phân tích dữ liệu. Hàm này có tính linh hoạt cao, cho phép người dùng sử dụng riêng lẻ hoặc kết hợp với một số hàm khác để đạt được kết quả mình mong muốn.

Công thức tính của hàm SUMPRODUCT

Về mặt kỹ thuật, hàm sumproduct tính tổng nhiều điều kiện trong Excel sẽ nhân các số trong các mảng đã chỉ định và trả về tổng của các sản phẩm đó.

Cú pháp của hàm sumproduct có điều kiện trong Excel rất đơn giản và dễ hiểu:

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

Trong đó:

array1: là mảng đầu tiên bắt buộc phải có nếu muốn nhân các thành phần của nó rồi cộng tổng lại.
[array2], [array3] (Mảng 1, mảng 2, v.v.): là các phạm vi ô hoặc mảng liên tục tiếp theo có các phần tử mà bạn muốn nhân, sau đó cộng tổng vào.

Số lượng mảng 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.

Số lượng mảng tối đa là 255: Trong Excel 2016, Excel 2013, Excel 2010 và Excel 2007 và 30 trong các phiên bản Excel trước đó.

Mặc dù hàm tổng nhiều điều kiện SUMPRODUCT hoạt động với các mảng, nhưng nó không yêu cầu sử dụng phím tắt mảng (Ctrl + Shift + Enter). Bạn cạnh tranh một công thức SUMPRODUCT theo cách thông thường bằng cách nhấn phím Enter.

Dùng hàm SUMPRODUCT

Lưu ý:

Tất cả các mảng trong công thức SUMPRODUCT phải có cùng số lượng hàng và cột, nếu không, bạn sẽ nhận được #VALUE! lỗi.
Nếu bất kỳ đối số mảng nào chứa các giá trị không phải là số, chúng sẽ được coi là số không.
Nếu một mảng là một bài kiểm tra logic, nó sẽ dẫn đến các giá trị TRUE và FALSE. Trong hầu hết các trường hợp, bạn cần chuyển đổi chúng thành 1 và 0 bằng cách sử dụng toán tử đơn nguyên kép (-).
Tổng hợp không hỗ trợ các ký tự đại diện.

>> Xem thêm: Cách kết hợp hàm sumif và hàm vlookup chi tiết nhất, kèm ví dụ

Ví dụ cách sử dụng hàm SUMPRODUCT

Giả sử bạn có số lượng trong các ô C2:C8, giá trong các ô D2:D8 và bạn muốn tìm hiểu tổng số. Nếu bạn đang làm bài kiểm tra toán ở trường, bạn sẽ tính tích số lượng với giá cho mỗi mục, sau đó cộng các tổng phụ. Trong Microsoft Excel, bạn có thể nhận kết quả bằng một công thức SUMPRODUCT duy nhất: =SUMPRODUCT(C2:C8,D2:D8)

Ví dụ về hàm SUMPRODUCT

Cách sử dụng hàm SUMPRODUCT

Hiện tại có 3 cách sử dụng hàm sumproduct để tính tổng các mã giống nhau, tính tổng theo điều kiện và tính tổng theo điều kiện có kết hợp với OR (hoặc). Từng cách cụ thể như sau:

Hàm SUMPRODUCT tính tổng các mã giống nhau

Bạn có thể sử dụng hàm sumproduct khi học Excel để tính tổng nhiều điều kiện để tính tổng các giá trị trong một dãy nếu điều kiện của chúng thỏa mãn.

Ví dụ, bạn muốn tính tổng các giá trị trong một dãy A1:A11 nếu chúng giống nhau với giá trị trong ô B3, bạn có thể sử dụng công thức sau:

=SUMPRODUCT((A1:A11=B3)*(A1:A11))

TẤT CẢ ĐỀU CÓ TRONG CUỐN SÁCH

"HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"

ĐĂNG KÝ MUA NGAY

Hàm SUMPRODUCT tính tổng theo điều kiện

Hàm tính tổng có điều kiện trong excel SUMPRODUCT cũng có thể được sử dụng để tính tổng các giá trị trong một dãy theo một điều kiện.

Ví dụ, bạn muốn tính tổng các giá trị trong một dãy C2:C7 nếu chúng lớn hơn 6, bạn có thể sử dụng công thức sau:

=SUMPRODUCT((C2:C7>6)*(C2:C7))

Dùng hàm SUMPRODUCT để tính tổng theo điều kiện

Hàm SUMPRODUCT tính tổng theo điều kiện có kết hợp với OR (hoặc)

Ngoài sử dụng một điều kiện để tính tổng các giá trị trong một dãy thì bạn có thể kết hợp nhiều điều kiện với nhau. Lúc này, thay vì dùng hàm SUMPRODUCT tính tổng theo 1 điều kiện thì bạn sẽ dùng hàm SUMPRODUCT kết hợp cùng OR theo ví dụ dưới đây:

Tính tổng các giá trị trong một dãy A1:A10 nếu chúng lớn hơn 6 hoặc nhỏ hơn 5, bạn có thể sử dụng công thức sau:

=SUMPRODUCT((C2:C7>6)+(C2:C7Z))

Trong đó:

Biểu thức (X:Y>Z) để thực hiện so sánh từng phần tử của mảng với giá trị Z
Sau khi so sánh xong từng phần tử trong mảng, hàm Sumproduct lúc này sẽ trở thành dạng: SUMPRODUCT(- {FALSE, FALSE, TRUE, TRUE, FALSE})

Lý do đặt dấu “-” trước mảng với mục đích là để biến các giá trị TRUE/ FALSE về dạng số nguyên -1 và 0 thì kết quả mới có thể chính xác. Hoặc bạn cũng có thể sử dụng 2 dấu “-” để các giá trị TRUE/ FALSE trả về 1 và 0.

Ví dụ: Đếm số sản phẩm từ ô C2 đến C7 thỏa mãn điều kiện lớn hơn 150

=SUMPRODUCT(--(C2:C7>150))= 3

Đếm số sản phẩm thỏa mãn điều kiện so sánh với 1 số cụ thể

Đếm số sản phẩm khi so sánh 2 mảng cùng với nhau

Khi đếm số sản phẩm khi cùng so sánh hai mảng với nhau thì bạn không thể sử dụng hàm COUNTIF. Thay vào đó, bạn nên dùng hàm sumproduct tính tổng nhiều điều kiện theo công thức:

=SUMPRODUCT(-(A:B>C:D))

Trong đó:

Biểu thức (A:B>C:D) sẽ thực hiện so sánh từng phần tử của cả hai mảng với nhau. Cụ thể, bạn sẽ so sánh A với C, B với D
Khi so sánh xong các phần tử của cả 2 mảng, SUMPRODUCT sẽ trở thành dạng: SUMPRODUCT(- {TRUE, FALSE, FALSE, FALSE, TRUE})

Lý do đặt dấu “-” trước mảng để đưa hàm về dạng SUMPRODUCT({-1, 0, 0, 0, -1}), kết quả bằng -2. Nếu bạn sử dụng thêm 1 dấu nữa thì kết quả trả về sẽ là 2.

Ví dụ: =SUMPRODUCT(-(D2:D7>C2:C7))=2

Đếm số sản phẩm khi so sánh 2 mảng cùng với nhau

Dùng hàm SUMPRODUCT nhiều điều kiện để thay cho hàm COUNTIF

Tính tổng có 2 điều kiện trong excel là phép toán phổ biến. Để giúp bạn hiểu rõ hơn về hàm tính tổng trong excel có điều kiện này, chúng tôi sẽ lấy một ví dụ cụ thể về tính tổng theo điều kiện trong excel như sau:

=SUMPRODUCT((A4:A10=”táo”)*(C4:C10>100))=2

Trong đó:

A4:A10=”táo”: Lọc toàn bộ giá trị là “táo”
C4:C10>100: Lọc các giá trị lớn hơn 100
(A4:A10=”táo”)*(C4:C10>100): Kết hợp lọc kết hợp các giá trị là “táo” và lớn hơn 100

>>> Xem thêm: Bật mí các phím tắt tính tổng trong excel nhanh, đơn giản nhất

Một số lỗi khi sử dụng hàm SUMPRODUCT

Khi sử dụng hàm SUMPRODUCT, bạn sẽ thường gặp một vài lỗi như sau:

Lỗi #VALUE!

Lỗi này thường xảy ra khi các phạm vi hoặc mảng không có cùng kích thước. Để tránh lỗi này khi dùng hàm tính tổng theo điều kiện, bạn cần đảm bảo rằng các phạm vi hoặc mảng bạn sử dụng trong hàm SUMPRODUCT có cùng kích thước.

Lỗi #VALUE! thường xảy ra khi các phạm vi hoặc mảng không có cùng kích thước

Lỗi #DIV/0!

Lỗi #DIV/0! xảy ra khi bạn chia một số cho 0 trong hàm sumproduct tính tổng nhiều điều kiện. Bạn cần kiểm tra lại các phép tính trong hàm SUMPRODUCT để đảm bảo không có chia cho 0 xảy ra.

Lỗi #NAME?

Lỗi này xảy ra khi Excel không nhận ra tên hàm SUMPRODUCT do bạn viết sai chính tả hoặc thiếu chữ trong từ “SUMPRODUCT”. Cách khắc phục lỗi này là bạn cần nhập đúng cú pháp và viết chính xác tên hàm SUMPRODUCT.

Lỗi #NAME? xảy ra khi Excel không nhận ra tên hàm SUMPRODUCT

Lỗi #REF!

Lỗi #REF! xảy ra khi các phạm vi hoặc mảng bạn sử dụng trong hàm SUMPRODUCT đã bị thay đổi hoặc xóa bỏ. Hãy kiểm tra lại các phạm vi và mảng trong hàm SUMPRODUCT để đảm bảo chúng không bị thay đổi hoặc xóa bỏ so với lúc ban đầu.

Lỗi #NUM!

Nguyên nhân dẫn tới Lỗi #NUM! là khi các giá trị trong các phạm vi hoặc mảng bạn sử dụng trong hàm SUMPRODUCT không hợp lệ. Cách tránh lỗi này là bạn cần đảm bảo các giá trị bạn sử dụng trong hàm SUMPRODUCT đúng định dạng và phù hợp với yêu cầu của công thức.

Lỗi #N/A

Lỗi #N/A xảy ra khi không tìm thấy giá trị phù hợp trong các phạm vi hoặc mảng được sử dụng trong hàm SUMPRODUCT. Cách khắc phục là bạn cần kiểm tra lại các giá trị và điều kiện trong hàm SUMPRODUCT để đảm bảo tìm thấy giá trị phù hợp.

Lỗi #N/A xảy ra khi không tìm thấy giá trị phù hợp trong các phạm vi

Tóm lại, khi gặp lỗi trong lúc dùng hàm SUMPRODUCT, bạn cần kiểm tra lại công thức và các phạm vi, mảng, giá trị được sử dụng trong hàm SUMPRODUCT. Đồng thời, hãy đảm bảo rằng các đối tượng được tham chiếu trong hàm SUMPRODUCT không bị thay đổi hoặc xóa bỏ.

Ngoài những bước mà Unica đã chia sẻ, nếu bạn đọc muốn tìm hiểu chi tiết hơn nữa về cách sử dụng hàm sumproduct tính tổng nhiều điều kiện trong Excel thì có thể tham khảo Video bài giảng sau:

Kết luận

Như vậy, Unica đã hướng dẫn các bạn đọc về hàm hàm SUMPRODUCT và cách tính tổng có điều kiện trong Excel đúng và nhanh chóng, hy vọng bài viết này sẽ giúp ích được nhiều cho bạn trong quá trình làm việc sớm hoàn thành công việc của mình. Bên cạnh đó bạn đọc quan tâm tới chứng chỉ IC3 giúp bạn trong công việc cũng như thăng tiến trong sự nghiệp, hãy tham khảo nhé. Khám phá ngay cuốn sách Excel từ cơ bản đến nâng cao giúp bạn nắm chắc kiến thức Excel.

Chúc các bạn thành công!

Tham khảo thêm: Các khóa học tin học văn phòng được nhiều người học hiện nay

Chia sẻ 1 2 3 4 5 [Tổng số: 221 Trung bình: 3] Tags: Excel Trở thành hội viên

Tác giả Nguyễn Thành Đông Thạc sĩ Thạc sĩ Nguyễn Thành Đông Kiểm toán viên - KPMG (2011). Ứng dụng Microsoft Excel phân tích, xử lý dữ liệu, trình bày báo cáo chuyên nghiệp. Chuyên viên đầu tư (2011) - Công ty...

Bài liên quan

EXP là hàm gì? Cấu trúc và cách sử dụng hàm EXP trong Excel

Hàm AVERAGEIF trong excel: Tính trung bình cộng nhiều điều kiện

Hàm đếm có nhiều điều kiện trong excel COUNTIFS: Công thức và cách dùng

Cách bỏ dấu chấm trong excel mà không phải ai cũng biết

Cách chuyển File Excel sang XML chi tiết nhất

Hướng dẫn chi tiết nhất cách dùng hàm UPPER và PROPER trong Excel Khóa học nổi bật

Sách hướng dẫn thực hành Excel từ cơ bản đến nâng cao

Đội ngũ Unica 198.000đ 268.000đ

Thực hành làm kế toán và lập báo cáo tài chính THỰC TẾ trên phần mềm Excel

Nguyễn Hoàng 5.0 (7) 999.000đ 7.000.000đ

Làm chủ Excel qua 100+ chuyên đề từ cơ bản đến nâng cao

Đinh Hồng Lĩnh 4.8 (32) 499.000đ 600.000đ Chủ đề được quan tâm Phong cách sống Marketing Kinh doanh và khởi nghiệp Thiết kế & Xây dựng Tin học văn phòng Kỹ năng Sức khoẻ & Làm đẹp Bài viết phổ biến