Bài viết này Học Excel Online sẽ hướng dẫn bạn cách sử dụng hàm SUMIF trong Google Sheetsđể tính tổng các ô có điều kiện thông qua các ví dụ công thức cho văn bản, số và ngày tháng và cách tính tổng với nhiều tiêu chí. Một số chức năng tiện lợi trong Google Sheets là những chức năng giúp bạn tóm tắt và phân loại dữ liệu. Hôm nay, chúng ta sẽ cùng tìm hiểu một trong những hàm như vậy – hàm SumIf – một công cụ mạnh mẽ để tính tổng các ô theo điều kiện. Trước khi đi vào các ví dụ về cú pháp và công thức, chúng ta hãy bắt đầu với một vài lưu ý quan trọng. Google Trang tính có hai chức năng để cộng các số dựa trên các điều kiện: SUMIF và SUMIFS. Cái trước chỉ đánh giá một điều kiện trong khi cái sau có thể kiểm tra nhiều điều kiện cùng một lúc. Trong bài viết này, chúng ta sẽ chỉ tập trung vào hàm SUMIF, việc sử dụng hàm SUMIFS sẽ được đề cập trong bài viết tiếp theo nhé. Nếu bạn biết cách sử dụng SUMIF trong Excelthì hàm SUMIF trong Google Trang tính sẽ nằm trong tầm tay bạn vì cơ bản cả hai đều giống nhau. Nhưng đừng vội bỏ qua bài viết này – bạn có thể tìm thấy một số công thức SUMIF rất hữu ích mà bạn chưa biết đấy!
- SUMIF trong Google Trang tính – cú pháp và cách sử dụng cơ bản
- Các ví dụ về sử dụng hàm SUMIF trong Google Trang tính
- Công thức SUMIF với tiêu chí văn bản [đối sánh chính xác]
- Công thức SUMIF với các ký tự đại diện [khớp một phần]
- SUMIF phân biệt chữ hoa chữ thường trong Google Trang tính
- Công thức SUMIF cho số
- Tính tổng nếu lớn hơn hoặc nhỏ hơn
- Tính tổng nếu bằng
- Tính tổng nếu không bằng
- Công thức SUMIF của Google Trang tính cho ngày tháng
- Tính tổng dựa trên các ô trống hoặc không trống
- Tính tổng nếu trống:
- Tổng nếu không trống:
- Hàm SUMIF trong Google Trang tính với nhiều tiêu chí [HOẶC logic]
- Google Sheets SUMIF – những điều cần nhớ
- 1. SUMIF chỉ có thể đánh giá một điều kiện
- 2. Hàm SUMIF không phân biệt chữ hoa chữ thường
- 3. Cung cấp dải ô và sum_range có kích thước bằng nhau
- 4. Ghi nhớ cú pháp của tiêu chí SUMIF
- 5. Khóa phạm vi với tham chiếu ô tuyệt đối nếu cần
SUMIF trong Google Trang tính – cú pháp và cách sử dụng cơ bản
Hàm SUMIF được dùng để tính tổng dữ liệu số dựa trên một điều kiện. Cú pháp của nó như sau: Cú pháp của hàm SUMIF như sau: =SUMIF[range, condition,[sum_range]] Trong đó:
- Range [phạm vi]: là vùng dữ liệu chứa tiêu chí/điều kiện sẽ kiểm tra.
- Condition [điều kiện]: là tiêu chí mà một ô trong phạm vi cần đáp ứng để đủ điều kiện là đối sánh. Điều kiện có thể là một giá trị [số, văn bản, ngày tháng] hoặc một tham chiếu đến một ô chứa tiêu chí.
- Sum_range là một tham số tùy chọn. Nếu được bao gồm, đó sẽ là phạm vi ô chứa các giá trị được thêm vào nếu số tương ứng của nó trong phạm vi phù hợp với điều kiện. Nếu tham số sum_range không được bao gồm thì hàm SUMIF sẽ sử dụng tham chiếu trong tham số đầu tiên làm vùng tính tổng.
Ví dụ: hãy tạo một công thức đơn giản để tính tổng các số trong cột B nếu cột A chứa một mục bằng “mục mẫu”. Đối với bài này, chúng ta xác định các đối số sau: Range – danh sách các mục – A5:A13. Condition – ô chứa mục quan tâm – B1. Sum_range – số tiền được tính tổng – B5:B13. Đặt tất cả các đối số lại với nhau, chúng ta nhận được công thức sau: = SUMIF [A5: A13, B1, B5: B13] Thực hiện trên Google Trang tính ta được:
Các ví dụ về sử dụng hàm SUMIF trong Google Trang tính
Từ ví dụ trên, bạn có thể thấy rằng việc sử dụng công thức SUMIF trong bảng tính Google khá dễ dàng. Trong hầu hết các trường hợp, nó thực sự là như vậy 🙂 Nhưng vẫn có một số thủ thuật và cách sử dụng khác có thể làm cho công thức của bạn hay ho hơn. Các ví dụ dưới đây sẽ minh họa một vài trường hợp sử dụng điển hình.
Công thức SUMIF với tiêu chí văn bản [đối sánh chính xác]
Để cộng các số có văn bản cụ thể trong một cột khác trong cùng một hàng, bạn chỉ cần cung cấp văn bản mà bạn quan tâm trong đối số tiêu chí của công thức SUMIF. Như thường lệ, bất kỳ văn bản nào trong bất kỳ đối số nào của bất kỳ công thức nào đều phải được đặt trong “dấu ngoặc kép”. Ví dụ, để có tổng số chuối, bạn sử dụng công thức sau: =SUMIF[A5:A13,”bananas”,B5:B13] Hoặc, bạn có thể đặt tiêu chí vào một số ô và tham chiếu đến ô đó: =SUMIF[A5:A13,B1,B5:B13] Công thức này rõ như ban ngày, phải không? Bây giờ, làm thế nào để bạn có được tổng số tất cả các mục ngoại trừ chuối? Đừng rối, hãy sử dụng hàm sau:
=SUMIF[A5:A13,”bananas”,B5:B13] Nếu một “mục loại trừ” được nhập trong một ô, thì bạn đặt toán tử không bằng với trong dấu ngoặc kép [“”] và nối toán tử và tham chiếu ô bằng cách sử dụng dấu và [&]. Ví dụ: =SUMIF [A5:A13,””&B1, B5:B13] Ảnh chụp màn hình sau minh họa cả hai công thức “Tổng nếu bằng” và “Tổng nếu không bằng” đang hoạt động:
Bạn hãy lưu ý rằng SUMIF trong Google Trang tính tìm kiếm chính xác văn bản được chỉ định. Trong ví dụ này, chỉ tổng số lượng Chuối[bananas], không bao gồm chuối Xanh[Green bananas] và chuối Goldfinger[Goldfinger bananas]. Nếu bạn muốn tính tổng tất cả các loại chuối, hãy sử dụng các ký tự đại diện trong ví dụ tiếp theo nhé!
Công thức SUMIF với các ký tự đại diện [khớp một phần]
Khi bạn muốn tính tổng các ô trong một cột nếu một ô trong cột khác chứa một văn bản hoặc ký tự cụ thể như một phần của nội dung ô, hãy kèm theo một trong các ký tự đại diện sau trong tiêu chí của bạn: Dấu hỏi [?] Để phù hợp với bất kỳ ký tự đơn nào. Dấu hoa thị [*] để khớp với bất kỳ chuỗi ký tự nào. Ví dụ: để tính tổng số lượng của tất cả các loại chuối, hãy sử dụng công thức sau: =SUMIF[A5:A13,”*bananas*”,B5:B13] Bạn cũng có thể sử dụng các ký tự đại diện cùng với các tham chiếu ô. Với điều này, hãy đặt ký tự đại diện trong dấu ngoặc kép và nối nó với một tham chiếu ô: =SUMIF[A5:A13, “*”&B1&”*”, B5:B13]
SUMIF phân biệt chữ hoa chữ thường trong Google Trang tính
Theo mặc định, SUMIF trong Google Trang tính không thấy sự khác biệt giữa chữ viết hoa và chữ nhỏ. Để buộc nó phải phân biệt các ký tự viết hoa và viết thường, hãy sử dụng SUMIF kết hợp với các hàm FIND và ARRAYFORMULA: SUMIF[ARRAYFORMULA[ FIND[“text“, range]], 1, sum_range] Giả sử bạn có một danh sách mã số đơn hàng trong cột A5:A13 và số tiền tương ứng trong C5:C13, trong đó các mã số có thể lặp lại ở một số hàng. Bạn nhập id đơn đặt hàng mục tiêu vào một ô, chẳng hạn B1, và sử dụng công thức sau để trả về tổng số đơn đặt hàng: =SUMIF[ARRAYFORMULA[FIND[B1, A5:A13]],1, C5:C13] Công thức này hoạt động như thế nào? Để hiểu rõ hơn về logic của công thức, hãy chia nó thành các phần: Phần khó nhất là đối số phạm vi: ARRAYFORMULA [FIND [B1, A5: A13]] Bạn sử dụng hàm FIND phân biệt chữ hoa chữ thường để tìm id đơn hàng chính xác. Vấn đề là một công thức FIND thông thường chỉ có thể tìm kiếm trong một ô duy nhất. Để tìm kiếm trong một phạm vi, cần có công thức mảng, vì vậy bạn lồng FIND bên trong ARRAYFORMULA. Khi kết hợp trên tìm thấy một kết quả chính xác, nó sẽ trả về 1 [vị trí của ký tự được tìm thấy đầu tiên], nếu không sẽ là lỗi #VALUE. Vì vậy, việc duy nhất bạn cần làm lúc này là tính tổng số tiền tương ứng với số 1. Đối với điều này, bạn đặt 1 trong đối số tiêu chí và C5:C13 trong đối số sum_range. Là xong!
Công thức SUMIF cho số
Để tính tổng các số đáp ứng một điều kiện nhất định, hãy sử dụng một trong các toán tử so sánh trong công thức SUMIF:
Tính tổng nếu lớn hơn hoặc nhỏ hơn
Để so sánh các số đã cho với một số cụ thể, hãy sử dụng một trong các toán tử logic sau: lớn hơn [>] nhỏ hơn [ =] nhỏ hơn hoặc bằng [ 200”] Lưu ý cú pháp chính xác: một số bắt đầu bằng toán tử so sánh và toàn bộ cấu trúc được đặt trong dấu ngoặc kép. Hoặc bạn có thể nhập số vào một ô nào đó [ví dụ B1] và nối toán tử so sánh với tham chiếu ô: =SUMIF[B5:B13, “>”&B1, B5:B13] Bạn thậm chí có thể nhập cả toán tử so sánh và số vào các ô riêng biệt và nối các ô đó: Xây dựng tiêu chí SUMIF bằng cách nối toán tử so sánh và số. Theo cách tương tự, bạn có thể sử dụng các toán tử logic khác như: Tính tổng nếu lớn hơn hoặc bằng 200: = SUMIF [B5: B13, “> = 200”] Tính tổng nếu nhỏ hơn 200: = SUMIF [B5: B13, “