Hàm SUMIF là một hàm tính tổng có điều kiện trong Excel, được sử dụng để tính tổng của các ô dựa trên một điều kiện hoặc tiêu chí nhất định. Trong bài viết này, boxthuthuat sẽ chia sẻ với các bạn về cấu trúc hàm sumif cũng như cách sử dụng hàm SUMIF trong Excel 2007, 2010, 2013, 2016 và 2019.
Contents
Công thức và cách dùng hàm SUMIF
Cấu trúc của hàm: =SUMIF (range, criteria, [sum_range])
Trong đó:
- range : phạm vi các ô được đánh giá theo tiêu chí nào đó, ví dụ A1: A10.
- criteria : điều kiện/tiêu chí cần phải đáp ứng. Các tiêu chí này có thể là dạng số, văn bản, ngày, tham chiếu ô, biểu thức logic, hoặc một hàm trong Excel. Ví dụ: bạn có thể nhập các tiêu chí như “8”, “bananas“, “10/25/2019”, “<8”, “A1”,…
Lưu ý: Với bất kỳ điều kiện nào, dù là 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ì bạn đều phải đặt chúng trong dấu nháy kép (“”). Đối với các điều kiện số thì không bắt buộc đặt trong dấu nháy kép
- sum_range: các ô dùng để tính tổng trong trường hợp điều kiện được đáp ứng.
Trong 3 đối số trên, range, criteria là 2 đối số bắt buộc, còn sum_range có thể có hoặc không. Bạn chỉ cần sử dụng sum_range nếu bạn muốn tính tổng các ô khác range (mà đã được định nghĩa trong đối số range). Nếu đối số sum_range bị bỏ qua, khi đó Excel sẽ tính tổng ở các ô trong range đáp ứng điều kiện
Ví dụ, bạn có một danh sách các sản phẩm trong cột A và số lượng tương ứng trong kho trong cột C, và bạn muốn tính tổng lượng hàng liên quan đến 1 sản phẩm nhất định, chẳng hạn ở đây là: bananas
Trong trường hợp này, chúng ta sẽ sử dụng công thức:
= SUMIF (A2: A8, “bananas”, C2: C8)
Trong đó:
- range: A2: A8
- criteria: “bananas”
- sum_range: C2: C8
Kết quả thu được sẽ như hình bên dưới:
Ở ví dụ trên, chúng ta sử dụng hàm SUMIF với các tiêu chí (criteria) dạng văn bản. Ngoài ra, bạn có thể thay dạng văn bản thành dạng số, ngày tháng hoặc một ô tham chiếu với tiêu chí bạn đưa ra. Chẳng hạn, bạn có thể sử dụng công thức trên được tham chiếu đến ô có chứa tên của sản phẩm được tính tổng:
= SUMIF (A2: A8, F1, C2: C8)
Kết quả thu được vẫn sẽ giống như trường hợp trên.
Chú thích: Tham số sum_range thực sự chỉ cần xác định bởi ô trên cùng bên trái của dải được tính tổng. Cá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à đối số sum_range không nhất thiết phải có cùng kích thước với đối số range, nghĩa là nó có thể khác nhau về số lượng hàng và cột. Tuy nhiên, ô đầu tiên (hay ô trên cùng bên trái) của dải cần tính tổng phải luôn luôn ở đúng cột. Ví dụ, với công thức SUMIF ở trên, bạn có thể lấy ô C2, hoặc C2: C4, hay thậm chí là C2: C100 làm đối số sum_range, và kết quả vẫn sẽ không thay đổi. Tuy vậy, bạn vẫn nên cung cấp range và sum_range có cùng kích thước.
Hướng dẫn sử dụng hàm Sumif trong một số trường hợp cụ thể
Dưới đây là một vài cách sử dụng SUMIF trong Excel với các tiêu chí và loại dữ liệu khác nhau.
Hàm SUMIF với dấu lớn hơn, nhỏ hơn hoặc bằng
Hàm Sumif có thể sử dụng với các giá trị lớn hơn, nhỏ hơn hoặc bằng một giá trị nhất định.
Lưu ý: Trong công thức của hàm SUMIF, một dấu so sánh theo sau bởi 1 chữ số hoặc một văn bản thì luôn luôn phải được đặt trong dấu nháy kép (“”).
Hàm SUMIF với dấu lớn hơn, nhỏ hơn hoặc bằng
| 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, “>5”) | Tính tổng các ô giá trị lớn hơn 5 trong dải ô A2:A10. |
| Tính tổng nếu nhỏ hơn | < | =SUMIF(A2:A10, “<10”, B2:B10) | Tính tổng các giá trị trong các ô B2:B10 nếu giá trị tương ứng trong các ô A2:A10 nhỏ hơn 10. |
| Tính tổng nếu bằng | = | =SUMIF(A2:A10, “=”&D1) | Tính tổng các ô từ A2:A10 có giá trị bằng với giá trị trong ô D1. |
| hoặc | |||
| =SUMIF(A2:A10,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ác ô A2:A10 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, “>=5”) | Tính tổng các ô có giá trị lớn hơn hoặc bằng 5 trong dải 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 giá trị trong các ô B2:B10 nếu giá trị trong ô tương ứng trong các ô A2:A10 nhỏ hơn hoặc bằng 10. |
Hàm SUMIF với tiêu chí văn bản
Ngoài tiêu chí về số liệu, Sumif cũng có thể tính tổng dựa trên tiêu chí về văn bản. Các tiêu chí về số liệu có thể đối chiếu theo từng phần hoặc chính xác hoàn toàn, tùy theo cách sử dụng được mô tả ở bảng dưới đây:
| 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:A8, “bananas”, C2:C8) |
Tính tổng các giá trị trong các ô C2:C8 nếu ô tương ứng trong cột A chứa chính xác từ “bananas” và không thêm một từ hay kí tự nào. Những ô chứa các từ như “green bananas”, “bananas green” hoặc“bananas!” không bao gồm trong trường hợp này. |
| Đối chiếu từng phần: =SUMIF(A2:A8, “*bananas*”, C2:C8) |
Tính tổng các giá trị trong các ô C2:C8 nếu ô tương ứng trong cột A chứa từ “bananas”, đứng độc lập hoặc đi kèm với những từ khác”. Các ô chứa từ “green bananas”, “bananas green” hoặc “bananas!” đều 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:A8, “<>bananas”, C2:C8) |
Tính tổng các giá trị trong các ô C2:C8 nếu ô tương ứng trong cột A chứa bất kì giá trị nào mà khác “bananas”. Nếu ô chứa “bananas” có thêm 1 số từ hay kí tự khác, ví dụ như “yellow bananas” hoặc “bananas yellow”, thì chúng sẽ được tính tổng. |
| Đối chiếu từng phần: =SUMIF(A2:A8, “<>*bananas*”, C2:C8) |
Tính tổngcác giá trị trong các ô C2:C8 nếu ô tương ứng trong cột A chứa bất kì giá trị nào mà khác “bananas”. Nếu ô chứa “bananas” có thêm 1 số từ khác chẳng hạn như “yellow bananas” or “bananas yellow” sẽ không được tính tổng. |
Có thể thấy rằng, với đối chiếu toàn phần, văn bản sẽ được đặt trong dấu nháy kép (“”). Còn đối với đối chiếu từng phần, ngoài dấu nháy kép(“”), văn bản sẽ được đặt trong 1 cặp dấu sao (**) (** sẽ nằm trong “”)
Ví dụ, chúng ta cần tính tổng lượng tồn kho của tất cả các sản phẩm khác sản phẩm “goldfinger bananas” trong bảng phía dưới. Công thức hàm sử dụng sẽ là: = SUMIF (A2: A7, “<> goldfinger bananas”, C2: C7)
Lưu ý: Cũng như hầu hết các hàm trong Excel, hàm Sumif không phân biệt chữ viết thường hay chữ viết hoa. Điều đó có nghĩa là gì bạn nhập “<bananas”, “<Bananas” hay“<BANANAS” thì kết quả trả về đều giống nhau.
Sử dụng dấu so sánh với ô tham chiếu
Thay vì sử dụng các tiêu chí là các số hay văn bản, bạn có thể sử dụng bằng các ô tham chiếu. chẳng hạn như:
= SUMIF (A2: A8, “<>” & F1, C2: C8)
Trong trường hợp này, bạn sẽ 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.
Lưu ý: 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ụ “<>” & F1.
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: A8, “=” & F1, C2: C8)
Công thức 2: = SUMIF (A2: A8, F1, C2: C8)
Hàm Sumif với ký tự đại diện
Nếu bạn cần tính tổng có điều kiện các ô dựa trên tiêu chí là văn bản và bạn muốn tính tổng bằng cách đối chiếu từng phần, thì bạn cần phải sử dụng các kí tự đại diện trong công thức SUMIF của bạn.
Hãy xem xét các kí tự đại diện sau đây:
- 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ụ 1: Các giá trị 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 bananas. Thì các công thức SUMIF sau sẽ có dạng như sau:
= SUMIF (A2: A8, “bananas *”, C2: C8) : tiêu chí bao gồm các văn bản kết thúc với dấu hoa thị (*).
= SUMIF (A2: A8, “*” & F1 & “*”, C2: C8) – các tiêu chí bao gồm một tham chiếu ô kèm theo dấu hoa thị, vui lòng lưu ý về việc sử dụng dấu và (&) trước và sau một tham chiếu ô để nối với chuỗi.
Nếu bạn chỉ muốn tính tổng những ô bắt đầu hoặc kết thúc với một văn bản nhất định, thì bạn chỉ cần thêm một dấu * trước hoặc sau văn bản:
= SUMIF (A2: A8, “bananas*”, C2: C8) – tổng giá trị trong C2: C8 nếu một ô tương ứng trong cột A bắt đầu bằng từ “bananas”.
= SUMIF (A2: A8, “*bananas”, C2: C8) – tổng giá trị trong C2: C8 nếu một ô tương ứng trong cột A kết thúc bằng từ “bananas”.
Ví dụ 2: Tính tổ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 6 chữ cái, thì bạn sẽ sử dụng công thức sau:
= SUMIF (A2: A8, “??????”, C2: C8)
Ví dụ 3. Tính tổng các ô có giá trị tương đương với giá trị văn bản
Nếu bảng tính của bạn chứa các 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: A8, “? *”, C2: C8) : cộng giá trị trong các ô C2: C8 nếu ô tương ứng trong cột A chứa ít nhất 1 kí tự.
= SUMIF (A2: A8, “*”, C2: C8) : bao gồm các ô có vẻ là 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: Sử dụng * hoặc ? như những ký tự bình 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 ô C2: C8 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: A8, “~?”, C2: C8)
Tính tổng các ô tương ứng với các ô trống
Một ô trống là một ô hoàn toàn không 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: A10, “=”, C2: C10)
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: A10, “”, C2: C10)
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 C sẽ được thêm vào.
Cách tính tổng các ô tương ứng với ô không trống
Nếu bạn muốn tính tổng các giá trị của ô trong cột C khi một ô tương ứng trong cột A không rỗng, hãy sử dụng “<>” làm tiêu chí trong công thức SUMIF của bạn:
= SUMIF (A2: A10, “<>”, C2: C10)
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.
Hướng dẫn sử dụng hàm SUMIF với tiêu chí 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) |
Hình dưới đây minh hoạ cách bạn có thể sử dụng công thức cuối cùng để tìm tổng số tiền của tất cả các sản phẩm được chuyển đi trong một tuần.
Tính tổng các giá trị ứng với một quãng thời gian nhất định
Câu hỏi sau đây được hỏi thường xuyên trên các diễn đàn và blog của Excel – “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?”
Câu trả lời là sử dụng một sự kết hợp, hoặc chính xác hơn, là sự khác biệt giữa 2 công thức SUMIF. Trong Excel 2007 trở về bây giờ, bạn cũng có thể sử dụng hàm SUMIFS cho phép nhiều tiêu chí, thậm chí là một lựa chọn tốt hơn. Chúng tôi sẽ đề cập tới vế sau trong chủ đề của bài viết tiếp theo, còn dưới đây sẽ là ví dụ về công thức SUMIF:
= 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, bao gồm.
Công thức này có thể có vẻ hơi khó khăn nếu bạn chỉ lướt qua, nhưng khi nếu đi sâu vào nó, thì bạn sẽ thấy nó rất đơn giản. Hàm SUMIF đầu tiên đã thêm tất cả 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.
Tính tổng các giá trị trong một vài cột
Để hiểu vấn đề hơn, hãy xem xét ví dụ sau đây: Giả sử bạn có một bảng tóm tắt doanh thu hàng tháng và nó được thu thập từ một số báo cáo cho từng khu vực, trong đó có một vài hồ sơ cho cùng một sản phẩm ở trên:
Vì vậy, làm thế nào để bạn tìm thấy tổng số apples (táo) được bán ở tất cả các tiểu bang trong ba tháng qua?
Như bạn đã biết, kích thước của sum_range được xác định bởi kích thước của tham số range. Đó là lý do tại sao bạn không thể sử dụng công thức như = SUMIF (A2: A9, “apples”, C2: E9) bởi vì nó sẽ thêm các giá trị tương ứng với “Apples” mà chỉ ở trong cột C. Và đây không phải là những gì chúng ta đang tìm kiếm.
Giải pháp hợp lý và đơn giản nhất cho thấy chính nó là tạo ra một cột để trợ giúp tính từng tổng số cho mỗi hàng và sau đó cột này sẽ được tham chiếu trong tiêu chí sum_range.
Đi trước và đặt công thức SUM đơn giản trong ô F2, sau đó điền vào cột F: = SUM (C2: E2)
Sau đó, bạn có thể viết một công thức SUMIF đơn giản như sau:
= SUMIF (A2: A9, “apples”, F2: F9)
hoặc là
= SUMIF (A2: A9, H1, F2: F9)
Trong các công thức trên, sum_range có cùng kích thước với dải, tức là 1 cột và 8 hàng, và do đó kết quả mà nó trả lại sẽ chính xác:
Nếu bạn muốn làm mà không có một cột trợ giúp, sau đó bạn có thể viết một công thức SUMIF riêng cho mỗi cột bạn muốn tính tổng, và sau đó thêm các kết quả trả về với hàm SUM:
= SUM (SUMIF (A2: A9, I1, C2: C9), SUMIF (A2: A9, I1, D2: D9), SUMIF (A2: A9, I1, E2: E9))
Một cách khác là sử dụng một công thức mảng phức tạp hơn (nhưng cũng đừng quên nhấn Ctrl + Shift + Enter):
{= SUM ((C2: C9 + D2: D9 + E2: E9) * (- (A2: A9 = I1)))}}
Cả hai công thức trên sẽ đưa ra kết quả là 2070 trong trường hợp trên.
Trên đây là hướng dẫn về cách sử dụng hàm Sumif trong Excel. Nếu có bất kỳ thắc mắc gì, hãy để lại bình luận ở phía bên dưới nhé!
