Bạn loay hoay mãi vẫn chưa tìm ra cách tính tổng các điều kiện trong Excel hiệu quả nhất? hàm Sumif chính là lời giải đáp dành cho bạn. Vậy hàm Sumif là gì? cách sử dụng hàm chuẩn nhất ra sao? Bạn sẽ không phải mất thời gian đi tìm câu trả lời, Trang thủ thuật sẽ bật mí đến bạn chi tiết nhất về hàm Sumif ngay dưới đây.
Đôi nét về hàm Sumif
Hàm Sumif là gì?
Hàm Sumif được xem là hàm dùng để tính tổng các giá trị thỏa mãn điều kiện nhất định trong bảng tính Excel.
Khi sử dụng hàm Sumif, bạn có thể tính tổng các ô dựa trên ngày, tháng, số và văn bản phù hợp với những tiêu chí cụ thể. Hàm còn hỗ trợ các toán tử logic (>, <, <>, =) và ký tự đại điện (*, ?) để khớp một phần.
Hàm Sumif khi ứng dụng vào Microsoft Excel được sử dụng để cộng các giá trị lại với nhau trên một phạm vi ô. Nhờ đó, người dùng có thể xác định điều kiện cụ thể phải được đáp ứng cho các giá trị trong một phạm vi ô được đưa vào quy trình bổ sung.
Ý nghĩa của hàm Sumif
Hàm Sumif đã quá quen thuộc đối với Excel: cho phép người dùng được mở rộng khả năng của hàm SUM.
Điều này có nghĩa là, thay vì bạn cho phép tính tổng trong phạm vi nhất định với Sumif, tất cả các ô bắt buộc phải thỏa mãn được những điều kiện mà người dùng đưa vào tham số Criteria.
Hàm Sumif mang đến sự tiện lợi hơn rất nhiều nếu người dùng muốn tính tổng doanh thu của đơn vị, doanh số của nhân viên hoặc doanh thu trong khoảng thời gian nhất định, tổng lương theo điều kiện nào đó,… Mọi yêu cầu này đều dễ dàng, chính xác khi bạn sử dụng hàm Sumif.
Cú pháp của hàm Sumif
=SUMIF(range, criteria, [sum_range])
Trong đó, cú pháp của hàm Sumif có các đối số như sau:
- Range (Phạm vi): Đây là phạm vi ô mà người dùng muốn đánh giá theo tiêu chí. Các ô trong mỗi phạm vi bắt buộc là số hoặc tên, mảng hay tham chiếu có chứa số. Giá trị trống và giá trị văn bản bị bỏ qua, phạm vi được chọn có thể chứa ngày ở định dạng Excel tiêu chuẩn
- Criteria (Tiêu chí): Đây là tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng vào các ô nào. Chúng có thể bao gồm các ký tự đại diện – dấu chấm hỏi (?) để khớp với bất kỳ ký tự đơn nào, dấu sao (*) để khớp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hoặc dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.
- Sum_range: Đây là vùng hay dải ô cần tính tổng. Nếu đối số sum_range bị bỏ qua, Excel sẽ cộng các ô được xác định trong đối số range (nó chính là các ô được áp dụng tiêu chí). Hơn nữa, sum_range có cùng kích cỡ và hình dạng với dải ô. Nếu không đảm bảo thì hiệu suất có thể bị gián đoạn và công thức sẽ tính tổng một dải ô bắt đầu với ô đầu tiên trong ô sum_range mà không cùng kích thước với dải ô.
Ví dụ 1: Tính tổng số tiền bán hàng cho quốc gia Mexico. Ở đây, cách tính tổng doanh thu sử dụng hàm Sumif ở ô A22, cụ thể số liệu được hiển thị trong hình bên dưới đây:
Trong đó, các thành phần của công thức Sumif được hiểu rằng:
- B2 : B19: Đây là phần phạm vi tiêu chí, nó được hiểu là tính tổng doanh số bán hàng cho quốc gia Mexico nằm trong phạm vi B2 : B19
- “Mexico”: Đây là tiêu chí, nó được hiểu là từ phạm vi tiêu chí, bạn cần cung cấp tiêu chí là “Mexico”
- F2 : F19: Đây là cột SUM, nó được hiểu là phần thực hiện tổng kết là phạm vi bán hàng từ F2 : F19
Ví dụ 2: Giả sử bạn có một danh sách các sản phẩm trong cột A và số tiền tương ứng trong cột. Bạn muốn tính tổng của tất cả số tiền liên quan đến một sản phẩm nhất định. Chẳng hạn: Chuối “Bananas”. Bạn tham khảo kết quả ngay hình bên dưới:
Lúc này, xác định các đối số cho công thức Sumif của bạn cụ thể như sau:
- Range: A2 : A8
- Criteria: “Bananas”
- Sum_range: C2 : C8
- Kết hợp những yếu tố này, bạn nhận được kết quả: =SUMIF (A2 : A8, “bananas”, C2 : C8)
Đối với ví dụ này, công thức Sumif được sử dụng với tiêu chí văn bản. Ngoài văn bản, bạn có thể bao gồm số, ngày tháng hoặc tham chiếu trong ô tiêu chí của mình. Chẳng hạn, bạn có thể viết lại công thức trên đây để nó 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)
Hướng dẫn cách sử dụng hàm Sumif trong Excel chuẩn không cần chỉnh
Sử dụng hàm Sumif lớn hơn, nhỏ hơn hoặc bằng
Để có thể sử dụng hàm Sumif cộng các giá trị lớn hơn, nhỏ hơn hoặc bằng một giá trị nhất định, bạn hãy xem xét các công thức Sumif có thể sử dụng sau đây. Một lưu ý nhỏ, trong công thức Sumif của Excel, toán tử so sánh theo sau là số hoặc văn bản phải luôn được đặt trong dấu ngoặc kép (“”)
| Tiêu chí | Nhà điều hành | Ví dụ về Công thức | Sự miêu tả |
| Tính tổng nếu lớn hơn | > | =SUMIF(A2:A10, “>5”) | Tính tổng các giá trị trên 5 trong các ô 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ột A nhỏ hơn 10. |
| Tính tổng nếu bằng | = (có thể bỏ qua) | =SUMIF(A2:A10, “=”&D1) hoặc là =SUMIF(A2:A10,D1) | Tính tổng các giá trị trong ô A2: A10 bằng giá trị trong ô D1. |
| Tính tổng nếu không bằng | <> | =SUMIF(A2:A10, “<>”&D1, B2:B10) | Tính tổng các giá trị trong các ô B2: B10 nếu một ô tương ứng trong cột A không bằng 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 giá trị lớn hơn hoặc bằng 5 trong phạm vi 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ị tương ứng trong cột A nhỏ hơn hoặc bằng 10. |
Sử dụng hàm Sumif với tiêu chí văn bản
Bên cạnh các con số, hàm Sumif còn mở rộng cho người dùng thêm các giá trị khác tùy thuộc vào việc một ô tương ứng trong một cột khác có chứa văn bản nhất định hay không. Một lưu ý nhỏ, bạn cần có các công thức Sumif khác nhau để đối sánh chính xác với từng phần.
| Tiêu chí | Ví dụ về Công thức | Sự miêu tả |
| Tính tổng nếu bằng | Kết hợp chuẩn xác: =SUMIF(A2:A8, “bananas”, C2:C8) | Tính tổng các giá trị trong các ô C2: C8 nếu một ô tương ứng trong cột A chứa chính xác từ “chuối” và không có từ hoặc ký tự nào khác. Các ô chứa “chuối xanh”, “chuối xanh” hoặc “chuối!” không bao gồm. |
| Trận đấu một phần: =SUMIF(A2:A8, “*bananas*”, C2:C8) | Tính tổng các giá trị trong các ô C2: C8 nếu một ô tương ứng trong cột A chứa từ “chuối”, một mình hoặc kết hợp với bất kỳ từ nào khác. Các ô chứa “chuối xanh” hoặc “chuối xanh” được tổng hợp. | |
| Tính tổng nếu không bằng | Kết hợp chuẩn xác: =SUMIF(A2:A8, ” <>bananas”, C2:C8) | Tính tổng các giá trị trong các ô C2: C8 nếu một ô tương ứng trong cột A chứa bất kỳ giá trị nào khác ngoài “bananas”. Nếu một ô chứa “bananas” cùng với một số từ hoặc ký tự khác như “chuối vàng” hoặc “chuối vàng”, các ô như vậy sẽ được cộng lại. |
| Trận đấu một phần: =SUMIF(A2:A8, ” <>*bananas*”, C2:C8) | Tính tổng các giá trị trong các ô C2: C8 nếu một ô tương ứng trong cột A không chứa từ “bananas”, một mình hoặc kết hợp với bất kỳ từ nào khác. Các ô chứa “yellow bananas” hoặc “bananas yellow” không được tổng hợp |
Mặc khác, công thức “tổng nếu không bằng” được hàm Sumif thể hiện chính xác đang hoạt động. Cụ thể là số lượng trong các kho của toàn bộ sản phẩm đều được bổ sung, trừ “yellow bananas”:
=SUMIF 9A2 : A7, “<> goldfinger bananas”, C2 : C7)
Tương tự như các hàm khác trong Excel, Sumif không phân biệt chữ hoa hay chữ thường. Điều này có nghĩa là “<> banana”, “<> Bananas” và “BANANAS” sẽ được tạo ra chính xác cùng một kết quả
Sử dụng hàm Sumif cùng toán tử so sánh với tham chiếu ô
Khi bạn có nhu cầu nâng cao, thực hiện những bước xa hơn, phổ biến hơn trong công thức Excel Sumif, 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 ô. Cụ thể công thức như sau: =SUMIF (A2 : A8, “<>”&F1, C2 : C8)
Khi sử dụng biểu thức logic với tham chiếu ô, bạn bắt buộc phải sử dụng dấu ngoặc kép (“”) để bắt đầu chuỗi văn bản và dấu và (&) để kết nối và kết thúc chuỗi
Ở trường hợp này, bạn sẽ không thay đổi công thức thành các giá trị tổng có điều kiện dựa vào một tiêu chí khác. Ngược lại, bạn chỉ cần nhập một giá trị mới vào một ô được tham chiếu.
Toán tử so sánh “bằng với” (=) có thể bị bỏ qua trước tham chiếu ô. Do vậy, cả hai công thức dưới đây đều tương đương nhau và đúng
- Công thức 1: =SUMIF (A2 : A8, “=”&F1, C2 : C8)
- Công thức 2: =SUMIF (A2 : A8, F1, C2: C8)
Sử dụng hàm Sumif với các ký tự đại diện
Khi bạn tính tổng các ô có điều kiện dựa trên tiêu chí “văn bản” hoặc tính tổng bằng đối sánh từng phần thì bạn phải sử dụng các ký tự đại diện trong công thức của hàm Sumif.
Những ký tự đại diện có sẵn mà bạn có thể sử dụng là:
- Dấu hoa thị (*): đại diện cho bất kỳ số ký tự nào
- Dấu chấm hỏi (?): đại diện cho một ký tự duy nhất ở một vị trí cụ thể
Ví dụ: tính tổng các giá trị dựa trên đối sánh từng phần
Khi bạn muốn tính tổng số tiền liên quan đến tất cả các loại “Banana”. Công thức của hàm Sumif sẽ được thể hiện như sau:
- Tiêu chí bao gồm văn bản kèm theo dấu (*): =SUMIF (A2 : A8, “*bananas*”, C2 : C8)
- Tiêu chí bao gồm một tham chiếu ô kèm theo dấu (*): =SUMIF (A2 : A8, “*”&F1&”*”, C2 : C8). Ở đây, bạn nên lưu ý việc sử dụng dấu và (&) trước và sau tham chiếu ô để nối một chuỗi
Để bạn sử dụng các ký tự đại diện với các ô tham thiếu, bạn nên kết hợp chúng với toán tử nối (&). Nếu bạn chỉ muốn đếm những ô bắt đầu hoặc kết thúc bằng văn bản nhất định, bạn chỉ cần thêm một dấu (*) vào trước hoặc sau văn bản:
- Tính tổng các 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ính tổng các giá trị trong C2 : C8 nếu một ô tương ứng trong cột A kết thúc bằng từ “bananas”: =SUMIF (A2 : A8, “*bananas”, C2 : C8)
Sử dụng hàm Sumif tính tổng các số lớn nhất hoặc nhỏ nhất trong phạm vi
Khi bạn muốn cộng các số lớn nhất hoặc nhỏ nhất trong phạm vi, bạn hãy sử dụng hàm Sum kết hợp cùng hàm Large hoặc Small
Ví dụ: tính tổng một biến số có giá trị lớn nhất hoặc nhỏ nhất
Bạn không muốn rườm rà khi phải thay đổi công thức mỗi khi bạn muốn tính tổng một số ô khác nhau. Lúc này, bạn có thể đặt số đó vào một số ô thay vì nhập trực tiếp chúng vào công thức. Tiếp đó, bạn hãy sử dụng hàm Row và Indirect, tham chiếu đến ô chứa biến, ô E1 như trường hợp cụ thể dưới đây:
- Tính tổng một số giá trị hàng đầu có thể thay đổi: =SUM (LARGE (B1 : B50, ROW (INDIRECT (“1 : “&E1))))
- Tính tổng một số giá trị dưới cùng có thể thay đổi: =SUM (SMALL (B1 : B50, ROW (INDIRECT (“1: “&E1))))
Sử dụng hàm Sumif tính tổng các ô tương ứng với các ô trống hoặc với các ô không trống
- Bạn sử dụng “=” làm tiêu chí. Bởi khi ô trống được hiểu là hoàn toàn không chứa công thức, không có chuỗi độ dài bằng 0 được trả về từ một số hàm Excel. Cụ thể công thức Sumif như sau: =SUMIF (A2 : A10, “=”, C2 : C10)
- Nếu “trống” bao gồm các chuỗi có độ dài bằng 0 thì bạn hãy sử dụng dấu “” làm tiêu chí: =SUMIF (A2 : A10, “ “, C2 : C10)
Ở hai trường hợp trên đây đều đánh giá các ô trong cột A. Nếu tìm thấy bất kỳ ô trống nào, các giá trị tương ứng từ cột C sẽ được thêm vào. Cụ thể trong hình bên dưới
Nếu trường hợp bạn muốn tính tổng giá trị của các ô trong cột C khi ô tương ứng trong cột A không trống, bạn cần sử dụng “<>” để làm tiêu chí trong công thức của hàm: =SUMIF (A2 : A10, “<>”, C2 : C10). Công thức này đếm các giá trị tương ứng với tất cả các ô không trố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
Khi sử dụng hàm Sumif để tính tổng có điều kiện các giá trị dựa trên ngày tháng khá tương đồng với cách sử dụng tiêu chí văn bản và số. Nếu bạn muốn tính tổng những giá trị tương ứng với ngày lớn hơn, nhỏ hơn hoặc bằng này bạn chỉ định, bạn có thể sử dụng các toán tử so sánh để thực hiện. Cụ thể một ví dụ về công thức hàm Sumif trong trường hợp này như sau:
| Tiêu chí | Ví dụ về Công thức | Sự miêu tả |
| Tính tổng các giá trị dựa trên một ngày nhất định. | =SUMIF(B2:B9,”10/29/2014″,C2:C9) | Tính tổng các giá trị trong ô C2: C9 nếu ngày tương ứng trong cột B là 29-10-2014. |
| Tính tổng các giá trị nếu một ngày tương ứng lớn hơn hoặc bằng một ngày nhất định. | =SUMIF(B2:B9,”>=10/29/2014″,C2:C9) | Tính tổng các giá trị trong ô C2: C9 nếu ngày tương ứng trong cột B lớn hơn hoặc bằng 29-10-2014. |
| Tính tổng các giá trị nếu một ngày tương ứng lớn hơn một ngày trong ô khác. | =SUMIF(B2:B9,”>”&F1,C2:C9) | Tính tổng các giá trị trong các ô C2: C9 nếu ngày tương ứng trong cột B lớn hơn ngày trong F1. |
Mặc khác, bạn muốn tính tổng các giá trị dựa trên ngày hiện tại, bạn có thể sử dụng hàm Sumif kết hợp với hàm Today () cụ thể như ví dụ bên dưới:
| Tiêu chí | Ví dụ về Công thức |
| Tính tổng các giá trị dựa trên ngày hiện tại. | =SUMIF(B2:B9, TODAY(), C2:C9) |
| Tính tổng các giá trị tương ứng với một ngày trước đó, tức là trước ngày hôm nay. | =SUMIF(B2:B9, “<” &TODAY(),C2:C9) |
| Tính tổng các giá trị tương ứng với một ngày trong tương lai, tức là sau ngày hôm nay. | =SUMIF(B2:B9, “>”&TODAY(), C2:C9) |
| Tính tổng các giá trị nếu một ngày xảy ra trong một tuần (tức là hôm nay + 7 ngày). | =SUMIF(B2:B9, “=”&TODAY()+7, C2:C9) |
Và công thức cuối cùng để tìm tổng số lượng tất cả các sản phẩm trong tuần như sau:
Hay bạn có thể sử dụng hàm Sumif tính tổng các giá trị trong một phạm vi ngày nhất định
Sử dụng hàm Sumif tính tổng các giá trị trong một số cột
Để sử dụng hàm Sumif tính tổng các giá trị trong một số cột, bạn tự đề xuất tạo một cột trợ giúp tính tổng các tổng phụ riêng lẻ cho mỗi hàng. Tiếp theo, bạn tham chiếu cột đó trong tiêu chí sum_range.
Bạn tiếp tục đặt công thức SUM đơn giản tại ô F2, điền vào cột F: =SUM (C2 : E2). Lúc này, bạn viết công thức Sumif như thông thường:
- =SUMIF (A2 : A9, “apples”, F2 : F9)
- =SUMIF (A2 : A9, H1, F2 : F9)
Trên đây là toàn bộ thông tin hàm Sumif đã được Trang thủ thuật chọn lọc để gửi đến bạn. Chắc hẳn bạn đã có cái nhìn rõ nét về hàm Sumif là gì, bản chất cũng như cách sử dụng hàm hiệu quả nhất. Hy vọng kiến thức này sẽ hữu ích và hỗ trợ bạn trong học tập và công việc nhé!