Cách dùng hàm SUMIF trên Google Sheets – Giới thiệu và ví dụ ứng dụng

Bài viết sau đây, của Siêu Marketing, sẽ giải thích cách làm việc với hàm SUMIF trên Google Sheets, kèm theo đó là các mẫu công thức cụ thể cho văn bản, số liệu và các ngày, cũng như cách tính toán tổng số với nhiều điều kiện khác nhau.

Trong Google Sheets, một số tính năng giúp việc tổng hợp và sắp xếp thông tin trở nên thuận lợi. Hôm nay, chúng ta sẽ tìm hiểu về một trong những tính năng đó – hàm SumIf, cung cấp khả năng mạnh mẽ trong việc cộng dồn dữ liệu dựa trên các tiêu chí nhất định. Chúng ta hãy xem xét một số điểm chú ý trước khi chúng ta đi vào chi tiết về cách dùng và ví dụ.

Có hai hàm trong Google Sheets cho phép bạn thực hiện việc cộng dữ liệu theo điều kiện: SUMIF và SUMIFS. Hàm đầu tiên được áp dụng khi chỉ có một điều kiện, trong khi hàm thứ hai có thể xử lý nhiều điều kiện cùng một lúc. Bài viết này chủ yếu tập trung vào hàm SUMIF, còn hàm SUMIFS sẽ được bàn luận trong một bài viết khác sau.

Nếu bạn đã quen với việc dùng hàm SUMIF trong Excel, việc áp dụng hàm SUMIF trên Google Sheets sẽ không mấy khó khăn, bởi cả hai có cấu trúc cơ bản tương tự. Dù sao, đừng bỏ qua bài viết này – rất có thể bạn sẽ khám phá thêm được các công thức SUMIF cực kỳ bổ ích chưa từng được biết tới!

Giới thiệu về hàm SUMIF trên Google Sheets – Cú pháp cơ bản và cách dùng

Hàm SUMIF thường được sử dụng để cộng dồn dữ liệu số dựa vào một điều kiện nhất định. Cú pháp của hàm này được mô tả như sau:

Cú pháp của hàm SUMIF:

=SUMIF(range, condition,[sum_range])

Trong đó:

  • Range: đề cập đến khu vực dữ liệu chứa điều kiện mà chúng ta cần so sánh.
  • Condition: là yêu cầu mà các ô trong range cần phải đáp ứng để được xem xét. Điều kiện này có thể là giá trị cụ thể (số, văn bản, ngày,…) hoặc có thể là một ô khác mà trong đó chứa điều kiện cần đánh giá.
  • Sum_range là tham số được chọn thêm, không bắt buộc phải có. Nếu nó được kể đến trong công thức, đó chính là phạm vi các ô chứa số liệu cần phải tính tổng nếu các ô tương ứng trong range phù hợp với điều kiện. Không bao gồm sum_range thì hàm SUMIF sẽ mặc định sử dụng range làm nơi tính toán tổng số.

Ví dụ một công thức đơn giản dùng để tính tổng số liệu trong cột B dựa vào điều kiện mà cột A đếm được giống “mục mẫu”.

Đối với ví dụ này, các đối số được làm rõ:

Range – danh sách cần đánh giá – A5:A13.

Condition – ô chứa mục tiêu so sánh – B1.

Sum_range – khoảng số liệu cần được cộng dồn – B5:B13.

Khi kết hợp các tham số này, công thức cuối cùng như sau:

= SUMIF (A5: A13, B1, B5: B13)

Khi áp dụng công thức này trên Google Sheets, ta có kết quả:

Hàm SUMIF trên Google Sheets
Hàm SUMIF trên Google Sheets

Hướng dẫn chi tiết việc sử dụng hàm SUMIF trong Google Sheets

Qua ví dụ cung cấp trước đó, bạn có thể nhận thấy việc áp dụng hàm SUMIF vào bảng tính của Google là khá đơn giản. Đúng vậy, đa phần các tình huống sử dụng hàm này đều dễ dàng thực hiện Tuy nhiên, còn có một số mẹo và cách dùng hàm này khiến công thức bạn tạo ra trở nên hiệu quả hơn.Dưới đây là một số ví dụ điển hình sẽ làm sáng tỏ cách thức hoạt động của các trường hợp sử dụng.

Áp dụng công thức SUMIF cho tiêu chí văn bản cố định (đối sánh tuyệt đối)

Bạn có thể tổng cộng các con số dựa trên văn bản đặc biệt trong một cột nếu như chúng nằm cùng hàng với một cột khác một cách dễ dàng. Chỉ cần nhập văn bản quan trọng của bạn vào trong đối số tiêu chí khi sử dụng công thức SUMIF. Lưu ý, bất kỳ văn bản nào sử dụng trong bất kỳ công thức nào cần được bao quanh trong “dấu ngoặc kép”.

Muốn tính tổng số lượng chuối, bạn dùng công thức:

=SUMIF(A5:A13, “bananas”, B5:B13)

Hoặc, đặt tiêu chí vào ô riêng và tham chiếu tới ô đó:

=SUMIF(A5:A13, B1, B5:B13)

Công thức trên khá rõ ràng, đúng không? Vậy nếu muốn tìm tổng của mọi thứ trừ chuối, bạn làm thế nào? Hãy áp dụng hàm dưới đây:

3 Hàm SUMIF trong Google Trang tính
3 Hàm SUMIF trong Google Trang tính

=SUMIF(A5:A13, “<>bananas”, B5:B13)

Trong trường hợp “điều kiện loại trừ” được nhập trong một ô, bạn đặt toán tử khác (“<>“) trong dấu ngoặc kép và liên kết nó với tham chiếu ô bằng dấu và (&). Chẳng hạn:

=SUMIF(A5:A13, “<>“&B1, B5:B13)

Hình dưới đây thể hiện 2 công thức “Tổng nếu bằng” và “Tổng nếu không bằng” đang được ứng dụng:

2 Hàm SUMIF trong Google Trang tính
2 Hàm SUMIF trong Google Trang tính

Chú ý rằng, SUMIF tại Google Trang tính chỉ tập trung vào văn bản được chỉ rõ, không kể các biến thể như chuối Xanh hay chuối Goldfinger. Nếu bạn cần tính tổng cho mọi loại chuối, hãy nhìn vào ví dụ sau sử dụng ký tự đại diện:

Phương pháp SUMIF kết hợp với ký tự đại diện (khớp một phần)

Khi bạn muốn tổng số ô cụ thể dựa trên nội dung của một ô khác chứa một phần văn bản hoặc ký tự nào đó, sử dụng ký tự đại diện sau đây trong tiêu chí:

Dùng dấu hỏi (?) để khớp bất kỳ ký tự đơn nào.

Dùng dấu hoa thị (*) đại diện cho bất kỳ chuỗi ký tự nào.

Ví dụ: để cộng tổng số lượng tất cả các loại chuối, dùng công thức:

=SUMIF(A5:A13, “*bananas*”, B5:B13)

Cùng với đó, bạn cũng có thể ghép nối ký tự đại diện cùng tham chiếu tới ô. Để làm điều này, hãy đặt ký tự đại diện trong dấu ngoặc kép và nối chúng với tham chiếu ô:

=SUMIF(A5:A13, “*”&B1&”*”, B5:B13)

Phân biệt chữ hoa và chữ thường trong SUMIF tại Google Trang tính

Google Trang tính là nền tảng mặc định không phân biệt chữ hoa và chữ thường trong SUMIF. Để yêu cầu nó phải nhận biết rõ ràng giữa chữ hoa và chữ thường, hãy dùng SUMIF cùng với hàm FIND và ARRAYFORMULA:

SUMIF(ARRAYFORMULA(FIND(“text“, range)), 1, sum_range)

Nếu bạn có một dãy mã số đơn hàng trong cột A5:A13 và các số liệu tiền tương ứng tới chúng, hãy dùng công thức trên để phân biệt các loại theo tổ chức mong muốn.

Bài viết phổ biến một phương pháp sử dụng công thức để tính tổng các đơn đặt hàng khi mã số có thể xuất hiện nhiều lần trên nhiều dòng. Đặc biệt, nếu mã số được nhập vào ô B1, thì công cụ sau đây được áp dụng:

=SUMIF(ARRAYFORMULA(FIND(B1, A5:A13)),1, C5:C13)

Để hiểu cách thức hoạt động của nó, chúng ta sẽ giải thích từ từng phần của công thức:

Đầu tiên, phần khó khăn nhất là việc sử dụng đối số phạm vi trong ARRAYFORMULA(FIND(B1, A5:A13)).

Ứng dụng hàm FIND – hàm phân biệt chữ hoa và chữ thường, để xác định mã đơn hàng chính xác. Tuy nhiên FIND chỉ tìm kiếm trong một ô, nên để mở rộng ra phạm vi rộng lớn hơn, bạn cần phải sử dụng ARRAYFORMULA bọc ngoài hàm FIND.

Hàm này sẽ trả về số 1 khi tìm thấy kết quả chính xác, hoặc trả về lỗi #VALUE nếu không tìm thấy. Nhiệm vụ của bạn là tính tổng các giá trị tương ứng với số 1 bằng cách đặt số 1 vào đối số tiêu chí và phạm vi C5:C13 vào đối số sum_range. Công việc hoàn tất!

Cách sử dụng công thức SUMIF với số

Khi cần tính tổng theo một điều kiện số cụ thể, bạn có thể sử dụng các toán tử so sánh trong công thức SUMIF:

Xác định tổng số khi vượt quá hoặc không đạt đến một ngưỡng nhất định

Sử dụng các toán tử so sánh sau để so sánh với một con số cụ thể:

lớn hơn (>)

nhỏ hơn (<)

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

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

Chẳng hạn, để cộng tất cả số trong cột B từ B5 đến B13 có giá trị lớn hơn 200:

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

Chú ý đến cú pháp đúng: số nằm sau toán tử so sánh và cả hai nằm trong dấu ngoặc kép.

Bạn cũng có thể đặt con số cần so sánh vào một ô, ví dụ B1, và kết nối nó với toán tử so sánh:

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

Khả năng khác là đặt cả toán tử so sánh lẫn con số vào các ô riêng rẽ và kết nối chúng lại với nhau:

Kết hợp toán tử và số để tạo tiêu chí cho SUMIF.

Tương tự, sử dụng toán tử khác để:

Cộng những số lớn hơn hoặc bằng 200:

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

Tính tổng số nhỏ hơn 200:

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

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

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

Tổng hợp số khi chúng bằng với một giá trị cụ thể

Nếu muốn tổng hợp những con số bằng với một số đặc biệt, bạn có thể sử dụng dấu bằng (=) cùng số đó hoặc chỉ đơn giản là bỏ dấu bằng và chỉ đưa số vào tiêu chí.

Ví dụ, để cộng tất cả giá trị trong cột B mà số lượng tương ứng trong cột C là 10:

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

hoặc

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

hoặc

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

trong đó B1 chứa số cần tìm.

Tổng hợp số khi chúng không bằng một giá trị nào đó

Khi cần tính tổng các con số không bằng số định trước, sử dụng toán tử “không bằng” (<>).

Trong ví dụ sau, để tính tổng các số lượng trong cột B không bằng 10 trong cột C, áp dụng một trong hai công thức dưới đây:

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

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

Màn hình bên dưới thể hiện kết quả:

Công thức SUMIF trong Google Sheets với các tiêu chí ngày tháng

Để cộng các giá trị dựa vào tiêu chí ngày tháng, các bạn cũng sẽ dùng các toán tử so sánh giống như đã làm ở trên. Điểm chính là ở việc bạn phải đối chiếu với ngày tháng

Thông tin cần được đưa vào trong hình thức mà Google Sheets có khả năng nhận biết được.

Cụ thể, để cộng dồn số tiền trong cột B từ B5 đến B13 cho các giao dịch diễn ra trước ngày 11/3/2018, bạn có thể lập nên tiêu chí bằng một trong số những cách dưới đây:

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

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

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

Ở đây B1 chính là ngày bạn muốn đặt làm mục tiêu:

Bạn muốn tổng hợp các số liệu dựa trên ngày hiện tại thì nên thêm hàm TODAY() vào đối số tiêu chí của bạn.

Chẳng hạn, để xây dựng công thức tính tổng số tiền của các giao dịch vào ngày hôm nay, bạn nên dùng:

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

Và để khám phá tổng số lượng giao dịch trong quá khứ và tương lai, bạn có thể làm như sau:

Đối với những giao dịch trước ngày hôm nay, dùng: =SUMIF(C5:C13,”<"&TODAY(),B5:B13)

Đối với những giao dịch sau ngày hôm nay, dùng: =SUMIF(C5:C13,”>”&TODAY(),B5:B13)

Tổng hợp dữ liệu dựa trên các ô không có dữ liệu hoặc có dữ liệu

Tại một vài trường hợp cụ thể, bạn cần tổng cộng giá trị trong một cột nếu ô cột đối diện không chứa thông tin hoặc là chứa thông tin.

Sử dụng các tiêu chí sau đây trong công thức SUMIF của bạn trên Google Sheets:

Tính tổng khi không có dữ liệu:

  • Dùng “=” cho trường hợp muốn tính tổng các ô không chứa dữ liệu nào cả.
  • Dùng “” cho trường hợp muốn tính tổng các ô rỗng kể cả khi chúng chứa chuỗi rỗng.

Tính tổng cho các ô đã có dữ liệu:

  • Dùng “<>” để cộng dồn các ô có chứa bất cứ thông tin nào, bao gồm cả những chuỗi không có ký tự nào.

Ví dụ, để cộng số tiền với điều kiện là ngày giao hàng đã được xác định (ô tại cột C chứa dữ liệu), dùng công thức:

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

Để tính tổng dựa trên những ô không xác định ngày giao (ô tại cột C trống), hãy dùng:

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

Sử dụng hàm SUMIF của Google Sheets với nhiều tiêu chí (logic HOẶC)

Hàm SUMIF trong Google Sheets ban đầu chỉ được thiết kế để cộng dồn giá trị theo một tiêu chí duy nhất. Để thực hiện cộng dồn với nhiều tiêu chí, bạn cần ghép nhiều hàm SUMIF lại với nhau.

Để cộng tổng số lượng của “Apple” và “Orange”, bạn có thể dùng công thức phía dưới:

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

Bạn cũng có thể đặt tên cho từng sản phẩm trong hai ô riêng biệt, ví dụ như B1 và B2, và sau đó sử dụng từng ô như là tiêu chí:

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

Chú ý là công thức này hoạt động dựa trên logic OR, nghĩa là nó cộng dồn giá trị khi thoả mãn ít nhất một trong những tiêu chí được đặt ra.

Trong ví dụ này, chúng tôi cộng số liệu ở cột B nếu cột A có chứa thông tin là “apple” hoặc “orange”. Công thức SUMIF() + SUMIF() này tương đương với cách biểu diễn giả định (không phải công thức thực tế) là: sumif(A:A,”apples” hoặc “oranges”,B:B).

Những điểm cần lưu ý khi dùng Google Sheets SUMIF

Bạn đã quen với cách hoạt động của hàm SUMIF trong Google Sheets, Siêu Marketing xin gửi đến bạn một số lưu ý quan trọng:

1. SUMIF chỉ thẩm định một điều kiện

Cấu trúc của hàm SUMIF chỉ cho phép nhập vào một phạm vi, một tiêu chí và một phạm vi cộng dồn. Để thực hiện cộng dồn với nhiều điều kiện, bạn cần kết hợp nhiều hàm SUMIF (logic OR) hoặc sử dụng công thức SUMIFS (logic AND).

2. Hàm SUMIF không quan trọng việc phân biệt chữ in hoa hay thường

Nếu bạn tìm kiếm một phiên bản SUMIF có khả năng phân biệt chữ cái viết hoa và viết thường, hãy áp dụng SUMIF cùng với ARRAYFORMULA.

Khi sử dụng hàm SUMIF, bạn cần đảm bảo rằng kích thước của sum_range và range phải giống nhau.

3. Đảm bảo dải ô và sum_range là đồng nhất về kích cỡ

Thực tế cho thấy sum_range chỉ cần thiết lập ô góc trên bên trái của dải tổng cộng, còn kích thước toàn bộ vùng sẽ tương ứng với dải ô đã nhập.

Điều này có nghĩa là SUMIF(A1:A10, “apples”, B1:B10) và SUMIF(A1:A10, “apples”, B1:B100) đều sẽ tính tổng dựa trên dải ô B1: B10, bởi vì kích thước này giống với phạm vi (A1: A10).

Tuy nhiên, dù cung cấp sum_range không chuẩn xác, Google Trang tính vẫn sẽ thực hiện đúng công thức nếu ô đầu tiên của sum_range được nhập chính xác.

Dẫu sao, để tránh nhầm lẫn và vấn đề không ổn định, bạn vẫn nên cần chỉ định dải ô và sum_range có kích thước phù hợp.

4. Nắm vững cách viết tiêu chí cho SUMIF

Để hàm SUMIF trong Google Trang tính thực hiện chính xác, bạn cần biểu đạt tiêu chí một cách đúng đắn:

Nếu tiêu chí gồm chữ, ký hiệu hoặc toán tử logic kèm số, chữ, hoặc ngày, tiêu chí nên được để 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í chứa toán tử logic kèm theo tham chiếu ô hoặc hàm khác, hãy dùng dấu ngoặc kép để mở chuỗi và dấu & để nối chuỗi. Chẳng hạn:

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

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

5. Sử dụng tham chiếu ô tuyệt đối để khóa phạm vi khi cần thiết

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

 

Bạn vừa xem qua những lưu ý khi dùng hàm SUMIF trên Google Trang tính. Mở Trang tính lên và thực hành theo hướng dẫn trên để nắm vững hơn nhé. Cảm ơn bạn đã theo dõi, hẹn gặp lại trong các bài viết sau trên blog!

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop