Cách dùng hàm SUMIF trong Excel

Chúng ta đã biết cách tính tổng các ô trong Excel với hàm SUM. Nhưng nếu phải tính tổng có điều kiện thì làm thế nào ? Thật may mắn Excel hỗ trợ chúng ta tính tổng các dữ liệu có điều kiện với hàm SUMIF. Dưới đây là hướng dẫn cách dùng hàm SUMIF trong Excel các bạn cùng chú ý theo dói nhé.

Cú pháp hàm SUMIF

Hàm SUMIF trong Excel còn được gọi là hàm tính tổng có điều kiện, được sử dụng để tính tổng các ô, dải ô dựa trên một điều kiện hay tiêu chí nhất định.

Nếu đã từng sử dụng hàm COUNTIF thì chắc chắn hàm SUMIF này sẽ không gây khó khăn cho bạn đơn giản vì cú pháp và cách sử dụng của nó SUMIF tương tự như COUNTIF.

SUMIF (range, criteria, [sum_range])

Trong 3 đối số của hàm SUMIF thì 2 đối số đầu là bắt buộc, đối số thứ 3 là tùy chọn

• range: dải của các ô được đánh giá theo tiêu chí của bạn, ví dụ A1: A15
• criteria: điều kiện/tiêu chí cần phải đáp ứng. Các tiêu chí có thể được cung cấp dưới dạng số, văn bản, ngày, biểu thức logic, tham chiếu ô, hoặc một hàm Excel khác. Ví dụ: bạn có thể nhập các tiêu chí như "16", "khoai tây", "10/25/2015", "<5", "A5", v.v ..
  • Lưu ý: Hãy chú ý rằng bất kỳ điều kiện dạng văn bản hoặc điều kiện có chứa các ký hiệu toán học thì đều phải được đặt trong dấu nháy kép (""). Đối với các điều kiện số, dấu nháy kép là không bắt buộc.
• [sum_range]:các ô dùng để tính tổng, nếu điều kiện được đáp ứng. Đối số này là tùy bạn chọn, và bạn chỉ cần sử dụng nó nếu bạn muốn tính tổng các ô khác (mà đã được định nghĩa trong đối số range). Nếu đối số sum_range bị bỏ qua, khi đó những ô trong phạm vi đánh giá range sẽ được thay thế.

Để minh họa cú pháp hàm SUMIF rõ ràng hơn chúng ta xét ví dụ dưới đây. Giả sử bạn có một danh sách cán mặt hàng trong cột A và số lượng tương ứng bán ra ở cột B. Bài toán đặt ra là cho biết tổng tất cả các mặt hàng đã bán ra liên quan đến một mật hàng nhất định, trong ví dụ này chúng ta tính tổng táo bán ra

Các đối số trong công thức SUMIF của chúng ta như sau:

• range: A2:A12
• criteria: "Táo"
• [sum_range]:B2:B12

Công thức của chúng ta lúc này như sau:

=SUMIF(A2:A12,"Táo",B2:B12)

Tuy nhiên việc truyền điều kiện bằng văn bản lại không tối ưu nếu ta muốn thay đổi sản phẩm. Vì vậy chúng ta cần sử dụng tham chiếu ô trong công thức này. Chúng ta viết lại công thức như sau: Với F2 là ô chứa Táo

=SUMIF(A2:A12,F2,B2:B12)

kết quả cho ra vẫn y nguyên 

Chú thích: Tham số sum_range thực sự chỉ xác định ô trên cùng bên trái của dải được tính tổng. Khu vực còn lại thì lại được xác định bởi kích thước của đối số range. Điều này có nghĩa là trong thực tế sum_range không nhất thiết phải có cùng kích thước với range , nghĩa là sum_range có thể khác range về số lượng hàng và cột. Tuy nhiên, ô đầu tiên (tức là ô trên cùng bên trái) của dải tính tổng (sum_range) phải luôn luôn ở đúng cột. Ví dụ, trong công thức SUMIF ở trên, bạn có thể lấy ô B3, hoặc B3:B5, hay thậm chí là B2: B99 làm đối số sum_range và kết quả vẫn sẽ như cũ. Tuy nhiên, tốt nhất vẫn là cung cấp range và sum_range có cùng kích thước.

Ví dụ minh họa sử dụng SUMIF trong EXCEL 

Hy vọng rằng, ví dụ trên đã giúp bạn hiểu cơ bản được cách hoạt động của hàm SUMIF. Dưới đây bạn sẽ tìm thấy một vài công thức cho biết rằng làm thế nào để sử dụng SUMIF trong Excel với các tiêu chí khác nhau và loạt dữ liệu khác nhau.

SUMIF với dấu lớn hơn, nhỏ hơn hoặc bằng

Trong các công thức SUMIF chúng ta hoàn toàn có thể sử dụng các biểu thức so sánh đi kèm với số cụ thể hoặc tham chiếu ô. Nếu tham chiếu ô chúng ta phải thêm dấu & trước tham chiếu để tránh lỗi

Lưu ý: Trong các công thức hàm SUMIF, một dấu so sánh theo sau bởi một số hoặc một văn bản phải luôn luôn được đặt trong dấu nháy kép ("").

Cách sử dụng hàm SUMIF với điều kiện là văn bản

Ngoài các con số, hàm SUMIF còn cho phép bạn tính tổng dựa trên điều kiện một ô tương ứng trong một cột khác có thể chứa một văn bản cho trước hay không. SUMIF có các công thức khác nhau để đối chiếu với điều kiện văn bản khác nhau như chính xác văn bản hay chính xác từng phần. Dưới đây là bảng tóm tắt.

Để biết thêm thông tin về giá trị khớp 1 phần, vui lòng xem ví dụ về hàm SUMIF với các kí tự đại diện.

Và bây giờ, hãy xem công thức chính xác “Tính tổng nếu khác” qua các thao tác. Như được minh họa trong ảnh chụp màn hình bên dưới, nó cho biết lượng tồn kho của tất cả các sản phẩm khác ngoài “Táo”:

=SUMIF(A2:A11,"<>*Táo*",B2:B11)

Lưu ý:  Giống như hầu hết các hàm Excel khác, SUMIF không phân biệt chữ hoa chữ thường, có nghĩa là dù bạn nhập Táo hay táo đều giống nhau

Sử dụng dấu so sánh với tham chiếu ô

Nếu bạn muốn có một công thức hàm SUMIF tối ưu hơn, bạn có thể thay thế giá trị số hoặc văn bản trong tiêu chí bằng một ô tham chiếu, như sau:

=SUMIF(A2:A11,"<>"&F2,B2:B11)

Sử dụng tham chiếu ô sẽ giúp bạn không phải thay đổi công thức để tính tổng có điều kiện dựa trên các tiêu chí khác mà bạn chỉ cần nhập một giá trị mới vào một ô được tham chiếu mà không phải viết lại công thức

Chú ý. Khi bạn sử dụng một biểu thức logic với một tham chiếu ô, bạn phải sử dụng dấu nháy kép ("") để bắt đầu một chuỗi văn bản và dấu và (&) để nối/kết thúc chuỗi, ví dụ "<>"&F2

Dấu bằng (=) có thể được bỏ qua trước một tham chiếu ô, do đó cả hai công thức dưới đây là mang tính tương đương và chính xác:

Công thức 1: = SUMIF(A2:A11,"="&F2,B2:B11)

Công thức 2: = SUMIF(A2:A11,F2,B2:B11

Hàm SUMIF sử dụng ký tự đại diện

Khi sử dụng SUMIF với điều kiện là văn bản không chỉ đối chiếu văn bản chính xác mà SUMIF còn có khả năng làm việc với đối chiếu từng phần. Khi đó bạn cần phải sử dụng các kí tự đại diện trong công thức SUMIF như sau:

• Dấu hoa thị (*) – đại diện cho nhiều kí tự
• Dấu hỏi (?) – đại diện cho một kí tự duy nhất ở một vị trí cụ thể

Ví dụ sử tính tổng dựa trên đối chiếu từng phần

Giả sử, bạn muốn tính tổng số tiền liên quan đến tất cả các loại Táo. Thì các công thức SUMIF sau sẽ chạy như sau:

• =SUMIF(A2:A12,"Táo"&"*",B2:B12) : Tính tổng tất cả các sản phẩm có tên bắt đầu bằng Táo
• =SUMIF(A2:A12,"*"&"Táo"&"*",B2:B12) : Tính tổng tất cả các sản phẩm có tên chứa từ Táo 
• =SUMIF(A2:A12,"*"&"Táo",B2:B12) : Tính tổng tất cả các sản phẩm kết thúc bằng từ Táo

Chúng ta có thể thay văn bản Táo bằng tham chiếu ô cho dễ sửa đổi. Lúc này công thức có dạng

• =SUMIF(A2:A12,F2&"*",B2:B12) : Tính tổng tất cả các sản phẩm có tên bắt đầu bằng Táo
• =SUMIF(A2:A12,"*"&F2&"*",B2:B12) : Tính tổng tất cả các sản phẩm có tên chứa từ Táo 
• =SUMIF(A2:A12,"*"&F2,B2:B12) : Tính tổng tất cả các sản phẩm kết thúc bằng từ Tá

Kết lại nếu bạn muốn tính tổng những ô bắt đầu hoặc kết thúc với 1 văn bản nhất định thì chỉ cần thêm 1 dấu sao (*) vào trước hoặc sau văn bản như ở trên là được

Ví dụ 2: Tính tổng những ô với số ký tự nhất định

Trong trường hợp bạn muốn tính tổng một số giá trị nếu giá trị này có chính xác 4 chữ cái rất đơn giản trong điều kiện truyền vào đối số sau "????" và lúc đó công thức trở thành như sau:"

=SUMIF(A2:A12,"????",B2:B12) 

Ví dụ 3: Tính tổng những ô có điều kiện tương ứng là văn bản

Nếu bảng tính của bạn chứa nhiều loại dữ liệu khác nhau và bạn chỉ muốn tổng các ô tương ứng với giá trị văn bản, thì các công thức SUMIF sau sẽ có ích cho bạn:

• =SUMIF (A2:A11,"?*",B2:B11) : Công thức cho kết quả cộng giá trị trong các ô B2:B11  nếu ô tương ứng trong cột A chứa ít nhất 1 kí tự.
• =SUMIF (A2:A11,"*",B2:B11) Tính tổng bao gồm các ô rỗng, chứa các chuỗi có độ dài bằng 0 (là kết quả của các công thức khác), ví dụ: = “”.

Cả hai công thức trên đều bỏ qua các giá trị không phải là văn bản như các lỗi, các phép toán luận, các chữ số và ngày tháng.

Ví dụ 4: Tính tổng những ô có điều kiện sử dụng * hoặc ? như ký tự thường

Nếu bạn muốn * hoặc ? được coi như là một chữ chứ không phải là một kí tự đại diện, thì hãy sử dụng dấu ngã trước đó (~). Ví dụ: công thức SUMIF sau sẽ thêm các giá trị trong các ô B2:B11 nếu một ô trong cột A nằm trong cùng một hàng mà chứa một dấu chấm hỏi:

= SUMIF (A2:A11,"~?",B2:B11)

Tính tổng các ô có điều kiện trống và không trống

Một ô trống thì có nghĩa là các ô đó hoàn toàn không có chứa gì không có công thức, không có chuỗi chiều dài bằng 0 được trả lại bởi một số hàm Excel khác, khi đó bạn có thể sử dụng “=” làm tiêu chí, như trong công thức SUMIF sau:

= SUMIF(A2:A11 “=”,B2:B11)

Nếu "trống" bao gồm các chuỗi độ dài bằng không (ví dụ: ô có công thức như =""), thì bạn có thể sử dụng "" làm tiêu chí:

= SUMIF(A2: A11,"", B2:B11)

Cả hai công thức trên đều đánh giá các ô trong cột A và nếu có bất kỳ ô trống nào được tìm thấy, các giá trị tương ứng từ cột B sẽ được thêm vào.

Tỉnh tổng các ô tương ứng với các ô không trống

Nếu bạn muốn tính tổng các giá trị của ô trong cột B khi một ô tương ứng trong cột A không rỗng, hãy sử dụng “<>” làm tiêu chí ( điều kiện) trong công thức SUMIF của bạn:

= SUMIF (A2:A11,“<>”,B2:B11)

Công thức trên tính tổng các giá trị tương ứng với tất cả các ô không rỗng, bao gồm các chuỗi có độ dài bằng không.

Sử dụng hàm SUMIF với ngày tháng

Nói chung, bạn sử dụng hàm SUMIF để tính tổng một cách có điều kiện các giá trị dựa trên ngày cũng giống như cách bạn sử dụng văn bản và các tiêu chí số.

Nếu bạn muốn tính tổng các giá trị  nếu các giá trị tương ứng lớn hơn, nhỏ hơn hoặc bằng so với ngày bạn chỉ định, thì hãy sử dụng dấu so sánh mà chúng tôi đã thảo luận từ những bài trước. Và dưới đây là một vài ví dụ công thức hàm SUMIF:

Trong trường hợp bạn muốn tính tổng các giá trị dựa trên ngày hiện thời, thì bạn phải sử dụng hàm SUMIF kết hợp với hàm TODAY(), như sau:

Tính tổng các giá trị ứng với thời gian nằm giữa 2 ngày

Làm cách nào để tính tổng các giá trị ứng với thời gian nằm giữa hai ngày?” Mặc dù có thể sử dụng hàm SUMIFS để giải bài toán này nhưng chúng ta cũng có thể sử dụng hàm SUMIF như sau

= SUMIF (B2:B9,">=10/1/2014",C2: C9)–SUMIF(B2:B9,">=11/1/2014", C2:C9)

Công thức này tính tổng các giá trị trong các ô C2:C9 nếu ngày trong cột B nằm giữa ngày 1/10/2014 và ngày 31/10/2014

Phân tích công thức:

• Hàm SUMIF đầu tiên tính tổng các ô trong C2: C9 nếu ô tương ứng trong cột B lớn hơn hoặc bằng ngày bắt đầu (trong ví dụ này là ngày 1 tháng 10). 
• Tiếp đó, bạn chỉ cần phải loại đi bất kỳ giá trị nào xảy ra vào sau ngày kết thúc (Oct-10 hay ngày 31/10) – là những giá trị được trả về bởi hàm SUMIF thứ hai.

Lỗi Hàm SUMIF, SUMIF không hoạt động

Có thể có vài lý do khiến Hàm SUMIF không hoạt động hoặc có đôi khi công thức của bạn sẽ không trả lại những gì bạn mong đợi chỉ vì kiểu dữ liệu trong một ô hoặc trong một số đối số không phù hợp với hàm SUMIF. Do đó, dưới đây là một danh sách những điều cần kiểm tra:

Range và Sum_Range phải là cacs dải không phải mảng

Các tham số đầu tiên (range) và thứ ba (sum_range) trong công thức SUMIF của bạn phải luôn là một tham chiếu dải, như A1: A10. Nếu bạn bất chấp mà bỏ qua điều này, ví dụ là trường hợp tham chiếu một mảng như {1,2,3}, thì Excel với trả lại với một thông báo lỗi.

• Công thức đúng: = SUMIF (A1: A3, “Táo”, C1: C3)
• Công thức sai: = SUMIF ({1,2,3}, “Táo”, C1: C3)

Tham chiếu đến bảng tính bị đóng

Củng giống như hầu hết các hàm Excel khác, SUMIF có thể tham chiếu đến các trang tính và bảng tính khác, miễn là chúng vẫn đang mở.

Tuy nhiên, khi bảng tính bị đóng SUMIF sẽ trả về một thông báo lỗi #VALUE!.

Chắc chằn rằng Range và Sum_Range có cùng kích cỡ

Như đã lưu ý ở phần đầu của hướng dẫn này, trong các phiên bản hiện đại của Microsoft Excel, các tham số range và sum_range không nhất thiết phải bằng nhau. Từ phiên bản Excel 2003 trở về trước, range và sum_range có kích thước không đồng đều có thể gây ra vấn đề. Tuy nhiên, ngay cả trong các phiên bản mới nhất của Excel 2016 và 2019, các công thức phức tạp SUMIF mà ở đó, nếu sum_range có ít hàng và/hoặc cột hơn range, thì sẽ không hợp lệ. Đó là lý do tại sao lời khuyên người dùng chú ý rằng phải luôn luôn giữ đối số range và sum_range cùng kích thước.