Cách dùng hàm SUMIF trong Excel
Trình duyệt của bạn không hỗ trợ nghe audio.
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 (“”).
Tiêu chí
Dấu nối
Ví dụ công thức
Mô tả
Tính tổng nếu lớn hơn
>
=SUMIF(A2:A10, “>15”)
Tính tổng các giá trị lớn hơn 15 trong các ô A2:A10.
Tính tổng nếu nhỏ hơn
<
=SUMIF(A2:A10, “<15”, B2:B10)
Tính tổng các giá trị trong các ô B2:B10 nếu giá trị tương ứng trong cột A nhỏ hơn 15.
Tính tổng nếu bằng
(có thể bỏ qua)
=
=SUMIF(A2:A10, “=”&D1)
hoặc
=SUMIF(A2:A10,D1)
Tính tổng các giá trị trong các ô A2:A10 mà có giá trị bằng với giá trị trong ô D1.
Tính tổng nếu khác
<>
=SUMIF(A2:A10,“<>”&D1, B2:B10)
Tính tông các giá trị trong các ô B2:B10 nếu giá trị tương ứng trong cột A khác với giá trị trong ô D1.
Tính tổng nếu lớn hơn hoặc bằng
>=
=SUMIF(A2:A10, “>=15”)
Tính tổng các ô có giá trị lớn hơn hoặc bằng 15 trong dải A2:A10.
Tính tổng nếu nhỏ hơn hoặc bằng
<=
=SUMIF(A2:A10,“<=15”, B2:B10)
Tính tổng các giá trị trong các ô B2:B10 nếu giá trị trong ô tương ứng trong cột A nhỏ hơn hoặc bằng 15.
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.
Tiêu chí
Công thức ví dụ
Mô tả
Tính tổng nếu bằng
Đối chiếu hoàn toàn:
=SUMIF(A2:A11,”Táo”,B2:B11)
Giá trị tính tổng trong các ô B2:B11 nếu ô tương ứng trong cột A chứa chính xác từ “Táo” và không dư một từ hay kí tự nào. Các ô chứa “Táo Tầu”, “Táo Mèo” or “Táo thuốc” không bao gồm trong trường hợp này.
Đối chiếu từng phần:
=SUMIF(A2:A11,”*Táo*”,B2:B11)
Giá trị tính tổng trong các ô
B2:B11
nếu ô tương ứng trong cột A chứa từ “Táo”, đứng độc lập hoặc đi kèm với những từ khác”. Các ô chỉ cần chứa từ Táo sẽ được tính trong trường hợp này.
Tính tổng nếu khác
Đối chiếu hoàn toàn:
=SUMIF(A2:A11,”<>Táo”,B2:B11)
Giá trị tính tổng trong các ô
B2:B11
nếu ô tương ứng trong cột A chứa bất kì giá trị nào mà khác “Táo”. Nếu ô chứa “Táo” có thêm 1 số từ hay kí tự khác, ví như “Táo Tàu” hoặc “Táo Đỏ”, thì chúng sẽ được tính tổng.
Đối chiếu từng phần:
=SUMIF(A2:A11,”<>*Táo*”,B2:B11)
Giá trị tính tổng trong các ô
B2:B11
nếu ô tương ứng trong cột A chứa bất kì giá trị nào mà khác “Táo”. Nếu các ô chỉ cần chứ từ Táo cũng sẽ không được tính tổng
Để 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:
Tiêu chí
Công thức ví dụ
Mô tả
Tính tổng các giá trị ô dựa trên 1 ngày tháng nhất định
=SUMIF(B2:B9,”10/29/2014″,C2:C9)
Tính tổng giá trị trong các ô C2:C9 nếu ngày ở ô tương ứng trong cột B là ngày 29/10/2014
Tính tổng các giá trị ô nếu ngày ở ô tương ứng lớn hơn hoặc bằng với ngày đã cho
=SUMIF(B2:B9,”>=10/29/2014″,C2:C9)
Tính tổng giá trị trong các ô C2:C9 nếu ngày ở ô tương ứng trong cột B là từ ngày 29/10/2014 trở về sau (về hiện tại)
Tính tổng các gí trị ô nếu ngày ở ô tương ứng lớn hơn ngày ở 1 ô khác.
=SUMIF(B2:B9,”>”&F1,C2:C9)
Tính tổng giá trị trong các ô C2:C9 nếu ngày ở ô tương ứng trong cột B là sau ngày trong ô F1.
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:
Tiêu chí
Công thức ví dụ
Tính tổng các giá trị trong các ô dựa trên ngày hiện thời
=SUMIF(B2:B9,TODAY(), C2:C9)
Tính tổng các giá trị ô mà nằm trong quá khứ, có nghĩa là trước ngày hiện thời.
=SUMIF(B2:B9, “<“&TODAY(), C2:C9)
Tính tổng các giá trị ô mà nằm trong tương lai, có nghĩa là sau ngày hiện thời.
=SUMIF(B2:B9,”>”&TODAY(), C2:C9)
Tính tổng các giá trị ô nếu các ngày tương ứng đều được xảy ra trong 1 tuần (có nghĩa là ngày hiện thời + 7 ngày nữa)
=SUMIF(B2:B9,”=”&TODAY()+7, C2:C9)
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.
