Tính Tổng Có Điều Kiện

Chúng ta đều đã biết cách tính tổng trong Excel bằng hàm SUM. Vậy còn hàm tính tổng có điều kiện thì sao? Trong bài viết ngày hôm nay, chúng ta hãy thuộc chienlubo.vn tò mò về hàm SUMIFS và cách áp dụng hàm SUMIFS trong Excel để tính tổng nhiều đk nhé.

Bạn đang xem: Tính tổng có điều kiện

Tuyệt đỉnh Excel - phát triển thành bậc thầy Excel trong 16 giờ

Giới thiệu hàm SUMIFS vào Excel

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

Cú pháp hàm SUMIFS

Nếu bạn đã biết hàm SUMIF vào 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 biểu hiện 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 tên. Excel đã chỉ lọc ra các ô tính bao gồm chứa dữ liệu trong phạm vi ô để tính tổng bằng hàm SUMIFS.criteria_range1: Là phạm vi ô tính trước tiên để áp dụng nhận xét theo điều kiện thứ nhất.criteria1: Là điều kiện trước tiên cần đề xuất đáp ứng. Bạn 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 như là 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 mang đến số cặp phạm vi-điều kiện trong phương pháp SUMIFS là 127.

Một điểm đặc biệt củahàm SUMIFSlà vấn đề hàm tất cả thể vận động với hàm AND. Khi chúng ta sử dụng công thứchàm ANDlồng vào hàm SUMIFS, Excel đang chỉ tính tổng các ô tính trong phạm vi sum_range thỏa mãn nhu cầu tất cả những đ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 áp dụng hàm SUMIFS làm cho hàm tính tổng có điều kiện trong Excel qua ví dụ dưới đây nhé.

Giả sử chúng ta có một bảng tổng hợp những lô sản phẩm trái cây được hỗ trợ bởi những nhà đáp ứng khác nhau. Với bảng này, bạn cần tính tổng thể lượng một sản phẩm đến từ một nhà đáp ứng xác định, cụ thể hơn là tính sản lượng táo bị cắn được hỗ trợ bởi Pete. Để có tác dụng được điều này, các bạn sẽ cần thực hiện hàm SUMIFS vào Excel.

*

Hãy cùng bắt đầu thực hành phương pháp tính tổng bởi hàm SUMIFS với bước khẳng định các tham số trong cú pháp hàm, lần lượt như sau:

sum_range: Phạm vi nên tính tổng là cột số lượng hàng hóa (Cột C) C2: C9.criteria_range1: Phạm vi thỏa mãn nhu cầu điều kiện trước tiên là tên mặt hàng (Cột A) A2: A9criteria1: Điều kiện áp dụng cho phạm vi A2:A9 là ô tính có nội dung “apples”criteria_range2: Phạm vi thỏa mãn nhu cầu điều kiện vật dụng hai là tên nhà thuộc ứng (Cột B) B2:B9criteria2: Điều kiện áp dụng cho phạm vi B2:B9 là ô tính có 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ị trên cột C dựa trên hai điều kiện tương ứng: cực hiếm tại cột A là "apples" và quý hiếm tại cột B là "Pete". Công thức chi tiết được diễn đạt như sau:

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


Chúng ta thấy trong bảng chỉ gồm hai ô tính C4 và C9 đáp ứng một cách đầy đủ các đk của hàm SUMIFS. Cũng chính vì vậy, công dụng hàm tính tổng nhiều đk trả về là tổng của hai cực hiếm tại ô C4 và C9.
*

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

Xem thêm: Top Những Món Ăn Tốt Cho Bệnh Xương Khớp Cần Bổ Sung, 7 Thực Phẩm Tốt Cho Người Mắc Bệnh Xương Khớp

Lúc này, phương pháp hàm SUMIFS như sau:

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


Vậy là chúng ta đã xử lý được yêu cầu áp dụng hàm tính tổng nhiều đk của đề bài rồi!

Phân biệt hàm SUMIF cùng hàm SUMIFS

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

Trình tự thông số hàm SUMIF với hàm SUMIFS

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

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

Trái cùng với trường hợp của hàm SUMIF, sum_range là tham số quan lại trọng bậc nhất trong cú pháp hàm SUMIFS. Trước khi khẳng định các cặp phạm vi-điều kiện nhỏ lẻ, chúng ta bắt buộc phải khẳng định phạm vi dữ liệu cần triển khai tính tổng cùng với SUMIFS. Đó là vì sao tại sao sum_range là tham số đứng đầu trong cú pháp hàm SUMIFS vào Excel.

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

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

Giả sử chúng ta có công thức hàm SUMIF:

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

Đây là một trong những công thức hoàn hảo và đang 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 tham số sum_range(ô C2)rồi auto áp dụng đo lường cho phạm vi tương đương kích cỡ của thông số range (A2:A9).


Trong phương pháp này, size tham số criteria2 (B2:B10) không khớp với kích cỡ 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 điệu này.

Cách áp dụng hàm SUMIFS với ví dụ nuốm thể

Tiếp tục với lấy một ví dụ ở những phần trên về bảng tổng phù hợp sản lượng hoa quả và đơn vị cung ứng, trong phần này, chienlubo.vn vẫn hướng dẫn chúng ta cách áp dụng hàm SUMIFS để cách xử trí từng ngôi trường hợp, yêu thương cầu rõ ràng nhé. Cùng thực hành ngay với 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ô hàng hoa quả đến từ nhà cung ứng Mike và thỏa mãn nhu cầu điều kiện những lô hàng bự đó chứa từ 200 sản phẩm trở lên.

Cách làm

Với đề bài bác này, họ sẽ áp dụng hàm SUMIFS vào Excel kết hợp toán tử đối chiếu "lớn hơn hoặc bằng" (>=) để thỏa mãn nhu cầu điều khiếu nại số lượng hàng hóa trong một lô hàng từ 200 đổ lên. ở bên cạnh đó, bọn họ để ý đề bài xích không yêu cầu tính tổng một mặt hàng cụ thể, cần công thức hàm SUMIFS vào trường vừa lòng này như sau:

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

*

Tương từ bỏ như cách làm SUMIFS trên, mang 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 vòng 200 cùng 300, họ sẽ thực hiện hai toán tử "lớn hơn hoặc bằng" (>=) với "nhỏ hơn 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ô sản phẩm được nhập kho trong thời gian 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 thông số criteria1 bắt buộc thể hiện đk "7 ngày gần nhất tính từ thời điểm ngày hôm nay", tức ngày ban đầu tính lô mặt hàng là 7 ngày trước, còn ngày xong là ngày hôm nay. Bởi đó, chúng ta sử dụng hàm TODAY đến tham số criteria1 và trừ thêm 7 để biểu hiện ngày bắt đầu. Tiếp đó, đến tham số criteria2, họ sử dụng hàm TODAY một lần tiếp nữa để ấn định ngày dứt là ngày hôm nay.

Công thức hàm SUMIFSđiều khiếu nại ngày mon trong trường thích hợp này như sau:

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

*


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

Đề bài

Giả sử ngày mua hàng được thể lúc này cột B, ngày nhấn hàng được thể hiện tại cột C, số lượng sản phẩm 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 sẽ đặt nhưng chưa được nhận.

Cách làm

Dựa vào yêu ước đề bài, họ cần tính tổng các giá trị tại cột D đáp ứng điều khiếu nại ô khớp ứng tại cột B (ngày đặt hàng) không hẳn ô rỗng cùng ô khớp ứng tại cột C (ngày thừa nhận hàng) là ô rỗng. Làm cố gắng nào để tiến hành được điều này? họ sẽ so với từng đk nhé.

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

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

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


Ngoài ra, một bí quyết khác là thực hiện kèm hàm SUM để thực hiện thao tác tính tổng vào Excel đối với các ô tính tất cả nội dung ô tính tương xứng khác rỗng: