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 Excel thì 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

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 (<)

lớn hơn hoặc bằng (> =)

nhỏ hơn hoặc bằng (<=)

Ví dụ: để cộng các số trong cột B từ B5:B13 lớn hơn 200, hãy sử dụng công thức sau:

= SUMIF (B5: B13, “> 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, “<200”)

Tính tổng nếu nhỏ hơn hoặc bằng 200:

= SUMIF (B5: B13, “<= 200”)

Tính tổng nếu bằng

Để tính tổng các số bằng một số cụ thể, bạn có thể sử dụng dấu bằng (=) cùng với số đó hoặc bỏ qua dấu bằng mà chỉ bao gồm số trong đối số tiêu chí.

Ví dụ: để cộng tất cả các giá tiền trong cột B có số lượng trong cột C bằng 10, bạn có thể sử dụng bất kỳ công thức nào dưới đây:

= SUMIF (C5: C13, 10, B5: B13)

hoặc là

= SUMIF (C5: C13, “= 10”, B5: B13)

hoặc là

= SUMIF (C5: C13, B1, B5: B13)

Trong đó B1 là ô chứa số lượng cần thiết.

Tính tổng nếu không bằng

Để tính tổng các số khác với số đã chỉ định, hãy sử dụng toán tử không bằng (<>).

Trong ví dụ tiếp theo, ta sẽ cộng các số lượng trong cột B có bất kỳ số lượng nào ngoại trừ 10 trong cột C, hãy thực hiện với một trong các công thức sau:

= SUMIF (C5: C13, “<> 10”, B5: B13)

= SUMIF (C5: C13, “<>” & B1, B5: B13)

Ảnh chụp màn hình bên dưới cho thấy kết quả:

Công thức SUMIF của Google Trang tính cho ngày tháng

Để tính tổng các giá trị dựa trên tiêu chí ngày, bạn cũng sử dụng các toán tử so sánh như các ví dụ trên. Điểm mấu chốt là ngày tháng phải được cung cấp ở định dạng mà Google Trang tính có thể hiểu được.

Ví dụ: để tính tổng số tiền trong B5:B13 cho các ngày giao hàng trước ngày 11 tháng 3 năm 2018, hãy xây dựng tiêu chí theo một trong những cách sau:

= SUMIF (C5: C13, “<3/11/2018”, B5: B13)

= SUMIF (C5: C13, “<” & DATE (2018,3,11), B5: B13)

= SUMIF (C5: C13, “<” & B1, B5: B13)

Trong đó B1 là ngày mục tiêu:

Trong trường hợp bạn muốn tính tổng các ô dựa trên ngày hôm nay, hãy kèm thêm hàm TODAY () trong đối số tiêu chí.

Ví dụ, chúng ta tạo một công thức tính tổng số tiền giao hàng ngày hôm nay:

= SUMIF (C5: C13, TODAY (), B5: B13)

Lấy ví dụ xa hơn, chúng ta có thể tìm thấy tổng số lần giao hàng trong quá khứ và tương lai:

Trước hôm nay: = SUMIF (C5: C13, “<” & TODAY (), B5: B13)

Sau hôm nay: = SUMIF (C5: C13, “>” & TODAY (), B5: B13)

Tính tổng dựa trên các ô trống hoặc không trống

Trong nhiều trường hợp, bạn có thể cần tính tổng các giá trị trong một cột nhất định nếu một ô tương ứng trong một cột khác trống hoặc không.

Đối với bài này, hãy sử dụng một trong các tiêu chí sau trong công thức SUMIF của Google Trang tính:

Tính tổng nếu trống:

• “=” để tính tổng các ô hoàn toàn trống.
• “” để tính tổng các ô trống bao gồm cả những ô chứa chuỗi độ dài bằng không.

Tổng nếu không trống:

• “<>” để thêm các ô chứa bất kỳ giá trị nào, bao gồm cả các chuỗi có độ dài bằng không.

Ví dụ: để tính tổng số tiền mà ngày giao hàng được đặt (một ô trong cột C không trống), hãy sử dụng công thức này:

= SUMIF (C5: C13, “<>”, B5: B13)

Để nhận tổng số tiền không có ngày giao hàng (một ô trong cột C trống), hãy sử dụng cái này:

= SUMIF (C5: C13, “”, B5: B13)

Hàm SUMIF trong Google Trang tính với nhiều tiêu chí (HOẶC logic)

Hàm SUMIF trong Google Trang tính được thiết kế để thêm các giá trị chỉ dựa trên một tiêu chí. Để tính tổng với nhiều tiêu chí, bạn có thể kết hợp hai hoặc nhiều hàm SUMIF với nhau.

Ví dụ, để tính tổng hợp số lượng Apple và Orange, hãy sử dụng công thức này:

=SUMIF(A6:A14, “apples”, B6:B14)+SUMIF(A6:A14, “oranges”, B6:B14)

Hoặc bạn có thể đặt tên mục trong hai ô riêng biệt, chẳng hạn B1 và ​​B2, và sử dụng mỗi ô đó làm tiêu chí:

=SUMIF(A6:A14, B1, B6:B14)+SUMIF(A6:A14, B2, B6:B14)

Lưu ý rằng công thức này hoạt động giống như SUMIF với OR logical – nghĩa là nó tính tổng các giá trị nếu đáp ứng ít nhất một trong các tiêu chí đã chỉ định.

Trong ví dụ này, chúng ta thêm các giá trị vào cột B nếu cột A bằng “apple” hoặc “orange”. Nói cách khác, SUMIF () + SUMIF () hoạt động giống như công thức giả sau (không phải là công thức thực, nó chỉ thể hiện logic!): sumif(A:A, “apples” or “oranges”, B:B).

Google Sheets SUMIF – những điều cần nhớ

Bây giờ bạn đã biết cách sử dụng hàm SUMIF trong Google Trang tính, Hocexcel.online sẽ giúp bạn tổng hợp một số điều cần chú ý nhé:

1. SUMIF chỉ có thể đánh giá một điều kiện

Cú pháp của hàm SUMIF chỉ cho phép một phạm vi, một tiêu chí và một sum_range. Để tính tổng với nhiều tiêu chí, hãy thêm một số hàm SUMIF với nhau (OR) hoặc sử dụng công thức SUMIFS (AND).

2. Hàm SUMIF không phân biệt chữ hoa chữ thường

Nếu bạn đang tìm kiếm một công thức SUMIF có thể phân biệt giữa các ký tự viết hoa và viết thường, hãy sử dụng SUMIF kết hợp với ARRAYFORMULA và FIND như ví dụ trên nhé!

3. Cung cấp dải ô và sum_range có kích thước bằng nhau

Trên thực tế, đối số sum_range chỉ xác định ô trên cùng bên trái của dải ô để tính tổng, vùng còn lại được xác định bởi các kích thước của đối số dải ô.

Nói cách khác, SUMIF(A1:A10, “apples”, B1:B10) and SUMIF(A1:A10, “apples”, B1:B100)đều sẽ tính tổng các giá trị trong phạm vi B1: B10 vì nó có cùng kích thước dưới dạng phạm vi (A1: A10).

Vì vậy, ngay cả khi bạn cung cấp sai phạm vi tổng sai, Google Trang tính vẫn sẽ tính đúng công thức của bạn, miễn là ô trên cùng bên trái của sum_range là chính xác.

MẶc dù vậy, bạn vẫn nên cung cấp phạm vi có kích thước bằng nhau và sum_range để tránh sai lầm và ngăn ngừa các vấn đề không nhất quán.

4. Ghi nhớ cú pháp của tiêu chí SUMIF

Để công thức SUMIF của Google Trang tính của bạn hoạt động chính xác, hãy diễn đạt tiêu chí theo đúng cách:

Nếu tiêu chí bao gồm văn bản, ký tự đại diện hoặc toán tử logic theo sau là số, văn bản hoặc ngày tháng, hãy đặt tiêu chí trong dấu ngoặc kép. Ví dụ:

= SUMIF (A2: A10, “apples”, B2: B10)

= SUMIF (A2: A10, “*”, B2: B10)

= SUMIF (A2: A10, “> 5”)

= SUMIF (A5: A10, “<> apples”, B5: B10)

Nếu tiêu chí bao gồm toán tử logic và tham chiếu ô hoặc một hàm khác, hãy sử dụng dấu ngoặc kép để bắt đầu chuỗi văn bản và dấu và (&) để nối và kết thúc chuỗi. Ví dụ:

= SUMIF (A2: A10, “>” & B2)

= SUMIF (A2: A10, “>” & TODAY (), B2: B10)

5. Khóa phạm vi với tham chiếu ô tuyệt đối nếu cần

Nếu bạn định sao chép hoặc di chuyển công thức SUMIF của mình, hãy sửa các phạm vi bằng cách sử dụng tham chiếu ô tuyệt đối (với dấu $) như trong SUMIF ($ A $ 2: $ A $ 10, “apples”, $ B $ 2: $ B $ 10).

Trên đây là cách bạn sử dụng hàm SUMIF trong Google Trang tính. Để có cái nhìn sâu hơn về các công thức được thảo luận trong hướng dẫn này, bạn có thể mở Google Trang tính lên và thực hành cùng mình nhé!. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog vào tuần tới!