Excel là một công cụ quen thuộc với hầu hết mọi người làm việc trong môi trường văn phòng. Một trong số những hàm Excel hữu ích nhất mà bạn nên biết đó là hàm SUMIF. Hàm này cho phép bạn tính tổng của một loạt các số liệu dựa trên một hoặc nhiều điều kiện. Trong bài viết này, chúng tôi sẽ hướng dẫn bạn cách dùng hàm SUMIF trong Excel thông qua một số ví dụ cụ thể.Cách dùng hàm SUMIF trong EXCEL
I. Ý nghĩa của hàm SUMIF
Hàm SUMIF trong Excel là một trong những hàm rất tiện lợi và linh hoạt, cho phép bạn tổng hợp dữ liệu dựa trên một hoặc nhiều điều kiện cụ thể. Dưới đây là một số ý nghĩa chính của hàm SUMIF:
Tính tổng theo điều kiện: Đây là chức năng chính của hàm SUMIF, giúp bạn tính tổng của một loạt các giá trị trong một phạm vi cụ thể, dựa trên một hoặc nhiều điều kiện. Điều này rất hữu ích khi bạn cần tổng hợp dữ liệu từ một bảng lớn với nhiều hàng và cột.
Tính toán nhanh chóng: Thay vì phải lọc dữ liệu và tính tổng thủ công, bạn chỉ cần nhập một công thức đơn giản để Excel thực hiện tất cả các bước này cho bạn. Điều này tiết kiệm thời gian và giảm thiểu nguy cơ lỗi.
Tính linh hoạt: Hàm SUMIF cho phép bạn sử dụng nhiều loại điều kiện khác nhau, bao gồm các giá trị cụ thể, các phạm vi giá trị (ví dụ, tất cả các giá trị lớn hơn hoặc nhỏ hơn một số cụ thể), và thậm chí các điều kiện dựa trên văn bản (ví dụ, tất cả các giá trị bắt đầu hoặc kết thúc bằng một chuỗi văn bản cụ thể).
II. Công thức của hàm SUMIF và SUMIFS
Sau đây là những công thức của hàm SUMIF và SUMIFS mà bạn có thể tham khảo qua:
1. Công thức hàm SUMIF
=SUMIF(range; criteria; sum_range)
Range: Là phạm vi mà bạn muốn áp dụng điều kiện. Đây có thể là một cột, một hàng hoặc một dãy ô bất kỳ trong bảng tính của bạn.
Criteria: Là điều kiện mà các giá trị trong phạm vi phải thoả mãn để được tính tổng. Điều kiện này có thể là một số, một biểu thức, một văn bản hoặc một công thức.
Sum_range (không bắt buộc): Là phạm vi mà các giá trị sẽ được tính tổng. Nếu không xác định, Excel sẽ tự động tính tổng các giá trị trong phạm vi được xác định trong tham số “range”.
Ví dụ về hàm SUMIF
Công thức hàm Sumif
2. Công thức hàm SUMIFS
=SUMIFS(sum_range; criteria_range; criteria;…)
sum_range: là phạm vi mà các giá trị sẽ được tính tổng. Đây là tham số bắt buộc.
criteria_range1, criteria_range2, …: là những phạm vi mà bạn muốn áp dụng các điều kiện. Đây cũng là tham số bắt buộc.
criteria1, criteria2, …: là những điều kiện mà các giá trị trong các phạm vi tương ứng phải thoả mãn để được tính tổng. Đây cũng là tham số bắt buộc.
III. Những ví dụ cách sử dụng hàm SUMIF trong Excel
Để giúp cho bạn nắm rõ hơn về cách sử dụng hàm SUMIF trong Excel, dưới đây là một số ví dụ cụ thể cho từng trường hợp áp dụng hàm SUMIF:
1. Tính tổng điều kiện với dấu lớn hơn, nhỏ hơn hoặc bằng của SUMIF
Hàm SUMIF trong Excel không chỉ cho phép bạn tính tổng dựa trên một giá trị cụ thể mà còn cho phép bạn tính tổng dựa trên các điều kiện như lớn hơn, nhỏ hơn hoặc bằng một giá trị nào đó. Dưới đây là cách sử dụng hàm SUMIF với các điều kiện này:
Lớn hơn một giá trị cụ thể:
Giả sử bạn muốn tính tổng các giá trị trong cột J nếu chúng lớn hơn 100. Công thức sẽ như sau:
=SUMIF(J2:J11,">100")
Ví dụ cách sử dụng hàm SUMIF trong Excel – Tính tổng điều kiện với dấu lớn hơn
Nhỏ hơn một giá trị cụ thể:
Nếu bạn muốn tính tổng các giá trị trong cột J nếu chúng nhỏ hơn 200, bạn sẽ sử dụng công thức như sau:
=SUMIF(J2:J11,"<200")
Ví dụ cách sử dụng hàm SUMIF trong Excel – Tính tổng điều kiện với dấu nhỏ hơn
Bằng một giá trị cụ thể:
Để tính tổng các giá trị trong cột J nếu chúng bằng 100, bạn sẽ sử dụng công thức như sau:
=SUMIF(J2:J11,"=100")
Ví dụ cách sử dụng hàm SUMIF trong Excel -Bằng một giá trị cụ thể
Lưu ý: bạn cần phải đặt dấu lớn hơn, nhỏ hơn hoặc bằng trong dấu ngoặc kép để Excel nhận biết chúng như là một phần của điều kiện.
Ngoài ra, bạn cũng có thể kết hợp các điều kiện này. Ví dụ, khi bạn muốn tính tổng các giá trị trong cột J nếu chúng lớn hơn hoặc bằng 200, bạn có thể sử dụng công thức như sau:
=SUMIF(J2:J11,">=200")
Ví dụ cách sử dụng hàm SUMIF trong Excel – Lớn hơn hoặc Bằng một giá trị cụ thể
2. Hàm SUMIF tính tổng theo điều kiện so sánh với chuỗi
Hàm SUMIF trong Excel cũng có thể được sử dụng để so sánh chuỗi ký tự. Bạn có thể sử dụng các toán tử như “=”, “<>”, “>” và “<” cùng với chuỗi ký tự.
Ví dụ, giả sử bạn có một bảng tính Excel với cột I chứa tên nhà cung cấp và cột J chứa số lượng. Bạn muốn tính tổng số lượng trái cây mà nhà cung cấp có tên “Hoàng”, bạn có thể sử dụng hàm SUMIF như sau:
=SUMIF(I2:I11, "Hoàng", J2:J11)
Hàm SUMIF tính tổng theo điều kiện so sánh với chuỗi
Trong ví dụ trên, hàm SUMIF sẽ xem xét cột A từ I2 đến I11, tìm tất cả các hàng mà giá trị của nó bằng với “Hoàng”. Sau đó, nó sẽ cộng lại tất cả các giá trị tương ứng trong cột B từ J2 đến J11.
Nếu bạn muốn tính tổng số lượng cung cấp không phải là “Hoàng”, bạn có thể sử dụng toán tử khác nhau “<>” như sau:
=SUMIF(I2:I11,"<>Hoàng", J2:B11)
Hàm SUMIF tính tổng theo điều kiện so sánh với chuỗi
Trong ví dụ này, hàm SUMIF sẽ cộng tất cả các giá trị trong cột B từ J2 đến J11, nếu tương ứng giá trị trong cột A từ I2 đến I11 không phải là “Hoàng”.
3. Sử dụng dấu so sánh với tham chiếu ô
Để sử dụng dấu so sánh với tham chiếu ô trong hàm SUMIF, bạn sẽ phải kết hợp dấu so sánh và tham chiếu ô trong một chuỗi bằng cách sử dụng dấu nối (&). Dưới đây là cách làm:
Giả sử bạn muốn tính tổng các giá trị trong cột J mà lớn hơn giá trị trong ô J2. Công thức sẽ như sau:
=SUMIF(J2:J11,">"&J2)
Sử dụng dấu so sánh với tham chiếu ô
Trong ví dụ này, “&” được sử dụng để nối dấu so sánh lớn hơn (“>”) với giá trị trong ô J2.
Tương tự, bạn cũng có thể sử dụng dấu so sánh nhỏ hơn, bằng hoặc không bằng với tham chiếu ô như sau:
=SUMIF(J2:J11,"<"&J2) – Tính tổng các giá trị trong cột B mà nhỏ hơn giá trị trong ô J2.
=SUMIF(J2:J11,"="&J2) – Tính tổng các giá trị trong cột B mà bằng giá trị trong ô J2.
=SUMIF(J2:J11,"<>"&J2) – Tính tổng các giá trị trong cột B mà không bằng giá trị trong ô J2.
Trong Excel, bạn có thể sử dụng các ký tự đại diện, hoặc wildcard, trong hàm SUMIF để thay thế cho một hoặc nhiều ký tự khi so khớp chuỗi. Có hai ký tự đại diện chính mà bạn có thể sử dụng:
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 có một danh sách các sản phẩm trong cột A và số lượng đã bán trong cột B. Bạn muốn tính tổng số lượng bán của tất cả các sản phẩm có chữ “Product” trong tên. Trong trường hợp này, bạn có thể sử dụng hàm SUMIF như sau:
=SUMIF(A2:A11,"*Product*",B2:B11)
Công thức SUMIF với kí tự đại diện – Ví dụ 1: Các giá trị tính tổng dựa trên đối chiếu từng phần
Công thức này sẽ tính tổng tất cả các giá trị trong cột B nếu tên sản phẩm tương ứng trong cột A có chứa “Product”.
Ví dụ 2: Tổng giá trị với số kí tự nhất định
Giả sử bạn muốn tính tổng số lượng bán của tất cả các sản phẩm có tên chứa đúng 8 ký tự. Bạn có thể sử dụng hàm SUMIF như sau:
=SUMIF(A2:A11,"????????",B2:B11)
Công thức SUMIF với kí tự đại diện – Ví dụ 2: Tổng giá trị với số kí tự nhất định
Trong công thức này, mỗi ký tự “?” đại diện cho một ký tự bất kỳ. Do đó, “????????” sẽ phù hợp với bất kỳ giá trị nào có đúng 8 ký tự.
Ví dụ 3: Tính tổng các ô có giá trị tương ứng với giá trị văn bản
Giả sử bạn muốn tính tổng số lượng bán của tất cả các sản phẩm có tên kết thúc bằng “A“. Bạn có thể sử dụng hàm SUMIF như sau:
=SUMIF(A2:A10,"*A",B2:B10)
Công thức SUMIF với kí tự đại diện – Ví dụ 3: Tính tổng các ô có giá trị tương ứng với giá trị văn bản
Công thức này sẽ tính tổng tất cả các giá trị trong cột B nếu tên sản phẩm tương ứng trong cột A kết thúc bằng “A”.
Ví dụ 4. Sử dụng “*” hoặc “?” như những kí tự bình thường
Dấu hoa thị (*) – thay thế cho bất kỳ chuỗi ký tự nào.
Dấu chấm hỏi (?) – thay thế cho một ký tự duy nhất.
Ví dụ, bạn muốn tính tổng số lượng bán (trong cột B) của tất cả các sản phẩm có tên bắt đầu bằng “Product” trong cột A. Bạn có thể sử dụng công thức SUMIF như sau:
=SUMIF(A2:A10,"Product*",B2:B10)
Ví dụ 4. Sử dụng “*” hoặc “?” như những kí tự bình thường
Trong công thức trên, “Product*” sẽ phù hợp với bất kỳ giá trị nào trong cột A bắt đầu bằng “Product”, bất kể ký tự phía sau.
Tương tự, nếu bạn muốn tính tổng số lượng bán của tất cả các sản phẩm có tên gồm 8 ký tự, trong đó 7 ký tự đầu tiên là “Product” và ký tự cuối cùng là bất kỳ, bạn có thể sử dụng công thức sau:
=SUMIF(A2:A10,"Product?",B2:B10)
Công thức SUMIF với kí tự đại diện – Ví dụ 4. Sử dụng “*” hoặc “?” như những kí tự bình thường
Trong ví dụ này, “Product?” sẽ phù hợp với bất kỳ giá trị nào trong cột A có 8 ký tự, trong đó 7 ký tự đầu tiên là “Product”.
5. Tổng các số lớn nhất hoặc nhỏ nhất trong một dải
Để tính tổng các số lớn nhất hoặc nhỏ nhất trong một dải, bạn cần sử dụng kết hợp của các hàm LARGE, SMALL, và SUM.
Ví dụ 1: Tính tổng một số lượng cụ thể của số lớn nhất / nhỏ nhất
Nếu bạn muốn tính tổng 3 số lớn nhất trong một dãy số, bạn có thể sử dụng công thức sau:
=SUM(LARGE(B1:B11,{1,2,3}))
Tổng các số lớn nhất hoặc nhỏ nhất trong một dải
Công thức =SUM(LARGE(B1:B11,{1,2,3})) trong Excel được sử dụng để tính tổng của 3 giá trị lớn nhất trong phạm vi từ B1 đến B11.
Hàm LARGE(array, k) trả về giá trị lớn thứ k trong một tập dữ liệu. array là tập hợp các giá trị bạn muốn tìm giá trị lớn, và k là vị trí của giá trị bạn muốn tìm (1 cho giá trị lớn nhất, 2 cho giá trị lớn thứ hai, v.v.).
Trong trường hợp này, k là một mảng bao gồm các số {1,2,3}. Điều này có nghĩa là hàm LARGE sẽ trả về 3 giá trị lớn nhất trong phạm vi từ B1 đến B11.
Sau đó, hàm SUM sẽ tính tổng của những giá trị này.
Tương tự với công thức trên nếu bạn muốn tính tổng 3 số nhỏ nhất trong một dãy số, bạn có thể sử dụng công thức sau:
=SUM(SMALL(B1:B11,{1,2,3}))
Tính tổng một số lượng cụ thể nhỏ nhất
Xem thêm các tin đăng tuyển dụng việc làm bán thời gian dành cho sinh viên tại Muaban.net:
Hàm INDIRECT và hàm ROW khi được kết hợp lại có thể tạo ra một dãy số, mà bạn có thể sử dụng làm tham số thứ hai cho hàm LARGE hoặc SMALL. Điều này cho phép bạn tính tổng một số lượng lớn các giá trị lớn nhất hoặc nhỏ nhất mà không cần phải nhập thủ công từng số vào công thức.
Ví dụ, hãy xem xét công thức sau:
=SUM(LARGE(B2:B11,ROW(INDIRECT("1:5"))))
Tổng các số lớn nhất trong một dải
Công thức =SUM(LARGE(B1:B11,ROW(INDIRECT("1:5")))) trong Excel sẽ tính tổng của 5 giá trị lớn nhất trong phạm vi từ B1 đến B11.
Hàm LARGE(array, k) trả về giá trị lớn thứ k trong một tập hợp dữ liệu. array là tập hợp các giá trị bạn muốn tìm giá trị lớn, và k là vị trí của giá trị bạn muốn tìm.
Đoạn ROW(INDIRECT("1:5")) tạo ra một mảng {1,2,3,4,5}. Hàm INDIRECT("1:5") trả về một tham chiếu tới các dòng từ 1 đến 5, và hàm ROW trả về số của dòng đó. Vì vậy, mảng này được sử dụng như tham số k cho hàm LARGE, tức là nó sẽ trả về 5 giá trị lớn nhất.
Cuối cùng, hàm SUM sẽ tính tổng của 5 giá trị lớn nhất này.
Tương tự, công thức sau:
=SUM(SMALL(B2:B11,ROW(INDIRECT("1:5"))))
Tổng các số nhỏ nhất trong một dải
Hàm INDIRECT sẽ tìm 5 giá trị nhỏ nhất trong dãy từ B2 đến B11, và sau đó tính tổng các giá trị này.
Bạn cần nhớ rằng các công thức này phải được nhập như các công thức mảng. Điều này có nghĩa là sau khi bạn nhập công thức, hãy nhấn tổ hợp phím Ctrl + Shift + Enter thay vì chỉ nhấn Enter.
6. Làm thế nào để tính tổng các ô tương ứng với các ô trống
Nếu bạn muốn tính tổng các giá trị trong cột B tương ứng với các ô trống trong cột A, bạn có thể sử dụng công thức sau:
=SUMIF(A2:A11,"",B2:B11)
Làm thế nào để tính tổng các ô tương ứng với các ô trống
Công thức=SUMIF(A2:A11,"",B2:B11)trong Excel được sử dụng để tính tổng các giá trị trong phạm vi từ B2 đến B11, nhưng chỉ khi tương ứng với các ô trong phạm vi từ A2 đến A11 là rỗng.
7. Làm thế nà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ị trong cột B tương ứng với các ô không trống trong cột A, bạn có thể sử dụng công thức sau:
=SUMIF(A2:A11,"<>", B2:B11)
Làm thế nào để tính tổng các ô tương ứng với các ô không trống
Công thức này sẽ kiểm tra mỗi ô trong dãy từ A2 đến A11. Nếu một ô không trống (nghĩa là, nó chứa một giá trị hoặc một ký tự), nó sẽ thêm giá trị tương ứng từ dãy từ B2 đến B11 vào tổng.
Lưu ý rằng “<>” là toán tử trong Excel có nghĩa là “không bằng”, vì vậy, "<>" nghĩa là “không bằng rỗng”, tức là “không trống”.
8. Cách sử dụng Hàm SUMIF với ngày tháng
Hàm SUMIF cũng có thể được sử dụng với ngày tháng để tính tổng các ô dựa trên một ngày cụ thể hoặc khoảng thời gian cụ thể. Dưới đây là một số ví dụ về cách sử dụng SUMIF với ngày tháng:
Ví dụ: Tính tổng các ô tương ứng với một ngày cụ thể
Giả sử bạn có một bảng dữ liệu trong đó cột C chứa các ngày và cột B chứa các giá trị. Để tính tổng các giá trị trong cột B tương ứng với một ngày cụ thể (ví dụ, ngày 1/1/2023), bạn có thể sử dụng công thức sau:
9. Cách tính tổng các giá trị ứng với một quãng thời gian nhất định
Nếu bạn muốn tính tổng các giá trị trong cột B tương ứng với một khoảng thời gian cụ thể (ví dụ, bạn muốn tính tổng sản phẩm nhập từ ngày 01/01 đến 02/01 trong năm 2023), bạn sẽ cần sử dụng hai công thức SUMIF:
Cách tính tổng các giá trị ứng với trong một quãng thời gian nhất định
Công thức trên đầu tiên sẽ tính tổng tất cả các giá trị tương ứng với các ngày từ ngày 1/1/2023 trở đi, trong khi công thức thứ hai sẽ trừ đi tất cả các giá trị tương ứng với các ngày từ ngày 3/1/2023 trở đi.
Lưu ý rằng bạn cần đảm bảo ngày tháng trong dữ liệu và công thức của bạn đều ở cùng một định dạng.
10. Làm thế nào để tính tổng các giá trị trong một vài cột?
Để tính tổng các giá trị trong nhiều cột, bạn có thể sử dụng hàm SUM hoặc SUMIF/SUMIFS, tùy thuộc vào yêu cầu cụ thể của bạn.
Sử dụng hàm SUM: Nếu bạn chỉ muốn tính tổng các giá trị trong một vài cột mà không quan tâm đến bất kỳ điều kiện nào, bạn có thể sử dụng hàm SUM. Ví dụ:
=SUM(B2:B11, C2:C11, D2:D11)
Làm thế nào để tính tổng các giá trị trong một vài cột?
Công thức này sẽ tính tổng các giá trị trong cột B, C và D từ dòng 2 đến dòng 11.
Sử dụng hàm SUMIF hoặc SUMIFS: Nếu bạn muốn tính tổng các giá trị trong một vài cột dựa trên một hoặc nhiều điều kiện, bạn có thể sử dụng hàm SUMIF hoặc SUMIFS. Ví dụ:
Làm thế nào để tính tổng các giá trị trong một vài cột?
Công thức này sẽ tính tổng các giá trị trong cột B, C, và D từ dòng 2 đến dòng 11 tương ứng với các giá trị trong cột A thỏa mãn điều kiện “A” trong phạm vi ngày từ ngày 01/01/2023 đến ngày 03/01/2023.
Định dạng dữ liệu: Hàm SUMIF hoạt động dựa trên dữ liệu đầu vào. Nếu dữ liệu của bạn chưa được định dạng đúng, kết quả có thể không chính xác. Đặc biệt, khi làm việc với ngày tháng, đảm bảo rằng cả dữ liệu và công thức của bạn đều ở cùng một định dạng.
So sánh văn bản và số: Hàm SUMIF có thể so sánh văn bản với văn bản và số với số, nhưng không thể so sánh văn bản với số. Đảm bảo rằng dữ liệu bạn đang cố gắng so sánh có cùng loại.
Ký tự đại diện: Khi sử dụng ký tự đại diện trong điều kiện của hàm SUMIF, hãy nhớ rằng Excel sẽ coi chúng như một phần của chuỗi ký tự, trừ khi bạn thêm một dấu (~) trước ký tự đại diện.
Có nhiều lý do khiến hàm SUMIF không hoạt động. Dữ liệu đầu vào có thể đã không được định dạng đúng hoặc các tham số trong công thức có thể đã không được chỉ định đúng. Ngoài ra, nếu bạn đang cố gắng so sánh văn bản với số, hàm SUMIF sẽ không hoạt động.
2. Làm thế nào để công thức SUMIF của bạn hoạt động nhanh hơn?
Hàm SUMIF có thể chậm lại khi xử lý một số lượng lớn dữ liệu. Một cách để tăng tốc quá trình này là giới hạn dữ liệu mà nó cần xử lý. Bạn có thể làm điều này bằng cách sử dụng một dải dữ liệu nhỏ hơn hoặc sắp xếp dữ liệu của bạn theo thứ tự nào đó trước khi áp dụng hàm SUMIF.
Qua bài viết, chúng ta đã cùng nhau tìm hiểu về cách dùng hàm SUMIF trong Excel, từ cách sử dụng cơ bản đến áp dụng các điều kiện phức tạp hơn. Đây là công cụ vô cùng hữu ích trong việc phân tích và tổng hợp dữ liệu, giúp công việc của bạn trở nên nhanh chóng và chính xác hơn.
Để tìm hiểu thêm về cách áp dụng kỹ năng Excel này vào công việc, hãy truy cập Muaban.net và tìm kiếm các cơ hội tìm việc làm kế toán – nơi mà việc sử dụng thành thạo hàm SUMIF sẽ mang lại nhiều lợi ích. Chúc bạn thành công trong công việc và học tập của mình!
Miễn trừ trách nhiệm:Thông tin cung cấp chỉ mang tính chất tổng hợp.
Muaban.net nỗ lực để nội dung truyền tải trong bài cung cấp thông tin đáng tin cậy tại thời điểm đăng tải.
Tuy nhiên, không nên dựa vào nội dung trong bài để ra quyết định liên quan đến tài chính, đầu tư, sức khỏe. Thông tin trên không thể thay thế lời khuyên của chuyên gia trong lĩnh vực. Do đó, Muaban.net không chịu bất kỳ trách nhiệm nào nếu bạn sử dụng những thông tin trên để đưa ra quyết định.
Xem thêm
Hoàng Ngọc là chuyên gia nội dung với hơn 3 năm kinh nghiệm trong lĩnh vực tuyển dụng và việc làm. Anh tập trung vào việc cung cấp thông tin hữu ích cho người lao động, đặc biệt là nhóm phổ thông, giúp họ tiếp cận cơ hội việc làm nhanh chóng và hiệu quả.
Hoàng Ngọc là chuyên gia nội dung với hơn 3 năm kinh nghiệm trong lĩnh vực tuyển dụng và việc làm. Anh tập trung vào việc cung cấp thông tin hữu ích cho người lao động, đặc biệt là nhóm phổ thông, giúp họ tiếp cận cơ hội việc làm nhanh chóng và hiệu quả.
