Nếu bạn thường xuyên sử dụng Google Sheets và từng cần cộng các giá trị dựa trên điều kiện nào đó trong các ô cụ thể, bạn sẽ cần biết cách dùng hàm SUMIF trong Google Sheets.
Khả năng tính tổng dữ liệu với hàm này không giới hạn ở chỉ hai giá trị. Bạn có thể tính tổng toàn bộ một phạm vi. Và điều kiện bạn cung cấp cho hàm để quyết định có tính tổng hay không có thể phụ thuộc vào nhiều ô trong bảng tính của bạn.
Cách hàm SUMIF hoạt động trong Google Sheets
SUMIF là một hàm bảng tính đơn giản, nhưng đủ linh hoạt để bạn thực hiện một số tính toán sáng tạo.
Bạn cần khung hàm như sau:
SUMIF(range, criterion, [sum_range])
Các tham số cho hàm này như sau:
- Range (Phạm vi): Phạm vi ô bạn muốn dùng để đánh giá có nên cộng giá trị hay không.
- Criterion (Điều kiện): Điều kiện của các ô bạn muốn đánh giá.
- Sum_range (Phạm vi tính tổng): Tham số tùy chọn, bao gồm các ô bạn muốn tính tổng. Nếu không bao gồm tham số này, hàm sẽ đơn giản tính tổng chính phạm vi đó.
Hàm trông đơn giản, nhưng việc bạn có thể tính tổng hoặc so sánh phạm vi nhiều ô cho phép sự linh hoạt nhiều hơn bạn nghĩ.
Ví dụ SUMIF với văn bản
Nếu bạn sẵn sàng bắt đầu với hàm SUMIF, cách tốt nhất là dùng hai cột trong bảng tính. Một cột để so sánh, và cột kia chứa giá trị bạn muốn cộng.
Bảng tính ví dụ trên là của chủ cửa hàng theo dõi mua hàng trong một khoảng thời gian. Chủ cửa hàng muốn tạo cột thêm tính tổng giá mua ở cột B cho các giá trị cụ thể ở cột A.
Trong trường hợp này, phạm vi so sánh sẽ là A2:A15.
Điều kiện sẽ là cụm từ tìm kiếm cho mặt hàng cần cộng. Vì vậy, để cộng tất cả các lần mua "Post Digger" (Cày cúc), điều kiện sẽ là văn bản "Post Digger".
Phạm vi tính tổng sẽ là phạm vi ô chứa giá trị cần cộng. Ở đây là B2:B15.
Khi bạn nhấn Enter, bạn sẽ thấy các giá trị cụ thể từ cột sum_range được cộng, nhưng chỉ với dữ liệu từ các ô mà cột A khớp với điều kiện bạn chỉ định.
Đây là cách đơn giản dùng hàm SUMIF; như một cách để lấy giá trị từ cột thứ hai dựa trên các mục liệt kê ở cột đầu.
Lưu ý: Bạn không phải gõ điều kiện vào công thức trong ngoặc kép. Thay vào đó, bạn có thể gõ giá trị đó vào một ô trong sheet và nhập ô đó vào công thức.
Sử dụng toán tử SUMIF với văn bản
Mặc dù ví dụ trên tìm khớp chính xác, bạn cũng có thể dùng toán tử để chỉ định phần văn bản bạn muốn khớp. Nếu bạn sửa tiêu chí tìm kiếm, bạn có thể tính tổng giá trị cho các ô có thể không khớp hoàn hảo nhưng cho câu trả lời bạn cần.
Sử dụng ví dụ trên, nếu bạn muốn cộng mua hàng của tất cả mặt hàng trừ cài vít điện (electric drill), bạn sẽ nhập công thức với toán tử <>.
=SUMIF(A2:A15,"<>Electric Drill",B2:B15)
Toán tử <> báo cho hàm SUMIF bỏ qua "Electric Drill" nhưng cộng tất cả các mặt hàng khác trong phạm vi B2:B15.
Như bạn thấy từ kết quả dưới đây, hàm SUMIF hoạt động như mong đợi.
Bạn cũng có thể dùng các toán tử sau khi dùng SUMIF với văn bản:
- ?: Tìm từ có bất kỳ ký tự nào ở vị trí bạn đặt ?. Ví dụ, "S?ovel" sẽ cộng bất kỳ mặt hàng nào bắt đầu bằng "S" và kết thúc bằng "ovel" với bất kỳ chữ cái nào ở giữa.
- *: Tìm từ bắt đầu hoặc kết thúc bằng bất cứ gì. Ví dụ, "*Watch" sẽ cộng tất cả mặt hàng là bất kỳ loại đồng hồ nào, bất kể thương hiệu.
Lưu ý: Nếu bạn thực sự muốn hàm SUMIF tìm kiếm ký tự như "?" hoặc "*" trong văn bản (chứ không dùng chúng như ký tự đặc biệt), hãy thêm ký tự tilde (~) phía trước. Ví dụ, "~?" sẽ bao gồm ký tự "?" trong văn bản tìm kiếm.
Hãy nhớ hàm SUMIF không phân biệt chữ hoa chữ thường. Vì vậy, nó không phân biệt chữ in hoa hoặc thường khi bạn dùng văn bản làm tiêu chí tìm kiếm. Điều này hữu ích vì nếu cùng một từ được nhập bằng chữ in hoa hoặc không, SUMIF vẫn nhận ra chúng khớp và tính tổng đúng các giá trị trong cột giá trị.
Sử dụng toán tử SUMIF với số
Tất nhiên, hàm SUMIF trong Google Sheets không chỉ hữu ích cho việc tìm văn bản trong cột có giá trị liên kết để tính tổng. Bạn cũng có thể tính tổng các phạm vi số đáp ứng điều kiện nhất định.
Để kiểm tra phạm vi số cho một điều kiện, bạn có thể dùng tập các toán tử so sánh.
- >: Lớn hơn
- <: Nhỏ hơn
- >=: Lớn hơn hoặc bằng
- <=: Nhỏ hơn hoặc bằng
Ví dụ, nếu bạn có danh sách số và muốn cộng những số lớn hơn 3000, bạn sẽ dùng lệnh SUMIF sau.
=SUMIF(B2:B15, ">3000")
Lưu ý rằng giống như với tiêu chí văn bản, bạn không cần gõ số "3000" trực tiếp vào công thức. Bạn có thể đặt số này vào một ô và dùng tham chiếu ô đó thay vì "3000" trong công thức.
Như sau:
=SUMIF(B2:B15, ">"&C2)
Một lưu ý cuối trước khi xem ví dụ. Bạn cũng có thể tính tổng tất cả giá trị trong phạm vi bằng một số cụ thể, chỉ bằng cách không dùng bất kỳ toán tử so sánh nào.
Ví dụ SUMIF với số
Hãy xem cách dùng hàm SUMIF trong Google Sheets bằng toán tử so sánh với số.
Trong ví dụ này, tưởng tượng bạn là người leo núi theo dõi tất cả các ngọn núi đã leo.
Ở ô D2, bạn muốn cộng tổng độ cao của tất cả ngọn núi trên 3000 feet bạn đã leo.
Để làm điều này, bạn sẽ dùng công thức đề cập ở phần trên.
Nhấn Enter sau khi gõ công thức, bạn sẽ thấy kết quả trong ô này.
Như bạn thấy, hàm SUMIF trong Google Sheets đã cộng tổng tất cả độ cao từ cột B cho bất kỳ ngọn núi nào cao hơn 3000 feet. Công thức SUMIF bỏ qua tất cả giá trị dưới độ cao đó.
Dùng các toán tử điều kiện khác ở phần trước để thực hiện cùng phép tính cho số nhỏ hơn, lớn hơn hoặc bằng, nhỏ hơn hoặc bằng, hoặc bằng.
Sử dụng toán tử SUMIF với ngày tháng
Bạn cũng có thể dùng hàm SUMIF với ngày tháng. Lại một lần, các toán tử so sánh giống như trên áp dụng, nên bạn không phải lo học toán tử mới.
Tuy nhiên, để hàm hoạt động, ngày tháng cần được định dạng đúng trong Google Sheets trước.
Bạn có thể gõ thủ công ngày vào hàm hoặc gõ vào ô và tham chiếu vào công thức. Định dạng như sau:
=SUMIF(B2:B15, ">10/4/2019", C2:C15)
Cách hoạt động:
- SUMIF sẽ kiểm tra phạm vi B2:B15 tìm bất kỳ ngày nào sau 10/4/2019.
- Nếu đúng, SUMIF sẽ cộng các ô trong C2:C15 cùng hàng nơi so sánh này đúng.
- Tổng kết quả sẽ hiển thị trong ô bạn gõ công thức.
Nếu bạn có ô ngày không định dạng cách này, bạn có thể dùng hàm DATE để định dạng lại đúng. Ví dụ, nếu bạn có ba ô (D2, D3, D4) chứa năm, tháng, ngày, bạn có thể dùng công thức sau.
Ví dụ:
=SUMIF(B2:B15, ">"&DATE(D2, D3, D4), C2:C15)
Nếu bạn có bảng tính chứa mua hàng mới nhất ở đầu trang, bạn có thể dùng hàm TODAY để chỉ tính tổng mua hôm nay và bỏ qua phần còn lại.
=SUMIF(B2:B15, TODAY())
SUMIF trong Google Sheets đơn giản nhưng đa dụng
Như bạn thấy, công thức SUMIF trong Google Sheets không tốn nhiều thời gian để học. Nhưng các cách khác nhau bạn có thể dùng nó làm cho nó rất đa dụng.
Nếu bạn dùng nhiều bảng tính cần tính tổng giá trị dựa trên điều kiện văn bản hoặc số từ ô khác, bạn nên làm quen với hàm SUMIF.