Hàm tính tổng có nhiều điều kiện

     

Chúng ta đều đã hiểu phương pháp tính tổng trong Excel bởi hàm SUM. Vậy còn hàm tính tổng có đk thì sao? Trong bài viết ngày hôm nay, chúng ta hãy cùng huyenkhuong.com tìm hiểu về hàm SUMIFS và cách thực hiện hàm SUMIFS trong Excel để tính tổng nhiều điều kiện nhé.

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

Tuyệt đỉnh Excel - biến hóa bậc thầy Excel vào 16 giờ

Giới thiệu hàm SUMIFS trong Excel

Như tên thường gọi của nó, hàm SUMIFS trong Excel là hàm ghép từ hàm SUM với hàm IFS. Vị đó, chức năng của hàm SUMIFS cũng chính là sự kết hợp tính năng tính tổng của hàm SUM và công dụng áp dụng nhiều đk cùng dịp cho dữ liệu của hàm IFS. Trường đoản cú đó, bọn họ hiểu rằng SUMIFS là hàm tính tổng nhiều đk trong Excel.

Cú pháp hàm SUMIFS

Nếu chúng ta đã biết hàm SUMIF trong Excel, bạn sẽ thấyhàm SUMIFS là dạng hàm phức tạp hơn của hàm SUMIF. Cú pháp hàm tính tổng theo điều kiện này được miêu tả như sau:

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…)

Trong đó:

sum_range: Là phạm vi ô tính cần thực hiện tính tổng dữ liệu. Phạm vi này rất có thể là một ô duy nhất, một dải ô hoặc những dải ô được để tên. Excel vẫn chỉ lọc ra các ô tính bao gồm chứa tài liệu trong phạm vi ô nhằm tính tổng bởi hàm SUMIFS.criteria_range1: Là phạm vi ô tính trước tiên để áp dụng đánh giá theo đk thứ nhất.criteria1: Là điều kiện trước tiên cần đề xuất đáp ứng. Chúng ta cũng có thể viết tham số đk dưới dạng văn bản, số, hoặc một công thức, một biểu thức hay 1 tham chiếu ô.criteria_range2, criteria2,...: Là những cặp phạm vi-điều kiện khác bạn có thể đưa vào cú pháp hàm. Số lượng giới hạn tối đa cho số cặp phạm vi-điều khiếu nại trong phương pháp SUMIFS là 127.

Một điểm quan trọng củahàm SUMIFSlà vấn đề hàm gồm thể vận động với hàm AND. Khi bạn sử dụng công thứchàm ANDlồng vào hàm SUMIFS, Excel sẽ chỉ tính tổng những ô tính trong phạm vi sum_range thỏa mãn nhu cầu tất cả các điều kiện tương ứng.

Công thức hàm SUMIFS cơ bản


Chúng ta sẽ mày mò cách sử dụng hàm SUMIFS làm hàm tính tổng có đk trong Excel qua ví dụ dưới đây nhé.

Giả sử các bạn có một bảng tổng hợp các lô hàng trái cây được hỗ trợ bởi các nhà cung ứng khác nhau. Với bảng này, bạn phải tính toàn bô lượng một sản phẩm đến xuất phát điểm từ 1 nhà cung ứng xác định, ví dụ hơn là tính sản lượng hãng apple được cung ứng bởi Pete. Để làm được điều này, bạn sẽ cần áp dụng hàm SUMIFS vào Excel.

*

Hãy cùng ban đầu thực hành phương pháp tính tổng bằng hàm SUMIFS cùng với bước xác định các tham số trong cú pháp hàm, thứu tự như sau:

sum_range: Phạm vi yêu cầu tính tổng là cột số số lượng hàng hóa (Cột C) C2: C9.criteria_range1: Phạm vi đáp ứng điều kiện trước tiên là tên món đồ (Cột A) A2: A9criteria1: Điều kiện áp dụng cho phạm vi A2:A9 là ô tính tất cả nội dung “apples”criteria_range2: Phạm vi thỏa mãn nhu cầu điều kiện sản phẩm công nghệ hai là tên gọi nhà cùng ứng (Cột B) B2:B9criteria2: Điều kiện áp dụng cho phạm vi B2:B9 là ô tính bao gồm nội dung “Pete”

Như vậy, cách làm hàm SUMIFS của bọn họ sẽ tính tổng các giá trị tại cột C dựa vào hai đk tương ứng: quý hiếm tại cột A là "apples" và giá trị tại cột B là "Pete". Công thức cụ thể được trình bày như sau:

= SUMIFS (C2: C9, A2: A9, “apples”, B2: B9, “Pete”)


Chúng ta thấy trong bảng chỉ tất cả hai ô tính C4 với C9 đáp ứng đầy đủ các điều kiện của hàm SUMIFS. Bởi vì vậy, công dụng hàm tính tổng nhiều điều kiện trả về là tổng của hai quý giá tại ô C4 và C9.
*

Nếu bạn phải một công thức đơn giản hơn, bạn hoàn toàn có thể viết tham số criteria1 bằng tham chiếu ô F1 đựng giá trị "apples" và viết thông số criteria2 bằng tham chiếu ô F2. Dựa vào đó, bạn có thể áp dụng cách làm SUMIFS trong Excel khi muốn tính toán lượng hoa trái khác tới từ nhà cung ứng khác.

Xem thêm: 【Hướng Dẫn】 Cách Trồng Và Chăm Sóc Các Loại Cây Lan Rừng Phổ Biến Hiện Nay

Lúc này, công thức hàm SUMIFS như sau:

=SUMIFS (C2:C9, A2:A9, F1, B2:B9, F2)


Vậy là chúng ta đã giải quyết được yêu cầu thực hiện hàm tính tổng nhiều điều kiện của đề bài rồi!

Phân biệt hàm SUMIF và hàm SUMIFS

Mục đích của nhị hàm SUMIF với hàm SUMIFS trong Excel phần nhiều là tính tổng cố nhiên điều kiện. Tuy nhiên, cách áp dụng hai hàm này không thể tráo đổi lẫn nhau trong hầu như trường hợp. Để biết đúng mực mình nên thực hiện hàm nào so với bảng dữ liệu đang có, bọn họ cần đọc về điểm giống cùng điểm khác hoàn toàn giữa nhì hàm tính tổng có điều kiện này

Trình tự thông số hàm SUMIF cùng hàm SUMIFS

Khi áp dụng hàm SUMIF với hàm SUMIFS đến trang tính của mình, bạn sẽ để ý rằng trình tự những tham số được hiển thị không giống nhau. Cụ thể hơn, thông số sum_range là tham số thứ nhất trong cú pháp SUMIFS, trong lúc đó chỉ xuất hiện ở vị trí thứ 3 trong cú pháp hàm SUMIF. Vậy tại sao là gì? Liệu tất cả phải Excel đang phức tạp hóa công thức để gia công khó tín đồ dùng?

Để giải đáp thắc mắc này, chúng ta cần phân tách các yếu tố của hai loại hàm tính tổng có đk này. Về phiên bản chất, sum_range là 1 trong những tham số tùy chọn trong cú pháp hàm SUMIF, nghĩa là các bạn hoàn toàn rất có thể không điền tham số này nếu cảm giác không yêu cầu thiết. Hàm vẫn sẽ tiến hành tính tổng trong Excel dựa trên phạm vi tài liệu bạn đã khẳng định trong các tham số trước đó.

Trái với trường vừa lòng của hàm SUMIF, sum_range là tham số quan trọng bậc nhất trong cú pháp hàm SUMIFS. Trước khi xác định các cặp phạm vi-điều kiện nhỏ tuổi lẻ, họ bắt buộc phải khẳng định phạm vi tài liệu cần thực hiện tính tổng cùng với SUMIFS. Đó là tại sao tại sao sum_range là tham số mở đầu trong cú pháp hàm SUMIFS trong Excel.

Kích thước các tham số hàm SUMIF cùng hàm SUMIFS

Trong cú pháp hàm SUMIF, thông số sum_range và tham số range không duy nhất thiết phải gồm cùng kích thước. Ngược lại, sự thống độc nhất trong kích thước các thông số là yếu đuối tố đưa ra quyết định với cách sử dụng hàm SUMIFS. Điều này nghĩa là bạn phải đảm bảo mỗi criteria_range buộc phải chứa thuộc số hàng và số cột với thông số sum_range.

Giả sử bọn họ có phương pháp hàm SUMIF:

=SUMIF(A2:A9,F1,C2:C18)

Đây là một trong công thức hoàn hảo và đã trả về cho bạn kết quả đúng là tổng số liệu trong phạm vi sum_range C2:C18 thỏa mãn nhu cầu điều kiện. Lý do bởi vì Excel chỉ việc xác định ô tính trước tiên trong thông số sum_range(ô C2)rồi tự động áp dụng đo lường và thống kê cho phạm vi tương đương kích thước của thông số range (A2:A9).


Trong công thức này, form size tham số criteria2 (B2:B10) không hợp với size tham số sum_range (C2:C9). Lúc ấn enter, các bạn sẽ thấy Excel trả về lỗi VALUE! bởi vì sự không đồng nhất này.

Cách sử dụng hàm SUMIFS cùng với ví dụ cầm cố thể

Tiếp tục với lấy một ví dụ ở những phần bên trên về bảng tổng đúng theo sản lượng củ quả và đơn vị cung ứng, trong phần này, huyenkhuong.com đang hướng dẫn chúng ta cách sử dụng hàm SUMIFS để xử lý từng ngôi trường hợp, yêu cầu ví dụ nhé. Cùng thực hành ngay với bọn chúng mình thôi!

Công thức hàm SUMIFS cùng với toán tử so sánh

Đề bài

Tính tổng sản lượng các lô sản phẩm hoa quả tới từ nhà cung ứng Mike và thỏa mãn nhu cầu điều kiện các lô hàng lớn đó cất từ 200 thành phầm trở lên.

Cách làm

Với đề bài này, bọn họ sẽ thực hiện hàm SUMIFS trong Excel kết hợp toán tử so sánh "lớn hơn hoặc bằng" (>=) để vừa lòng điều kiện số số lượng hàng hóa trong một lô sản phẩm từ 200 đổ lên. Lân cận đó, họ để ý đề bài bác không yêu cầu tính tổng một mặt hàng cụ thể, nên công thức hàm SUMIFS trong trường thích hợp này như sau:

=SUMIFS(C2:C9, B2:B9, “Mike”, C2:C9, “>=200”)

*

Tương từ như cách làm SUMIFS trên, đưa sử họ cần tính tổng các giá trị vào pham vi C2:C9 vừa lòng điều kiện nằm trong tầm 200 với 300, bọn họ sẽ thực hiện hai toán tử "lớn rộng hoặc bằng" (>=) và "nhỏ rộng hoặc bằng" (

=SUMIFS(C2:C9, C2:C9, “>=200”, C2:C9, “


Công thức hàm SUMIFS với tài liệu ngày

Đề bài

Tính tổng sản lượng hoa quả từ những lô hàng được nhập kho trong thời hạn 7 ngày vừa qua bao gồm ngày hôm nay.

Cách làm

Dựa vào đề bài, họ xác định tham số criteria1 nên thể hiện điều kiện "7 ngày sớm nhất tính từ ngày hôm nay", tức ngày bước đầu tính lô sản phẩm là 7 ngày trước, còn ngày hoàn thành là ngày hôm nay. Vị đó, chúng ta sử dụng hàm TODAY đến tham số criteria1 với trừ thêm 7 để bộc lộ ngày bắt đầu. Tiếp đó, đến tham số criteria2, chúng ta sử dụng hàm TODAY một đợt tiếp nhữa để ấn định ngày chấm dứt là ngày hôm nay.

Công thức hàm SUMIFSđiều kiện ngày tháng trong trường phù hợp này như sau:

=SUMIFS(D2:D10, C2:C10, “>=” và TODAY() – 7, C2:C10, “

*


Công thức hàm SUMIFS với các ô trống

Đề bài

Giả sử ngày đặt đơn hàng được thể bây giờ cột B, ngày dìm hàng được thể hiện tại cột C, số lượng hàng hóa trong một lô hàng đã đặt được thể bây giờ cột D. Tính tổng sản lượng hàng đã đặt nhưng không được nhận.

Cách làm

Dựa vào yêu cầu đề bài, chúng ta cần tính tổng các giá trị tại cột D thỏa mãn nhu cầu điều khiếu nại ô tương ứng tại cột B (ngày để hàng) không hẳn ô rỗng với ô tương xứng tại cột C (ngày dìm hàng) là ô rỗng. Làm rứa nào để thực hiện được điều này? họ sẽ phân tích từng đk nhé.

Công thức hàm SUMIFS cùng với ô không rỗng

Đầu tiên, chúng ta cần tính tổng các giá trị tại cột D với điều kiện ô tương xứng tại cột B không hẳn ô rỗng. Bạn có thể sử dụng cặp cam kết tự đặc trưng "" để xác định điều khiếu nại này. đưa sử bí quyết hàm SUMIFS như sau:

=SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)


Ngoài ra, một cách khác là áp dụng kèm hàm SUM nhằm thực hiện thao tác tính tổng vào Excel đối với các ô tính có nội dung ô tính tương xứng khác rỗng: