Bài tút này sẽ giúp các bạn hiểu rõ cách sử dụng hàm SUMIF trong Excel qua những ví dụ thực tế để có thể giải quyết được nhu cầu tính tổng theo điều kiện rất phổ biến và cần thiết khi làm báo cáo.

Một khi bạn hiểu rõ cách sử dụng hàm SUMIF thì việc tìm hiểu và áp dụng một số hàm tính tổng theo nhiều điều kiện SUMIFS, đếm theo điều kiện – COUNTIF, tính trung bình theo điều kiện AVERAGEIFS khác cũng sẽ rất nhanh chóng và dễ dàng.

Hàm SUMIF – cú pháp và cách viết

Đầu tiên, chúng ta hãy cùng nhau nhìn vào ví dụ sau đây.

Yêu cầu trong trường hợp này: chúng ta cần tính sản lượng xuất khẩu của sản phẩm Chuối, hay nói cách khác: tính tổng cột B ở các dòng có chữ Chuối ở cột A.

Hàm SUMIF hay hàm tính tổng theo điều kiện trong Excel được dùng để cộng giá trị trong các ô trên Excel theo điều kiện. Cú pháp của hàm SUMIF như sau:

=SUMIF(range, criteria, [sum_range])

Hàm SUMIF nhận 3 tham số, 2 tham số đầu tiên là bắt buộc, tham số thứ 3 không bắt buộc:

• range: là vùng chứa điều kiện cần so sánh, trong ví dụ trên là vùng A2:A7
• criteria: là điều kiện dùng để so sánh, trong ví dụ trên, từ “Chuối” được lưu trong ô E1 giúp chúng ta linh hoạt thay đổi hơn, không cần vào tận công thức ở E2 để thay đổi công thức khi điều kiện thay đổi
• [sum_range]: vùng chứa dữ liệu cần tính tổng, vì tham số này không bắt buộc nên, nếu bạn bỏ qua tham số này, thì Excel sẽ hiểu và tính tổng trên vùng range. Trong ví dụ trên, sum_range là B2:B7

Hàm SUMIF – các điều kiện đi kèm

Hàm SUMIF tính tổng theo điều kiện so sánh với số

Hàm SUMIF có thể dùng trong trường hợp tính tổng theo điều kiện cụ thể so sánh với một con số, các phép toán cụ thể được trình bày cùng ví dụ công thức trong bảng sau đây:

Hàm SUMIF tính tổng theo điều kiện so sánh với chuỗi

Cách viết hàm SUMIF so sánh với tham chiếu ô

Trong ví dụ ở ảnh minh hoạ trên, chúng ta đã sử dụng hàm SUMIF cùng với điều kiện được tham chiếu tới 1 ô trong bảng tính Excel. Ngoài cách viết này, chúng ta còn có thể viết công thức SUMIF trong trường hợp này như sau:

=SUMIF(A2:A7,"="&E1,B2:B7)

Trong công thức ví dụ sử dụng hàm SUMIF để tính tổng khi so sánh điều kiện với chuỗi, chúng ta viết điều kiện vào ngay trong công thức cho dễ hình dung như sau:

=SUMIF(A2:A8, "chuối", C2:C8)

cho mục đích ví dụ thì việc này không có vấn đề gì tuy nhiên, trong thực tế sử dụng, bạn nên viết tách rời phần điều kiện ra 1 ô khác, sau đó sử dụng hàm SUMIF tham chiếu tới ô đó, như vậy chúng ta có thể hạn chế việc sửa công thức khi thay đổi điều kiện như sau (giả sử điều kiện được lưu trong ô E1

=SUMIF(A2:A8, E1, C2:C8)

Đối với trường hợp sử dụng SUMIF với ký tự dấu sao *, thì chúng ta có thể chuyển công thức

=SUMIF(A2:A8, "<>*chuối*", C2:C8)

thành:

=SUMIF(A2:A8, "<>*" & E1 & "*", C2:C8)

Cách viết hàm SUMIF với điều kiện kèm ký tự đại diện

Cũng giống như một số hàm Excel khác, hàm SUMIF hỗ trợ viết điều kiện kèm theo ký tự đại diện là dấu * hoặc dấu ?

• Dấu * đại diện cho bất kỳ số lượng ký tự nào
• Dấu ? đại diện cho 1 ký tự bất kì tại một vị trí xác định

• Để tính tổng những ô có chứa “Chuối”, bạn có thể dùng công thức
  =SUMIF(A2:A7, "<>*" & E1 & "*", B2:B7)
• Để tính tổng những ô mà giá trị trong ô đó bắt đầu bằng “Chuối”, bạn có thể dùng công thức
  =SUMIF(A2:A7, E1 & "*", B2:B7)

• Để tính tổng những ô mà giá trị trong ô đó kết thúc bằng “Chuối”, bạn có thể dùng công thức
  =SUMIF(A2:A7,"*" & E1, B2:B7)

Cũng với hình minh hoạ trên, nếu bạn muốn tính tổng các dòng ở cột B, mà dữ liệu ở cột A có chính xác 3 ký tự (Cam, Táo), thì công thức SUMIF trong trường hợp này sẽ có thể viết như sau:

=SUMIF(A2:A7,"???", B2:B7)

Làm gì khi trong kết quả cũng có dấu * hoặc dấu ?

Khi vùng dữ liệu để kiểm tra điều kiện có dấu * hoặc dấu ?, thì chúng ta cần cách để có thể nói cho Excel biết điều này, trong trường hợp như vậy, chúng ta sẽ dùng dấu ngã ~, ví dụ như sau:

Trong ví dụ này, chúng ta cần tính tổng của loại sản phẩm “siêu sao” – có dấu * ở trong tên, công thức cho trường hợp này là:

=SUMIF(A2:A8,"*~**",B2:B8)

Phần ~* trong công thức báo cho Excel biết điều kiện chúng ta cần tìm có dấu *. 2 dấu * về 2 phía của ~* báo cho Excel biết chúng ta muốn tìm kiếm bất cứ ký tự nào về 2 phía của dấu * đó. Kết quả là “Chuối *” được tìm ra, và công thức SUMIF trả về giá trị là 1000.

Cách viết hàm SUMIF để tính tổng theo ô trống

Trong ví dụ ở ảnh trên, chúng ta cần tính tổng cột B tương ứng với ô không có dữ liệu (ô trống trong cột A), công thức cho trường hợp này đơn giản như sau:

=SUMIF(A2:A8,"=",B2:B8)

Cách viết hàm SUMIF để tính tổng theo ô không trống

Cùng với hình minh hoạ cho trường hợp ô trống, để tính tổng cột B tương ứng với những ô không rỗng ở cột A, chúng ta viết công thức SUMIF như sau:

=SUMIF(A2:A8,"<>",B2:B8)

Sử dụng hàm SUMIF với điều kiện ngày tháng năm

Thường thì chúng ta sẽ sử dụng hàm SUMIF để tính tổng theo điều kiện với ngày tháng năm cũng tương tự như cách chúng ta đã biết khi sử dụng với dữ liệu số, dữ liệu dạng chuỗi. Tuy nhiên với dữ liệu kiểu ngày tháng thì chúng ta có 1 số lưu ý và cách viết như sau

Nếu công thức SUMIF của bạn không hoạt động, hãy kiểm tra lại cách nhập ngày tháng trong Excel trên máy của bạn

Thay vào tham số điều kiện trong trường hợp tính tổng theo điều kiện sử dụng ngày tháng phía trên, bạn có thể sử dụng các hàm TODAY() cho vùng điều kiện này. Ví dụ như sau:

Để tính tổng theo điều kiện trong 1 khoảng ngày, ví dụ khoảng ngày của chúng ta là “trong tháng 1 năm 2020”, thì công thức chúng ta có thể viết như sau

=SUMIF(B2:B9, ">=1/1/2020", C2:C9) - SUMIF(B2:B9, ">=2/1/2020", C2:C9)

Hi vọng qua bài viết này của Học Excel Online bạn có thể nắm và áp dụng được những kiến thức cơ bản về hàm SUMIF để áp dụng vào thực tế công việc. Bạn có thể theo dõi thêm video sau đây để hiểu thêm về hàm SUMIF