✅ Hàm SUMIF & SUMIFS - Tổng giá trị Nếu - Excel & Google Trang tính

Mục lục

Tổng quan về hàm SUMIF
Cú pháp và đối số của hàm SUMIF:
Hàm SUMIF là gì?
SUMIF trong Google Trang tính

Hướng dẫn này trình bày cách sử dụng Excel SUMIF và SUMIFS Chức nănghàng tấn trong Excel và Google Trang tính để tổng hợp dữ liệu đáp ứng các tiêu chí nhất định.

Tổng quan về hàm SUMIF

Bạn có thể sử dụng hàm SUMIF trong Excel để tính tổng các ô chứa một giá trị cụ thể, tính tổng các ô lớn hơn hoặc bằng một giá trị, v.v.

(Chú ý cách các đầu vào công thức xuất hiện)

Cú pháp và đối số của hàm SUMIF:

1	= SUMIF (phạm vi, tiêu chí, [sum_range])

phạm vi - Phạm vi ô mà bạn muốn áp dụng tiêu chí.

tiêu chuẩn - Các tiêu chí được sử dụng để xác định ô nào cần thêm vào.

sum_range - [tùy chọn] Các ô để thêm cùng nhau. Nếu sum_range bị bỏ qua, các ô trong phạm vi sẽ được thêm vào với nhau.

Hàm SUMIF là gì?

Hàm SUMIF là một trong những hàm cũ hơn được sử dụng trong bảng tính. Nó được sử dụng để quét qua một loạt các ô kiểm tra một tiêu chí cụ thể, sau đó cộng các giá trị trong một phạm vi tương ứng với các giá trị đó. Hàm SUMIF ban đầu chỉ được giới hạn ở một tiêu chí. Sau năm 2007, hàm SUMIFS được tạo ra cho phép vô số tiêu chí. Hầu hết cách sử dụng chung vẫn giống nhau giữa hai loại, nhưng có một số khác biệt quan trọng trong cú pháp mà chúng ta sẽ thảo luận trong suốt bài viết này.

Nếu bạn chưa làm như vậy, bạn có thể xem lại nhiều cấu trúc và ví dụ tương tự trong bài viết COUNTIFS.

Ví dụ cơ bản

Hãy xem xét danh sách doanh số được ghi lại này và chúng tôi muốn biết tổng thu nhập.

Bởi vì chúng tôi đã có một khoản chi phí, giá trị âm, chúng tôi không thể chỉ tính một khoản tiền cơ bản. Thay vào đó, chúng tôi chỉ muốn tính tổng các giá trị lớn hơn 0. “Lớn hơn 0” là tiêu chí của chúng tôi trong một hàm SUMIF. Công thức của chúng tôi để xác định điều này là

1	= SUMIF (A2: A7, "> 0")

Ví dụ về hai cột

Mặc dù hàm SUMIF ban đầu được thiết kế để cho phép bạn áp dụng tiêu chí cho phạm vi số bạn muốn tính tổng, nhưng phần lớn thời gian bạn sẽ cần áp dụng một hoặc nhiều tiêu chí cho các cột khác. Hãy xem xét bảng này:

Bây giờ, nếu chúng tôi sử dụng hàm SUMIF ban đầu để tìm xem chúng tôi có bao nhiêu Chuối (được liệt kê trong ô D1), chúng tôi sẽ cần cung cấp phạm vi mà chúng tôi muốn Tổng là đối số cuối cùng và vì vậy công thức của chúng tôi sẽ là

1	= SUMIF (A2: A7, D1, B2: B7)

Tuy nhiên, khi các lập trình viên cuối cùng nhận ra rằng người dùng muốn đưa ra nhiều hơn một tiêu chí, thì hàm SUMIFS đã được tạo ra. Để tạo một cấu trúc có thể hoạt động cho bất kỳ số lượng tiêu chí nào, SUMIFS yêu cầu phạm vi tổng được liệt kê trước. Trong ví dụ của chúng tôi, điều này có nghĩa là công thức cần phải

1	= SUMIFS (B2: B7, A2: A7, D1)

LƯU Ý: Hai công thức này nhận được cùng một kết quả và có thể trông giống nhau, vì vậy hãy chú ý đến hàm nào đang được sử dụng để đảm bảo bạn liệt kê tất cả các đối số theo đúng thứ tự.

Làm việc với Ngày, Nhiều tiêu chí

Khi làm việc với ngày tháng trong bảng tính, mặc dù có thể nhập ngày tháng trực tiếp vào công thức, nhưng cách tốt nhất là có ngày tháng trong một ô để bạn có thể chỉ tham chiếu ô trong công thức. Ví dụ: điều này giúp máy tính biết rằng bạn đang muốn sử dụng ngày 27/5/2020 chứ không phải số 5 chia cho 27 chia cho 2022.

Hãy xem bảng tiếp theo của chúng tôi ghi lại số lượng khách truy cập vào một trang web hai tuần một lần.

Chúng ta có thể chỉ định điểm bắt đầu và điểm kết thúc của phạm vi mà chúng ta muốn xem xét trong D2 và E2. Sau đó, công thức của chúng tôi để tính tổng số lượng khách truy cập trong phạm vi này có thể là:

1	= SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)

Lưu ý cách chúng tôi có thể nối các so sánh của “=” với các tham chiếu ô để tạo tiêu chí. Ngoài ra, mặc dù cả hai tiêu chí đang được áp dụng cho cùng một phạm vi ô (A2: A7), bạn cần phải viết ra phạm vi hai lần, một lần cho mỗi tiêu chí.

Nhiều cột

Khi sử dụng nhiều tiêu chí, bạn có thể áp dụng chúng cho cùng một phạm vi như chúng tôi đã làm với ví dụ trước đó hoặc bạn có thể áp dụng chúng cho các phạm vi khác nhau. Hãy kết hợp dữ liệu mẫu của chúng tôi vào bảng này:

Chúng tôi đã thiết lập một số ô để người dùng nhập nội dung họ muốn tìm kiếm vào các ô từ E2 đến G2. Do đó, chúng tôi cần một công thức tính tổng số táo được hái trong tháng Hai. Công thức của chúng tôi trông giống như sau:

1	= SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)

SUMIFS với logic loại OR

Cho đến thời điểm này, các ví dụ mà chúng tôi đã sử dụng đều là so sánh dựa trên AND, nơi chúng tôi đang tìm kiếm các hàng đáp ứng tất cả các tiêu chí của chúng tôi. Bây giờ, chúng tôi sẽ xem xét trường hợp khi bạn muốn tìm kiếm khả năng một hàng đáp ứng một tiêu chí này hoặc tiêu chí khác.

Hãy xem danh sách bán hàng này:

Chúng tôi muốn cộng tổng doanh số cho cả Adam và Bob. Để làm điều này, bạn có một số lựa chọn. Đơn giản nhất là thêm hai SUMIFS với nhau, như sau:

1	= SUMIFS (B2: B7, A2: A7, "Adam") + SUMIFS (B2: B7, A2: A7, "Bob")

Ở đây, chúng tôi đã để máy tính tính điểm cá nhân của chúng tôi và sau đó chúng tôi cộng chúng lại với nhau.

Tùy chọn tiếp theo của chúng tôi phù hợp khi bạn có nhiều phạm vi tiêu chí hơn, như vậy bạn không muốn phải viết lại toàn bộ công thức nhiều lần. Trong công thức trước, chúng tôi đã yêu cầu máy tính thêm hai SUMIFS khác nhau với nhau theo cách thủ công. Tuy nhiên, bạn cũng có thể làm điều này bằng cách viết các tiêu chí của bạn bên trong một mảng, như sau:

1	= SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"}))

Nhìn vào cách mảng được xây dựng bên trong dấu ngoặc nhọn. Khi máy tính đánh giá công thức này, nó sẽ biết rằng chúng ta muốn tính một hàm SUMIFS cho mỗi mục trong mảng của chúng ta, do đó tạo ra một mảng số. Sau đó, hàm SUM bên ngoài sẽ lấy mảng số đó và biến nó thành một số duy nhất. Bước qua đánh giá công thức, nó sẽ giống như sau:

123	= SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"}))= SUM (27401, 43470)= 70871

Chúng tôi nhận được cùng một kết quả, nhưng chúng tôi có thể viết ra công thức ngắn gọn hơn một chút.

Xử lý khoảng trống

Đôi khi tập dữ liệu của bạn sẽ có các ô trống mà bạn cần tìm hoặc tránh. Việc thiết lập các tiêu chí cho những tiêu chí này có thể hơi phức tạp, vì vậy hãy xem một ví dụ khác.

Lưu ý rằng ô A3 thực sự trống, trong khi ô A5 có công thức trả về một chuỗi có độ dài bằng 0 là “”. Nếu chúng ta muốn tìm tổng cộng của thực sự ô trống, chúng tôi sẽ sử dụng tiêu chí là “=” và công thức của chúng tôi sẽ giống như sau:

1	= SUMIFS (B2: B7, A2: A7, "=")

Mặt khác, nếu chúng tôi muốn lấy tổng cho tất cả các ô trông trống về mặt trực quan, chúng tôi sẽ thay đổi tiêu chí thành “” và công thức trông giống như

1	= SUMIFS (B2: B7, A2: A7, "")

Hãy lật lại: điều gì sẽ xảy ra nếu bạn muốn tìm tổng các ô không trống? Rất tiếc, thiết kế hiện tại sẽ không cho phép bạn tránh được chuỗi có độ dài bằng 0. Bạn có thể sử dụng tiêu chí là “”, nhưng như bạn có thể thấy trong ví dụ, tiêu chí đó vẫn bao gồm giá trị từ hàng 5.