Thủ thuật

Hướng dẫn cách sử dụng hàm SUMIF trong Google Sheet tính tổng phức tạp

Trương Nguyễn Anh Thư

30-04-2023, 5:11 pm

Hàm SUMIF là một trong những hàm cơ bản giúp các bạn co thể tính tổng một nhóm giá trị với điều kiện đưa ra trong công cụ Google Sheet. Đây là một hàm khá thông dụng và hữu ích, vì vậy, các bạn hãy cùng mình tìm hiểu cách dùng và cú pháp của hàm SUMIF trong Google Sheet qua bài dưới đây nhé!

Hàm SUMIF có nghĩa là gì? Ứng dụng của hàm SUMIF trong Google Sheet như thế nào?

Hàm SUMIF có nghĩa là gì?

Hàm SUMIF có thể được hiểu là sự kết hợp giữa các hàm SUM với IF. Hai hàm này có chức năng là trả về kết quả là tổng những dữ liệu phù hợp với một điều kiện nhất định, bằng cách quét qua tất cả các giá trị trong bảng dữ liệu mà các bạn đã chọn.

Công thức của hàm SUMIF

=SUMIF(range;condition;sum_range)

Trong đó:

+ Range : là phạm vi được kiểm tra điều kiện.

+ Condition : là điều kiện xác định các ô được tính tổng. Điều kiện có thể là một giá trị như (số, văn bản, ngày tháng) hoặc là một tham chiếu đến một ô chứa tiêu chí.

+ Sum_range: là dải ô để tính tổng. Nếu sum_range được viết vào công thức, đó sẽ là phạm vi ô chứa những giá trị được tính tổng vào, nếu các số tương ứng của nó trong phạm vi dùng để xét điều kiện đúng với điều kiện đề ra. Nếu sum_range không được viết thì nó sẽ được giả định rằng phạm vi xét điều kiện range cũng là sum_range nhé.

Ví dụ minh họa về hàm SUMIF

Ví dụ tại Sheet 1,cho bảng là "Ví dụ về SUMIF cơ bản" có từ F2 đến J9, nội dung về chi tiêu của một nhóm người, gồm các cột như là STT, Hàng hóa, Giá tiền, Tên người mua và Ngày mua.

Ví dụ về hàm SUMIF cơ bản như hình trên

Công thức, như sau: =SUMIF(I4:I9,"Tú",H4:H9)

Ý nghĩa: là tính tổng tiền mà bạn Tú đã dùng.

Giải thích: Nếu bên cột I4:I9 chỉ có chứa chữ “Tú” thì cộng dồn các ô tương ứng bên cột H4:H9 lạ với nhau. Kết quả chúng ta sẽ thu được là 1500 (5000 + 10000).

Tính tổng tiền mà bạn Tú đã dùng như hình trên

Ứng dụng của hàm SUMIF là gì?

Hàm SUMIF được dùng để tính tổng của chỉ số dương hoặc là âm trong một phạm vi sẵn có, tính tổng 1 dãy số với một điều kiện, còn dùng để tìm tổng của doanh số cho một bộ phận, hoặc cũng có thể là tìm tổng chi tiêu trước 1 ngày nhất định,...vv Nói chung, tùy theo từng trường hợp và mục đích mà các bạn có thể dùng hàm SUMIF để tính tổng một phạm vi giá trị phù hợp với nhu cầu của mình mong muốn.

Hướng dẫn cách sử dụng hàm SUMIF trong Google Sheet

Những cách sử dụng cơ bản

+ Hàm SUMIF với điều kiện có số

Công thức, như saU: =SUMIF(H4:H9;">=50000")

Ý nghĩa: Là tính tổng giá trị của những hàng hóa được mua với giá trên hoặc bằng 50000 đồng.

Giải thích: Là công thức này các bạn hoàn toàn có thể nhập =SUMIF(H4:H9;">=50000";H4:H9) hoặc à nhập như công thức trên mình có chia sẻ. Tại vì dãy sum_range của ví dụ mình đưa ra trùng với dãy các bạn dùng để xét điều kiện range luôn nên mình không cần ghi lại sum_range lần nữa nhé. Và sau khi hàm tính toán xong, mình được đáp như sau: 177000 (75000 + 102000).

Tổng giá trị của những hàng hóa trên hoặc bằng 50000 đồng như hình trên

+ Hàm SUMIF với điều kiện là text

Công thức, như sau: =SUMIF(I4:I9;"Dương";H4:H9)

Ý nghĩa: Là tính tổng số tiền Dương đã dùng để mua hàng hóa.

Giải thích: Trong đó, hàm xét trong dãy I4:I9 nếu có tên là “Dương” thì các bạn sẽ cộng dồn lại các giá trị tương ứng bên phạm vi H4:H9. Kết quả sẽ được tính ra là 95000 (20000 + 75000).

Tổng tiền bạn Dương đã dùng như hình trên

+ Hàm SUMIF với điều kiện là ngày/tháng/năm

Công thức, như sau: đang load như hình bên dưới.

Ý nghĩa: Là tính tổng tiền mà nhóm bạn đã tiêu trước và trong ngày 15/05/2021.

Giải thích: Hàm sẽ xét trong phạm vi J4:J9 nếu có giá trị là ngày/tháng/năm nhỏ hơn hoặc bằng ngày 15/05/2021 thì các bạn sẽ cộng dồn các giá trị của các ô tương ứng bên cột H4:H9. Đáp án sẽ được tìm ra là 139000 (10000 + 20000 + 75000 + 34000).

Tổng tiền tiêu trước và trong ngày 15/05/2021 như hình trên

+ Hàm SUMIF với những ký tự đại diện (WILDCARD)

Ký tự (*)

Ký tự (*): Nghãi là Khớp với bất kỳ chuỗi ký tự nào. Được kết hợp với một từ hoặc là gốc chữ cái để tìm các biến thể chỉ cần chứa từ gốc của thuật ngữ.

Công thức, như sau: =SUMIF(G4:G9;"Sữa*";H4:H9)

Ý nghĩa: Là tính tổng giá trị những hàng hóa chứa chữ “Sữa”.

Giải thích: Hàm sẽ xét phạm vi của ô G4:G9 chứa từ “Sữa” ở đầu thì sẽ cộng dồn các giá trị tại các ô tương ứng bên phạm vị H4:H9, kết quả tính cho ra là 112000 (10000 + 102000).

Tính tổng giá trị những hàng hóa chứa chữ “Sữa” như hình trên

Ký tự (?)

Ký tự (?): Được sử dụng để đại diện cho một ký tự duy nhất trong bảng.

Ví dụ tại Sheet 1, mình sẽ tạo thêm một bảng dữ liệu tên là "Hàm SUMIF với các ký tự đại diện (WILDCARD)" từ tô L2 đến N9, nội dung về những sản phẩm liên quan đến quần áo. Bảng sẽ gồm 3 cột là: STT, Tên sản phẩm và Giá.

Bảng dữ liệu liên quan đến các mặt hàng áo khoác như hình trên

Công thức, như sau: =SUMIF(M4:M9;"Hoodie size ?";N4:N9)

Ý nghĩa: Tính tổng các giá tiền của áo Hoodie có phân loại size.

Giải thích: Trong đó, hàm dò điều kiện sẽ là “Hoodie size ?” trong phạm vi M4:M9, nếu phù hợp với điều kiện chỉ có 1 ký tự đằng sau “Hoodie size ” thì cộng giá trị tại các ô tương ứng trong dãy M4:M9 trong bảng, kết quả tính được sẽ là 528000 (100000 + 199000 + 229000).

Tính tổng giá tiền của áo Hoodie có phân loại size như hình trên

Kết hợp với hàm INDEX

Ở đây, mình sẽ chỉ minh họa ví dụ đơn giản nhất để cho các bạn có thể dễ hình dung! Các bạn hãy tính tổng giá trị tiền đã dùng của bạn Tú tại Sheet 1, dữ liệu trong bảng "Ví dụ về SUMIF cơ bản".

Công thức, như sau: =SUMIF(I4:I9;I13;INDEX(F4:J9;;3))

Ý nghĩa: Là hàm SUMIF kết hợp thêm hàm INDEX để tính tổng tiền mà Tú đã dùng.

Giải thích: Hàm SUMIF xét điều kiện giá trị tại I13 (Tú) có phù hợp trong phạm vi I4:I9 hay không nhé. Tiếp đến, hàm INDEX sẽ trả về giá trị tương của cột thứ 3, trong phạm vi cố định là F4:J9 (bảng Ví dụ về SUMIF cơ bản). Sau đó, hàm SUMIF tính tổng giá trị các ô tương ứng trong kết quả trả về của hàm INDEX phù hợp với điều kiện ô I13. Sau khi hoàn tất, mình sẽ thu được kết quả số tiền của bạn Tú đã xài là 15000 đồng.

Tổng tiền mà Tú đã dùng như hình trên

Kết hợp với hàm VLOOKUP

Trước tiên, các bạn hãy nhớ lại cách dùng hàm VLOOKUP trước nhé! VLOOKUP được dùng để tra cứu và truy xuất dữ liệu từ một cột cụ thể trong bảng. Đối với một số giá trị được chỉ định, hàm sẽ tìm, tra cứu giá trị trong một cột dữ liệu và được trả về giá trị tương ứng từ một cột khác.

Công thức của hàm VLOOKUP, như sau:

VLOOKUP(khóa_tìm_kiếm; dải_ô; chỉ_mục; được_sắp_xếp)

Trong đó:

+ khóa_tìm_kiếm: Là giá trị cần tìm kiếm.

+ dải_ô: Là dải ô cần xem xét để tìm kiếm. Cột đầu tiên trong dải_ô này sẽ là nơi để dò với khóa_tìm_kiếm.

+ chỉ_mục: Là giá trị của được trả về nằm trong cột chỉ_mục. Trong đó, cột đầu tiên trong dải_ô sẽ được đánh là cột thứ 1.

+ được_sắp_xếp: Sẽ có giá trị TRUE hoặc FALSE hoặc không ghi. Nếu đặt thành FALSE, kết quả phù hợp chính xác sẽ được trả về ngay. Nếu đặt là TRUE hoặc không ghi thì kết quả khớp gần nhất (nhỏ hơn hoặc bằng khóa tìm kiếm) sẽ được trả về ngay.

Quay lại với hàm SUMIF, tại Sheet 2, đề bài là sự kết hợp 2 hàm này thông qua bài toán tính tổng tiền mà Tú đã xài dựa trên biệt danh Tèo, sử dụng 3 bảng sau: "Ví dụ về SUMIF cơ bản" từ phạm vi A1 đến E8, "Bảng dò biệt danh" phạm vi từ A10 đến B12 và "Kết hợp SUMIF với VLOOKUP" từ D10 đến E12 nhé.

Bảng dữ liệu dùng minh họa VLOOKUP như hình trên

Công thức, như sau: =SUMIF(D3:D8;VLOOKUP(D12;A12:B15;2;FALSE);C3:C8)

Ý nghĩa: Tính toán tổng giá trị tiền mà bạn Tú đã sử dụng dựa trên biệt danh của bạn Tèo.

Giải thích: Trong công thức này, hàm SUMIF sẽ có nhiệm vụ dò tìm các ô thỏa điều kiện VLOOKUP(D12;A12:B15;2;FALSE) (kết quả điều kiện này là "Tú") trong phạm vi D3:D8. Sau đó các bạn cộng dồn các giá trị của ô tương ứng trong phạm vi C3:C8 và thu được kết quả cho bạn Tèo (Tú) là 15000.

Tổng giá trị tiền mà bạn Tú đã sử dụng dựa trên biệt danh Tèo như hình trên

Kết hợp với hàm TODAY

Các bạn tiếp tục tạo thêm 1 sheet mới tên là Sheet 3, tạo thêm một số dòng dữ liệu mới trong bảng “Ví dụ về SUMIF cơ bản” và tạo thêm bảng "Chi tiêu trong ngày hôm nay".

Tạo thêm một số dòng dữ liệu mới như hình trên

Công thức, như sau: =SUMIF(E3:E11;TODAY();C3:C11)

Ý nghĩa: Là tính toán chi tiêu đã dùng trong ngày hiện tại.

Giải thích: Sau khi các bạn nhập công thức thì có kết quả là 129000 (100000 + 4000 + 25000), hàm SUMIF dò điều kiện TODAY() tức là ngày (23/05/2021) có khớp với ô nào trong phạm vi E3:E11 không, sau đó các bạn cộng dồn tất cả các giá trị của các ô tương ứng trong phạm vi C3:C11 để thỏa điều kiện có ngày mua là ngày 23/05/2021.

Tính toán chi tiêu đã dùng trong ngày hiện tại như hình trên

Kết hợp với hàm LEFT và RIGHT

+ Kết hợp với hàm LEFT

Để minh họa cho phần này, tại Sheet 4, mình đã chuẩn bị sẵn một bảng dữ liệu như hình dưới đây sẽ có phạm vi từ A1 đến D6, gồm các trường như là: STT, Mã hàng, Sản phẩm và Giá tiền.

Bảng dữ liệu minh họa cho hàm LEFT, RIGHT như hình trên

Công thức, như sau: =SUMIF(B2:B6;left(F7;2)&"*";D2:D6)

Ý nghĩa: Là tính tổng giá trị của sản phẩm thuộc loại hàng điện thoại di động.

Giải thích: Hàm lấy điều kiện left(F7;2)&"*", tức là “DT*” dò trong dãy phạm vi B2:B6. Sau đó, với những ô thỏa điều kiện, các bạn cộng các giá trị ô ứng bên dãy D2:D6. Cuối cùng, kết quả sẽ thu được là 28000000 (8000000 + 20000000)

Tính tổng giá trị của sản phẩm thuộc loại hàng điện thoại di động như hình trên

+ Kết hợp với hàm RIGHT

Công thức, như sau: =SUMIF(B2:B6;"*"&RIGHT(A10;2);D2:D6)

Ý nghĩa: Là tính tổng giá trị của các sản phẩm cùng thương hiệu là iPhone.

Giải thích: Hàm sẽ lấy điều kiện "*"&RIGHT(E10;2), tức là lấy “*90” dò trong dãy phạm vi B2:B6. Sau đó, với những ô các bạn đã thỏa điều kiện, tính tổng giá trị các ô ứng bên dãy D2:D6. Cuối cùng, kết quả sẽ thu được là 48000000 (8000000 + 40000000).

Tính tổng cho các sản phẩm cùng thương hiệu iPhone như hình trên

- Kết hợp nhiều tiêu chí

Hàm SUMIF là một trong những hàm tính rất mạnh và linh hoạt dùng trong Google Sheet. Các bạn không chỉ có thể tính tổng của một dãy số dựa trên một điều kiện mà các bạn cũng có thể tính tổng của một hay nhiều dãy số nào đó với các điều kiện khác nhau.

Ví dụ, dùng lại bảng dữ liệu tại Sheet 2, các bạn hãy tính tổng số tiền mà Tú và Dương đã dùng, lúc này có 2 cách để tính ra kết quả đúng nhất.

Công thức, như sau: =SUMIF(D3:D8;"Tú";C3:C8)+SUMIF(D3:D8;"Dương";C3:C8)

Ý nghĩa: Tính tổng số tiền mà bạn Tú và bạn Dương đã dùng bằng sử dụng 2 lần SUMIF cho 2 điều kiện khác nhau.

Giải thích: Kết quả thu được sẽ là 110000 ((10000 + 5000) + (20000 + 75000)). Ở đây, công thức chỉ đơn giản là tính riêng tổng số tiền mà bạn Tú đã dùng và bạn Dương đã dùng sau đó cộng hai kết quả này lại với nhau.

Kết hợp nhiều tiêu chí trong hàm SUMIF như hình trên

Những phép so sánh số học hàm SUMIF hỗ trợ

Hàm SUMIF cũng sẽ được hỗ trợ các phép so sánh cơ bản nhưng các hàm khác, dưới đây sẽ là bảng một số phép so sánh, ký hiệu sẽ được sử dụng trong hàm giúp các bạn làm việc với hàm SUMIF thuận tiện hơn.

Phép so sánh	Ký hiệu toán tử	Ví dụ	Giải thích ví dụ
Lớn hơn	>	=SUMIF(C7:C12;">60000")	Hãy tính tổng giá tiền các mặt hàng có giá lớn hơn 60000, kết quả: 177000.
Nhỏ hơn	<	=SUMIF(C7:C12;"<60000")	Hãy tính tổng giá tiền các mặt hàng có nhỏ hơn 60000, kết quả: 69000.
Lớn hơn hoặc bằng	>=	=SUMIF(C7:C12;">="&C10)	Hãy tính tổng giá tiền các mặt hàng có giá lớn hơn hoặc bằng 75000 (giá trị của ô C10), kết quả: 177000.
Nhỏ hơn hoặc bằng	<=	=SUMIF(C7:C12;"<="&C10)	Hãy tính tổng giá tiền các mặt hàng có giá nhỏ hơn hoặc bằng 75000 (giá trị của ô C10), kết quả: 144000.
Không bằng	<>	=SUMIF(C7:C12;"<>"&C10)	Hãy tính tổng giá tiền các mặt hàng có giá khác 75000 (giá trị của ô C10), kết quả: 171000.

Dữ liệu dùng để minh họa những công thức trong bảng trên nằm tại ô Sheet 6, bảng "Ví dụ về SUMIF cơ bản" phạm vi từ A5 đến A12, gồm các cột như là: STT, Hàng hóa, Giá tiền, Tên người mua, Ngày mua.

Bảng dữ liệu minh họa các phép so sánh như hình trên

Một số lưu ý khi sử dụng hàm SUMIF trong Google Sheet

- Hàm SUMIF chỉ đánh giá theo 1 điều kiện, nếu các bạn muốn sử dụng nhiều điều kiện thì nên dùng hàm SUMIFS.

- Trong phần điều kiện nếu đó là sự kết hợp của toán tử và tham chiếu ô hoặc một hàm khác, thì chúng phải được kết hợp với nhau bằng dấu “&” (và). Ví dụ như là: ">="&C10 hoặc "<="&DATE(2021;5;15).

Có ký hiệu & nếu điều kiện là kết hợp của toán tử và một hàm khác như hình trên

- Hàm SUMIF sẽ không phân biệt chữ hoa, thường. Vì vậy các bạn nhập sumif hoặc SUMIF đều được.

- Nếu dùng công thức của 1 ô vào sao chép cho các ô còn lại thì các bạn hãy cẩn thận với phạm vi tham chiếu xét điều kiện (range) và phạm vi tính tổng (sum_range); để cố định một phạm vi thì nhấn phím F4 trên bàn phím là hoàn tất.

Trên đây là toàn bộ nội dung mà mình muốn chia sẻ đến các bạn, hy vọng bài viết này sẽ hữu ích giúp cho các bạn hoàn thành công việc một cách nhanh chóng và khoa học nhất. Cảm ơn các abnj đã bỏ thời gian để theo dõi và hẹn các bạn trong những bài viết sau!