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

Khi chúng ta nghe thương hiệu hàm SUMPRODUCT lượt thứ nhất, nó dường như tương tự như một công thức không có tác dụng tiến hành một tổng số thường thì của hoạt động và sinh hoạt thành phầm. Nhưng với cơ hội chỉ dẫn sau đây thì thực sự hàm sumproduct tính tổng nhiều điều kiện không hề không có tác dụng một chút nào nhé.

Bạn đang xem: hàm sumproduct tính tổng nhiều điều kiện

Video chỉ dẫn sử người 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 ĐK là 1 trong hàm đo lường và tính toán trong những bảng tính nhập học Excel. Nó tiến hành quy tắc nhân trong những thành phần ứng của nhị hoặc nhiều mảng và tiếp sau đó tính tổng của những thành phẩm nhân cơ.

Hàm SUMPRODUCT thông thường được dùng nhằm tính tổng những độ quý hiếm trong những mặt hàng số hoặc nhằm tiến hành những quy tắc tính tương tự động như nhân quỷ trận nhập bảng tính Excel

Ý nghĩa hàm sumproduct nhập excel

Hàm sumproduct tính tổng nhiều điều kiện trong Excel ý nghĩa cần thiết trong công việc tiến hành những đo lường và tính toán phức tạp. Cụ thể, chân thành và ý nghĩa của hàm SUMPRODUCT là:

1. Tính tổng những độ quý hiếm nhân

Hàm sumproduct tính tổng nhiều ĐK được cho phép tính tổng những thành phẩm nhân của những thành phần ứng trong những mảng không giống nhau. Tính năng này tiếp tục khiến cho bạn tính tổng những độ quý hiếm nhân nhập một mặt hàng số hoặc trong những quy tắc tính tương tự động như nhân quỷ trận.

2. sát dụng những tiêu chuẩn thanh lọc dữ liệu

Hàm tính tổng nhiều điều kiện sumproduct nhập Excel hoàn toàn có thể được dùng nhằm tinh lọc những tiêu chuẩn thanh lọc tài liệu và tính tổng những độ quý hiếm phù phù hợp với những ĐK được hướng dẫn và chỉ định. bằng phẳng cơ hội dùng những biểu thức logic như "<", ">", "=", "<=", ">=", "<>", chúng ta có thể thanh lọc tài liệu và tính tổng những độ quý hiếm thỏa mãn nhu cầu những tiêu chuẩn bản thân đề ra.

Hàm SUMPRODUCT nhập Excel hoàn toàn có thể được dùng nhằm tinh lọc những tiêu chuẩn thanh lọc tài liệu và tính tổng những độ quý hiếm phù phù hợp với những ĐK được chỉ định

3. Tính toán bên trên những phạm vi ko liên tục

Một ưu thế của hàm sumproduct tính tổng nhiều điều kiện là được cho phép chúng ta tiến hành đo lường và tính toán bên trên những phạm vi ko liên tiếp nhập một bảng tính. quý khách hàng hoàn toàn có thể hướng dẫn và chỉ định những phạm vi ko liên tiếp và hàm SUMPRODUCT tiếp tục tính tổng những độ quý hiếm ứng trong những phạm vi nhưng mà chúng ta Quanh Vùng.

>>> Xem thêm: Cách dùng hàm SUMIFS nhập Excel tính tổng nhiều ĐK sở hữu ví dụ

4. Kết phù hợp với những hàm không giống nằm trong SUMPRODUCT

Không chỉ người sử dụng riêng biệt lẻ nhưng mà hàm tổng sở hữu ĐK SUMPRODUCT hoàn toàn có thể kết phù hợp với những hàm không giống nhập Excel như IF, SUMIF, COUNTIF và nhiều hàm không giống nhằm tiến hành những đo lường và tính toán phức tạp. bằng phẳng cơ hội phối kết hợp những hàm này, chúng ta có thể đưa đến những công thức uy lực nhằm phân tách tài liệu và tiến hành những đo lường và tính toán phong phú.

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

Tóm lại, hàm nằm trong có rất nhiều ĐK nhập excel sumproduct là 1 trong khí cụ cần thiết nhập Excel nhằm đo lường và tính toán, thanh lọc tài liệu và phân tách tài liệu. Hàm này còn có tính hoạt bát cao, được cho phép người tiêu dùng dùng riêng biệt lẻ hoặc kết phù hợp với một trong những hàm không giống nhằm đạt được thành phẩm bản thân ước muốn.

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

Về mặt mày nghệ thuật, hàm sumproduct tính tổng nhiều điều kiện nhập Excel tiếp tục nhân những số trong những mảng tiếp tục hướng dẫn và chỉ định và trả về tổng của những thành phầm cơ.

Cú pháp của hàm sumproduct sở hữu ĐK nhập Excel cực kỳ giản dị và dễ dàng hiểu:

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

Trong đó:
- array1: là mảng thứ nhất sẽ phải sở hữu nếu còn muốn nhân những bộ phận của chính nó rồi nằm trong tổng lại.

- [array2], [array3] (Mảng 1, mảng 2, v.v.) là những phạm vi dù hoặc mảng liên tiếp tiếp theo sau sở hữu những thành phần mà bạn mong muốn nhân, tiếp sau đó nằm trong tổng vào.

- Số lượng mảng ít nhất là 1 trong những. Trong tình huống này, công thức SUMPRODUCT chỉ giản dị là nằm trong toàn bộ những thành phần mảng và trả về tổng.

- Số lượng mảng tối nhiều là 255 nhập Excel năm 2016, Excel 2013, Excel 2010 và Excel 2007 và 30 trong những phiên bạn dạng Excel trước cơ.

- Mặc cho dù hàm tổng nhiều ĐK SUMPRODUCT hoạt động và sinh hoạt với những mảng, tuy nhiên nó ko đòi hỏi dùng phím tắt mảng ( Ctrl + Shift + Enter).. quý khách hàng tuyên chiến và cạnh tranh một công thức SUMPRODUCT Theo phong cách thường thì bằng phương pháp nhấn phím Enter.

Dùng hàm SUMPRODUCT

Lưu ý:

+ Tất cả những mảng nhập công thức SUMPRODUCT cần sở hữu nằm trong con số mặt hàng và cột, còn nếu như không, các bạn sẽ cảm nhận được #VALUE! lỗi.

+ Nếu ngẫu nhiên đối số mảng này chứa chấp những độ quý hiếm ko cần là số, bọn chúng sẽ tiến hành xem như là số ko.

+ Nếu một mảng là 1 trong bài xích đánh giá logic, nó sẽ bị dẫn theo những độ quý hiếm TRUE và FALSE. Trong đa số những tình huống, bạn phải quy đổi bọn chúng trở nên 1 và 0 bằng phương pháp dùng toán tử đơn nguyên vẹn kép (-).

+ Tổng ăn ý ko tương hỗ những ký tự động đại diện thay mặt.

>> Xem thêm: Cách phối kết hợp hàm sumif và hàm vlookup cụ thể nhất, kèm cặp ví dụ

Ví dụ cơ hội dùng hàm SUMPRODUCT

Giả sử chúng ta sở hữu con số trong những dù C2:C8, giá chỉ trong những dù D2:D8 và mình muốn thăm dò hiểu tổng số. Nếu chúng ta đang khiến bài xích đánh giá toán ở ngôi trường, các bạn sẽ tính tích con số với giá chỉ cho từng mục, tiếp sau đó với những tổng phụ. Trong Microsoft Excel, chúng ta có thể nhận thành phẩm vì thế một công thức SUMPRODUCT duy nhất:
=SUMPRODUCT(C2:C8,D2:D8)

Ví dụ về hàm SUMPRODUCT

Cách dùng hàm SUMPRODUCT

Hiện bên trên sở hữu 3 cơ hội dùng hàm sumproduct nhằm tính tổng những mã như là nhau, tính tổng theo đuổi ĐK và tính tổng theo đuổi ĐK sở hữu kết phù hợp với OR (hoặc). Từng cơ hội rõ ràng như sau:

1. Hàm SUMPRODUCT tính tổng những mã như là nhau

Bạn hoàn toàn có thể sử dụng hàm sumproduct Khi học Excel để tính tổng nhiều ĐK nhằm tính tổng những độ quý hiếm nhập một mặt hàng nếu như ĐK của bọn chúng thỏa mãn nhu cầu.

Ví dụ, mình muốn tính tổng những độ quý hiếm nhập một mặt hàng A1:A11 nếu như bọn chúng như là nhau với độ quý hiếm nhập dù B3, chúng ta có thể dùng công thức sau:

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

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

Hàm tính tổng sở hữu ĐK nhập excel SUMPRODUCT cũng hoàn toàn có thể được dùng nhằm tính tổng những độ quý hiếm nhập một mặt hàng theo đuổi một ĐK.

Ví dụ, mình muốn tính tổng những độ quý hiếm nhập một mặt hàng C2:C7 nếu như bọn chúng to hơn 6, chúng ta có thể dùng công thức sau:

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

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

3. Hàm SUMPRODUCT tính tổng theo đuổi ĐK sở hữu kết phù hợp với OR (hoặc)

Ngoài dùng một ĐK nhằm tính tổng những độ quý hiếm nhập một mặt hàng thì chúng ta có thể phối kết hợp nhiều ĐK cùng nhau. Lúc này, thay cho người sử dụng hàm SUMPRODUCT tính tổng theo đuổi 1 ĐK thì các bạn sẽ người sử dụng hàm SUMPRODUCT phối kết hợp nằm trong OR theo đuổi ví dụ bên dưới đây:

Tính tổng những độ quý hiếm nhập một mặt hàng A1:A10 nếu như bọn chúng to hơn 6 hoặc nhỏ rộng lớn 5, chúng ta có thể dùng công thức sau:

=SUMPRODUCT((C2:C7>6)+(C2:C7<5)*(C2:C7))

Xem thêm: rất xinh đẹp tuyệt vời

Tính tổng nhiều ĐK với SUMPRODUCT

Lưu ý: Trong những công thức hàm sumproduct tính tổng nhiều điều kiện bên trên, tất cả chúng ta dùng toán tử "*" nhằm tiến hành quy tắc nhân và "+" nhằm tiến hành quy tắc nằm trong logic OR. quý khách hàng hoàn toàn có thể kiểm soát và điều chỉnh những phạm vi và ĐK nhập công thức sao mang đến phù phù hợp với ước muốn của tôi.

Cách người sử dụng hàm SUMPRODUCT nâng cao

Hàm SUMPRODUCT cực kỳ hoạt bát, chúng ta có thể dùng hàm này kết phù hợp với một trong những hàm không giống nhằm tiến hành những quy tắc tính phức tạp rộng lớn. Để khiến cho bạn hiểu rộng lớn về kiểu cách người sử dụng hàm sumproduct nhập excel, tại đoạn này, Cửa Hàng chúng tôi tiếp tục lấy một vài ba ví dụ nâng lên như sau:

1. Đếm số thành phầm thỏa mãn nhu cầu ĐK đối chiếu với cùng một số cụ thể

Thông thông thường, các bạn sẽ dùng hàm kiểm điểm sở hữu ĐK COUNTIF nhằm tiến hành kiểm điểm số thành phầm theo đuổi 1 ĐK rõ ràng. Nhưng thay cho người sử dụng COUNTIF, chúng ta có thể người sử dụng công thức sumproduct sở hữu ĐK sau nhằm tiến hành việc này:

= SUMPRODUCT(-(X:Y>Z))

Trong đó:

- Biểu thức (X:Y>Z) nhằm tiến hành đối chiếu từng thành phần của mảng với độ quý hiếm Z

- Sau Khi đối chiếu hoàn thành từng thành phần nhập mảng, hàm Sumproduct thời điểm này tiếp tục phát triển thành dạng: SUMPRODUCT(- {FALSE, FALSE, TRUE, TRUE, FALSE})

Lý vì thế bịa đặt lốt “-” trước mảng với mục tiêu là nhằm đổi mới những độ quý hiếm TRUE/ FALSE về dạng số nguyên vẹn -1 và 0 thì thành phẩm mới nhất hoàn toàn có thể đúng mực. Hoặc chúng ta cũng hoàn toàn có thể dùng 2 lốt “-” nhằm những độ quý hiếm TRUE/ FALSE trả về 1 và 0.

Ví dụ: Đếm số thành phầm kể từ dù C2 cho tới C7 thỏa mãn nhu cầu ĐK to hơn 150

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

Đếm số thành phầm thỏa mãn nhu cầu ĐK đối chiếu với cùng một số cụ thể

2. Đếm số thành phầm Khi đối chiếu 2 mảng cùng theo với nhau

Khi kiểm điểm số thành phầm Khi nằm trong đối chiếu nhị mảng cùng nhau thì chúng ta ko thể dùng hàm COUNTIF. Thay nhập cơ, chúng ta nên người sử dụng hàm sumproduct tính tổng nhiều điều kiện theo đuổi công thức:

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

Trong đó:

- Biểu thức (A:B>C:D) tiếp tục tiến hành đối chiếu từng thành phần của tất cả nhị mảng cùng nhau. Cụ thể, các bạn sẽ đối chiếu A với C, B với D

- Khi đối chiếu hoàn thành những thành phần của cả hai mảng, SUMPRODUCT tiếp tục phát triển thành dạng: SUMPRODUCT(- {TRUE, FALSE, FALSE, FALSE, TRUE})

Lý vì thế bịa đặt lốt “-” trước mảng để lấy hàm về dạng SUMPRODUCT({-1, 0, 0, 0, -1}), thành phẩm vì thế -2. Nếu chúng ta dùng thêm một lốt nữa thì SERP được xem là 2.

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

Đếm số thành phầm Khi đối chiếu 2 mảng cùng theo với nhau

3. Dùng hàm SUMPRODUCT nhiều ĐK để thay thế mang đến hàm COUNTIF

Tính tổng sở hữu 2 ĐK nhập excel là quy tắc toán phổ cập. Để khiến cho bạn nắm rõ rộng lớn về hàm tính tổng nhập excel sở hữu ĐK này, Cửa Hàng chúng tôi tiếp tục lấy ví dụ như rõ ràng về tính chất tổng theo đuổi ĐK nhập excel như sau:

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

Trong đó:

- A4:A10=”táo”: Lọc toàn cỗ độ quý hiếm là “táo”

- C4:C10>100: Lọc những độ quý hiếm to hơn 100

- (A4:A10=”táo”)*(C4:C10>100): Kết ăn ý thanh lọc phối kết hợp những độ quý hiếm là “táo” và to hơn 100

>>> Xem thêm: Bật mí những phím tắt tính tổng nhập excel nhanh chóng, giản dị nhất

Một số lỗi Khi dùng hàm SUMPRODUCT

Khi dùng hàm SUMPRODUCT, các bạn sẽ thông thường bắt gặp một vài ba lỗi như sau:

1. Lỗi #VALUE!

Lỗi này thông thường xẩy ra Khi những phạm vi hoặc mảng không tồn tại nằm trong độ dài rộng. Để tách lỗi này khi sử dụng hàm tính tổng theo đuổi ĐK, bạn phải đảm nói rằng những phạm vi hoặc mảng chúng ta dùng nhập hàm SUMPRODUCT sở hữu nằm trong độ dài rộng.

Lỗi #VALUE! thông thường xẩy ra Khi những phạm vi hoặc mảng không tồn tại nằm trong kích thước

2. Lỗi #DIV/0!

Lỗi #DIV/0! xẩy ra khi chúng ta phân chia một trong những mang đến 0 nhập hàm sumproduct tính tổng nhiều điều kiện. quý khách hàng cần thiết soát lại những quy tắc tính nhập hàm SUMPRODUCT nhằm đáp ứng không tồn tại phân chia mang đến 0 xẩy ra.

3. Lỗi #NAME?

Lỗi này xẩy ra Khi Excel không sở hữu và nhận đi ra thương hiệu hàm SUMPRODUCT vì thế chúng ta viết lách sai chủ yếu mô tả hoặc thiếu hụt chữ nhập kể từ “SUMPRODUCT”. Cách xử lý lỗi này là bạn phải nhập trúng cú pháp và viết lách đúng mực thương hiệu hàm SUMPRODUCT.

Lỗi #NAME? xẩy ra Khi Excel không sở hữu và nhận đi ra thương hiệu hàm SUMPRODUCT vì thế chúng ta viết lách sai chủ yếu mô tả hoặc thiếu hụt chữ nhập kể từ “SUMPRODUCT”

4. Lỗi #REF!

Lỗi #REF! xẩy ra Khi những phạm vi hoặc mảng chúng ta dùng nhập hàm SUMPRODUCT đã trở nên thay cho thay đổi hoặc xóa sổ. Hãy soát lại những phạm vi và mảng nhập hàm SUMPRODUCT nhằm đáp ứng bọn chúng không xẩy ra thay cho thay đổi hoặc xóa sổ đối với khi ban sơ.

5. Lỗi #NUM!

Nguyên nhân dẫn cho tới Lỗi #NUM! là lúc những độ quý hiếm trong những phạm vi hoặc mảng chúng ta dùng nhập hàm SUMPRODUCT ko hợp thức. Cách tách lỗi này là bạn phải đáp ứng những độ quý hiếm chúng ta dùng nhập hàm SUMPRODUCT trúng định hình và phù phù hợp với đòi hỏi của công thức.

6. Lỗi #N/A

Lỗi #N/A xẩy ra lúc không nhìn thấy độ quý hiếm tương thích trong những phạm vi hoặc mảng được dùng nhập hàm SUMPRODUCT. Cách xử lý là bạn phải soát lại những độ quý hiếm và ĐK nhập hàm SUMPRODUCT nhằm đáp ứng nhìn thấy độ quý hiếm tương thích.

Lỗi #N/A xẩy ra lúc không nhìn thấy độ quý hiếm tương thích trong những phạm vi hoặc mảng được dùng nhập hàm SUMPRODUCT

Tóm lại, Khi bắt gặp lỗi trong những lúc người sử dụng hàm SUMPRODUCT, bạn phải soát lại công thức và những phạm vi, mảng, độ quý hiếm được dùng nhập hàm SUMPRODUCT. Đồng thời, hãy đảm nói rằng những đối tượng người dùng được tham ô chiếu nhập hàm SUMPRODUCT không xẩy ra thay cho thay đổi hoặc xóa sổ.

Ngoài những bước nhưng mà Unica tiếp tục share, nếu như bạn gọi mong muốn thăm dò hiểu cụ thể không chỉ có vậy về kiểu cách dùng hàm sumproduct tính tổng nhiều điều kiện nhập Excel thì hoàn toàn có thể tìm hiểu thêm Video bài xích giảng sau:

Xem thêm: cách tắt quảng cáo trên điện thoại samsung

Như vậy, Unica tiếp tục chỉ dẫn chúng ta đọc về hàm hàm SUMPRODUCT và cách tính tổng sở hữu ĐK nhập Excel đúng và nhanh gọn, kỳ vọng nội dung bài viết này sẽ hỗ trợ ích được rất nhiều cho mình nhập quy trình thao tác làm việc sớm hoàn thiện việc làm của tôi. Dường như độc giả quan hoài cho tới chứng chỉ IC3 khiến cho bạn nhập việc làm giống như thăng tiến thủ nhập sự nghiệp, hãy tìm hiểu thêm nhé. Khám đập ngay lập tức cuốn sách Excel kể từ cơ bạn dạng cho tới nâng cao khiến cho bạn bắt vững chắc kiến thức và kỹ năng Excel.

Chúc chúng ta trở nên công!