Hàm SUMIF trong Excel

Hàm SUM rất quen thuộc với chúng ta trong Excel. Nó trả về tổng của một bảng một chiều mà không cần kiểm tra điều kiện gì. Trong nhiều trường hợp, chúng ta không muốn cộng các số trong bảng đó với các điều kiện kèm theo. Khi đó lựa chọn hàm SUMIF sẽ rất hiệu quả. Các thể hiện và cách sử dụng nó sẽ được CongNghePlus chia sẻ chi tiết trong bài viết này

Chắc hẳn là bạn đã nhiều lần sử dụng hàm SUM, lấy tổng của nhiều ô liên tiếp. Tuy nhiên trong một số trường hợp chúng ta không muốn lấy tổng tất cả các ô liên tiếp đó, mà phải kèm theo một điều kiện nào đó. Khi đó hàm SUMIF là giải pháp rất hợp lý. Hãy cùng CongNghePlus khám phá hàm SUMIF là gì và cách sử dụng hàm này như thế nào ngay sau đây.

Hàm SUMIF trong Excel

SUMIF là hàm gì?

Microsoft Excel có một số chức năng để tổng hợp các tập dữ liệu lớn cho các báo cáo và phân tích. Một trong những hàm hữu ích nhất có thể giúp bạn hiểu được một tập hợp dữ liệu đa dạng là hàm SUMIF.

Hàm SUMIF thay vì cộng tất cả các số trong một phạm vi thì hàm SUMIF cho phép bạn chỉ tính tổng những giá trị đáp ứng điều kiện của bạn.

Ví dụ 1: Giả sử rằng trong một cột có chứa số, bạn chỉ muốn tính tổng các giá trị lớn hơn 5. Bạn có thể sử dụng công thức sau: = SUMIF (B2: B25, “> 5”).

Ví dụ 2: Giả sử chúng ta có một bảng như hình bên dưới.

Ví dụ hàm SUMIF

Cần thống kê doanh số theo nhân viên và theo nhóm. Ta thấy rằng điều kiện lấy tổng, đó là phải tìm theo tên nhân viên hoặc tên nhóm khi lấy tổng doanh số.

Lúc này hàm SUMIF sẽ giúp giải quyết nhanh chóng bài toán của chúng ta như mô tả ngay sau đây. Tuy nhiên, hàm SUMIF có rất nhiều thể phức tạp khác như hàm SUMIF kết hợp với các hàm khác trong Excel để tạo ra các công thức tính toán giải quyết các bài toán phức tạp hơn.

Cách sử dụng hàm SUMIF cơ bản

Trước khi xét các biến thể và kết hợp của hàm SUMIF với các hàm khác, chúng ta hãy xem trường hợp đơn giản nhất của hàm.

Công thức hàm SUMIF

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

=SUMIF(range, criteria, [sum_range])

Trong đó:

  • Range: Phạm vi các ô bạn muốn đánh giá theo tiêu chí Criteria. Các ô trong mỗi phạm vi phải là số hoặc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua. Phạm vi được chọn có thể chứa các ngày ở định dạng Excel tiêu chuẩn.
  • Criteria: Tiêu chí xác định các giá trị được tính tổng. Nó có thể là số, biểu thức hoặc một chuỗi văn bản.
  • Sum_range: Tham số này là tùy chọn, nó sẽ chỉ ra các ô cần tính tổng. Nếu để trống sum_range, những ô trong phạm vi đánh giá sẽ được thay thế.

» Xem thêm: Cách sử dụng hàm VLOOKUP cơ bản

Ví dụ cách dùng hàm SUMIF

Trở lại bài toán cần thống kê doanh số theo nhân viên và theo nhóm ở trên.

Cú pháp =SUMIF(range, criteria, [sum_range]) được áp dụng với các thông số (áp dụng cho G2 đến G9):

=SUMIF(B$2:B$13, F2, D$2:D$13)

Sẽ thực hiện thao tác: Dò tìm biến số F2 trong bảng B$2:B$13. Khi tìm thấy thì cộng giá trị tương ứng cùng hàng trong bảng D$2:D$13.

Tương tự áp dụng cho phạm vi từ G11 đến G13: =SUMIF(C$2:C$13,F11,D$2:D$13)

Kết quả cho ra như sau:

Kết quả cho hàm SUMIF

Chúng ta thấy hàm SUMIF hoạt động rất giống hàm VLOOKUP. Khác nhau ở chỗ hàm VLOOKUP chỉ trả về một giá trị, trong khi hàm SUMIF trả về tổng tích lũy.

Cách sử dụng hàm SUMIF nâng cao

Biến số của hàm SUMIF

Trở lại cú pháp của hàm SUMIF: =SUMIF(Range, Criteria, [sum_range])

Biến số Criteria (Tiêu chí) có thể được áp dụng cho ngày, số và văn bản. Hàm SUMIF hỗ trợ các toán tử logic (>, <, <>, =) và các ký tự đại diện (*,?) để đối sánh từng phần.

Hàm SUMIF nhiều điều kiện

Trở lại với bảng doanh số, giả sử chúng ta cần tính tổng doanh số của nhân viên “Trần Văn Cường” và “Đậu Xuân Trung”. Đó là bài toán với tham biến nhiều giá trị. Như đã nói, đó là trường hợp biến Criteria là một mảng. Bạn chú ý rằng Criteria hoặc nhận giá trị này, hoặc nhận giá trị kia trong mảng.

SUMIF nhiều điều kiện

Đôi khi ta gọi đó là bài toán hàm SUMIF hai (hay nhiều) điều kiện.

Trở lại bài toán sau đây, chúng ra cần tính tổng doanh số của nhóm Cường và Trung.

Như đã nói, nếu biến criteria chạy qua một mảng giá trị, hàm SUMIF cũng quét qua một mảng. Nếu sử dụng hàm SUM cho mảng đó ta có lời giải cho bài toán tính tổng doanh số nêu ở trên.

Ở ô G2 bạn gõ hàm: =SUM(SUMIF(B2:B13,{“Đậu Xuân Trung”,”Trần Văn Cường”},D2:D13))

Và sẽ có kết quả như sau:

Hàm SUM lồng SUMIF nhiều điều kiện

Hàm SUMIFS là gì? Phân biệt SUMIF và SUMIFS

Hàm SUMIFS là hàm lấy tổng theo nhiều điều kiện, nó có thể được coi như là hàm tổng hợp giữa hàm SUMIF và toán tử nhiều điều kiện.

Hàm SUMIFS có cú pháp như sau:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Trong đó:

  • Sum_range: là các ô cần tính tổng trong bảng dữ liệu, những giá trị trống và giá trị text được bỏ qua, tham số bắt buộc phải có.
  • Criteria_range1: phạm vi cần được kiểm tra bằng điều kiện criteria1, là giá trị bắt buộc.
  • Criteria1: điều kiện áp dụng cho criteria_range1, có thể là một số, biểu thức, tham chiếu ô để xác định ô nào trong criteria_range1 sẽ được tính tổng, cũng là giá trị bắt buộc.
  • Criteria_range2, criteria2,… : tùy chọn các phạm vi và điều kiện bổ sung, tối đa 127 cặp điều kiện.

Một ví dụ về hàm SUMIFS giúp bạn dễ hình dung hơn.

Giả sử hãy dùng lại bảng cũ, nhưng bây giờ chúng ta cài thêm giả thiết, là có hai người cùng tên “Trần Văn Cường” ở hai nhóm khác nhau. Như vậy, trong khi kiểm tra điều kiện để cộng, chúng ta phải kiểm tra hai điều kiện đồng thời.

Hàm SUMIFS

Như đã thấy, hàm SUMIFS với thông số: =SUMIFS(D$2:D$13,B2:B13,F2,C2:C13,G2) đã cho kết quả như mong muốn.

Kết hợp hàm SUMIF và VLOOKUP

Trong trường hợp bảng bị tách ra thành các bảng con thì thông số criteria có thể không nằm trong cột nữa mà nó phải được dò tìm ở một bảng khác. Thao tác dò tìm thường được tiến hành bởi hàm VLOOKUP. Khi đó, hàm VLOOKUP là tham biến cho hàm SUMIF.

Ví dụ này cho thấy cách sử dụng hàm SUMIF kết hợp với hàm VLOOKUP để tìm tổng doanh số bán hàng trong cùng một tháng qua các năm khác nhau.

Xem xét bài toán trong hình ảnh dưới đây.

Ví dụ hàm SUMIF kết hợp với hàm VLOOKUP

Bảng tra cứu được xem xét như hình bên dưới. Ngoài ra, nó bao gồm các giá trị tham chiếu cho các tháng từ tháng 1 đến tháng 12.

Ví dụ hàm SUMIF kết hợp với hàm VLOOKUP

Sau khi nhập dữ liệu vào bảng chính và bảng tra cứu, hàm SUMIF xác định tổng doanh số phát sinh trong các tháng khác. Ở đây, giá trị Tra cứu được coi là tháng. Công thức kết hợp giữa hàm SUMIF và hàm VLOOKUP được hiển thị như sau:

Ví dụ hàm SUMIF kết hợp với hàm VLOOKUP

Như vậy, kết quả cho ra tổng doanh thu trong tháng 1 được xác định là 17263,3. Khi chúng tôi thay đổi giá trị tra cứu sang tháng khác, tổng doanh số bán hàng tương ứng sẽ được tạo ra.

Cách sử dụng hàm SUMIF giữa 2 sheet

Nếu các bảng con được tách ra nằm ở các sheet khác nhau thì khi đó hàm SUMIF cần tham chiếu đến nhiều sheets khác nhau, lúc đó cú pháp của nó sẽ có dạng:

=SUMIF(Sheet3!B$2:B$13,F2,D$2:D$13)

Tức là bạn chỉ cần có thêm phần chỉ định vị trí trang Sheet.

Cách dùng hàm SUMIF trong Google Sheet

Trong Google Sheets, các tham số được phân cách bởi dấu “;” chứ không phải là dấu “,” như trong Excel. Ngoài ra các thao tác là giống nhau các bạn nhé.

Hàm SUMIF trong Google Trang tính

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

  • Trở lại cú pháp: SUMIF(range, criteria, [sum_range]). Khi bạn không điền dữ liệu vào phần sum_range, tổng sẽ được tính theo range.
  • Điều kiện có chứa chữ hoặc ký hiệu toán học đều phải được đặt trong dấu ngoặc kép ” “.
  • Phạm vi xác định dạng số có thể được cung cấp là số sẽ không phải dùng đến dấu ngoặc.
  • Hàm SUMIF trả về kết quả không chính xác khi bạn dùng với các chuỗi dài hơn 255 ký tự hoặc với chuỗi #VALUE!.

Như vậy, với bài viết mà CongNghePlus chia sẻ kiến thức về hàm SUMIF trong Excel. Nếu bạn muốn nâng cao thêm kỹ năng sử dụng phần mềm quản lý và thống kê số liệu Microsoft Excel, hãy tham khảo thêm một số bài viết hay sau đây:

Nếu thấy bài viết này hữu ích với bạn, hãy Like, Share và đánh giá bài viết ở phần dưới đây để ủng hộ CongNghePlus có thêm động lực phát triển hơn trong thời gian tới nhé!

Đánh giá bài viết: