fbpx

Hàm SUMIF trong Excel, chi tiết cách sử dụng và ví dụ thực tế

Chia sẻ bài viết này:
  •  
  •  
  •  
  •  
  •  
  •  
  •   
  •   

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.

Hàm SUMIF trong Excel và ví dụ

Hàm SUMIF trong Excel và ví dụ

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:

Điều kiện Toán tử Công thức ví dụ Giải thích
Tính tổng nếu lớn hơn > =SUMIF(A2:A10, “>5”) Tính tổng các số lớn hơn 5 trong vùng A2:A10
Tính tổng nếu nhỏ hơn < =SUMIF(A2:A10, “<10”, B2:B10) Tính tổng các ô trong cột B, nếu các ô tương ứng, cùng dòng trong cột A có giá trị nhỏ hơn 10
Tính tổng nếu bằng = =SUMIF(A2:A10, “=”&D1) hoặc =SUMIF(A2:A10,D1) Tính tổng các số trong vùng A2:A10, nếu bằng D1
Tính tổng nếu không bằng <> =SUMIF(A2:A10, “<>”&D1, B2:B10) Tính tổng các ô trong cột B, nếu các ô tương ứng, cùng dòng trong cột A có giá trị khác giá trị ở D1
Tính tổng nếu lớn hơn hoặc bằng >= =SUMIF(A2:A10, “>=5”) Tính tổng các số lớn hơn hoặc bằng 5 trong vùng A2:A10
Tính tổng nếu nhỏ hơn hoặc bằng <= =SUMIF(A2:A10, “<=10”, B2:B10) Tính tổng các ô trong cột B, nếu các ô tương ứng, cùng dòng trong cột A có giá trị nhỏ hơn hoặc bằng 10

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

Điều kiện Công thức ví dụ Giải thích
Tính tổng nếu bằng chính xác =SUMIF(A2:A8, “chuối”, C2:C8) Tính tổng giá trị trong vùng C2:C8 nếu các ô tương ứng cùng dòng trong cột A có giá trị chính xác = “chuối”
Tính tổng nếu chứa chữ … =SUMIF(A2:A8, “*chuối*”, C2:C8) Tính tổng giá trị trong vùng C2:C8 nếu các ô tương ứng cùng dòng trong cột A có chứa chữ “chuối”
Tính tổng nếu không bằng =SUMIF(A2:A8, “<>chuối”, C2:C8) Tính tổng giá trị trong vùng C2:C8 nếu các ô tương ứng cùng dòng trong cột A có giá trị khác “chuối”
Tính tổng nếu không chứa =SUMIF(A2:A8, “<>*chuối*”, C2:C8) Tính tổng giá trị trong vùng C2:C8 nếu các ô tương ứng cùng dòng trong cột A không chứa chữ “chuối”

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

Hàm SUMIF trong Excel và ví dụ

Hàm SUMIF trong Excel và ví dụ

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
Hàm SUMIF với điều kiện chứa ký tự đại diện

Hàm SUMIF với điều kiện chứa ký tự đại diện

  • Để 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:

Hàm SUMIF và ký tự wildcard

Hàm SUMIF và ký tự wildcard

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

Hàm SUMIF theo điều kiện ô rỗng

Hàm SUMIF theo điều kiện ô rỗ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

Điều kiện Công thức ví dụ Giải thích
Tính tổng theo điều kiện là ngày cụ thể =SUMIF(B2:B9,”10/29/2020″,C2:C9) Tính tổng giá trị trong vùng C2:C9 nếu ô tương ứng cùng dòng trong vùng B2:B9 có giá trị bằng ngày 29 tháng 10 năm 2020
Tính tổng từ sau một ngày cụ thể =SUMIF(B2:B9,”>=10/29/2020″,C2:C9) Tính tổng giá trị trong vùng C2:C9 nếu ô tương ứng cùng dòng trong vùng B2:B9 có giá trị lớn hơn hoặc bằng ngày 29 tháng 10 năm 2020
Tính tổng từ sau một ngày cụ thể, với giá trị ngày được lưu trong 1 ô khác =SUMIF(B2:B9,”>”&F1,C2:C9) Tính tổng giá trị trong vùng C2:C9 nếu ô tương ứng cùng dòng trong vùng B2:B9 có giá trị lớn hơn ngày được lưu trong ô F1

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:

Điều kiện Công thức
Tính tổng dựa vào ngày hiện tại =SUMIF(B2:B9, TODAY(), C2:C9)
Tính tổng giá trị trước ngày hôm nay =SUMIF(B2:B9, “<“&TODAY(), C2:C9)
Tính tổng giá trị sau ngày hôm nay =SUMIF(B2:B9, “>”&TODAY(), C2:C9)
Tính tổng giá trị vào ngày này tuần tới =SUMIF(B2:B9, “=”&TODAY()+7, C2:C9)

Để 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, 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


Chia sẻ bài viết này:
  •  
  •  
  •  
  •  
  •  
  •  
  •   
  •