Hàm sumif là gì

Hàm SUMIF là hàm tính tổng các giá trị trong một phạm vi đáp ứng điều kiện trong hàm. Nếu chưa biết cách sử dụng hàm SUMIF như thế nào bạn đọc có thể tham khảo hướng dẫn trong bài viết dưới đây và làm theo nhé.

Bài viết liên quan

• Lỗi thường gặp khi sử dụng hàm SUMIF trong Excel
• Bài tập tính chi phí du lịch sử dụng hàm Vlookup, Countif, Sum và Sumif
• Cách kết hợp hàm Sumif và Vlookup, sử dụng hàm tìm kiếm và tính tổng trong Excel
• Hàm DVARP trong Excel
• Hàm CUBESET trong Excel, xác định tập hợp phần tử được tính

SUMIF là hàm tính tổng có điều kiện các ô đáp ứng một điều kiện duy nhất trong Excel. Các điều kiện, tiêu chí có thể được áp dụng cho ngày, số và văn bản. Hàm tính tổng SumIF cũng hỗ trợ các phép tính logic [>, , =] và các ký tự đại diện [*,?] để đối sánh và trả về kết quả là tổng các giá trị được cung cấp.

Hướng dẫn dùng hàm SUMIF, cú pháp và ví dụ minh họa

Đọc đến đây, chắc hẳn bạn sẽ thắc mắc hàm SUMIF và SUMIFS có gì khác nhau? Để trả lời câu hỏi này, bạn cần hiểu rằng, SUMIFS cũng là hàm tính tổng, tuy nhiên, khác với SUMIF chỉ tính tổng của các ô đáp ứng 1 tiêu chí duy nhất, SUMIFS sẽ tính tổng các ô đáp ứng "nhiều" tiêu chí khác nhau.

Cách dùng hàm SUMIF - Ví dụ minh họa

1. Định nghĩa hàm SUMIF

Hàm SUMIF là hàm tính tổng các giá trị trong một phạm vi thỏa mãn các điều kiện của nó. Khi sử dụng hàm SUMIF bạn sẽ:

+ Nhanh chóng tính được tổng dãy số theo các điều kiện mà bạn xác định.
+ Có thể kết hợp với nhiều hàm trong Excel.
+ Tiện lợi, thuận tiện trong công việc.

1. Cấu trúc hàm SUMIF trong EXCEL

Cấu trúc hàm SUMIF: =SUMIF[range, criteria, sum_range]
 

Trong đó, các tham số hàm SUMIF tương ứng như sau: 
 

- Range [Phạm vi]: Phạm vi các ô bạn muốn áp dụng tiêu chí Criteria
 

- Criteria [tiêu chí]: Là Tiêu chí xác lập điều kiện cho giá trị được tính tổng. Nó có thể là số, biểu thức hoặc một chuỗi văn bản.
 

- Sum_range: Vùng cần tính tổng hay dải ô cần tính tổng.

Chú ý: Do tính toán trong ô của Excel, nên hàm SUMIF tính tổng này trên các phiên bản Excel 2016, Excel 2013, hay các phiên bản đời trước như Excel 2010, 2007, 2003 đều áp dụng cấu trúc hàm như nhau.

Bài tập hàm SUMIF: Bài toán sử dụng hàm SumIF tính tổng phụ cấp cho những người có chức vụ là "Nhân Viên" trong bảng số liệu dưới đây:

Với công thức cho ô D15 là : =SUMIF[C5:C14,"Nhân Viên",D5:D14]

Ta thu được kết quả như sau: 2500000. Khi chúng ta kiểm tra lại kết quả thì thấy rằng trong VD hàm Sumif này có 5 người có chức vụ là "Nhân viên", như vậy tổng phụ cấp cho những người đó với kết quả là 2500000 là chính xác.

* Một vài lưu ý khi sử dụng hàm SUMIF:

- Khi sum_range bị bỏ qua, tổng sẽ được tính theo Range [thay thế bằng những ô trong phạm vi đánh giá].

- SUMIF không phân biệt chữ hoa chữ thường. Bạn có thể tính tổng các giá trị trong đó dữ liệu text có thể là "Taimienphi.vn" hoặc "TAIMIENPHI.VN".

- Criteria [tiêu chí] có chứa chữ hay ký hiệu toán học đều phải được đặt trong dấu ngoặc kép [" "]. Ví dụ ">32", "Taimienphi.vn".

- Tham chiếu ô trong tiêu chí không được đặt trong dấu ngoặc kép, tức là "=10/1/2014", C2:C9] - SUMIF[B2:B9, ">=11/1/2014", C2:C9]

Công thức trên tính tổng giá trị trong các ô C2:C9 nếu ngày trong cột B nằm trong khoảng từ 1/10/2014 – 31/10/2014.

Hàm SUMIF đầu tiên tính giá trị các ô C2:C9 trong đó ô tương ứng trong cột B lớn hơn hoặc bằng ngày đầu tiên [trong ví dụ trên là ngày 1/10]. Sau đó trừ đi các giá trị rơi vào sau ngày cuối cùng [ngày 31/10] được trả về bởi hàm SUMIF thứ hai.

2.10. Tính tổng giá trị trong các cột

Để hiểu rõ về vấn đề, cùng xem xét ví dụ dưới đây. Giả sử bạn có một bảng tổng kết doanh thu bán hàng hàng tháng. Vì có nhiều mặt hàng khác nhau và được bán ở nhiều cửa hàng khác nhau nên bạn muốn tính tổng doanh thu một sản phẩm một:

Chẳng hạn như bạn muốn tính tổng số táo bán ở tất cả các cửa hàng trong 3 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 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], vì công thức này sẽ thêm các giá trị tương ứng “táo” trong cột C. Đây không phải là những gì mà bạn đang tìm kiếm.

Giải pháp hợp lý nhất là tạo một cột phụ để tính tổng từng mặt hàng một, sau đó tham chiếu cột phụ đó trong sum_range.

Nhập công thức SUM đơn giản trong ô F2, sau đó nhập vào cột F công thức: = SUM [C2: E2].

Bước tiếp theo bây giờ là viết công thức SUMIF như dưới đây:

=SUMIF[A2:A9, “táo”, F2:F9]

Hoặc:

=SUMIF[A2:A9, H1, F2:F9]

Trong các công thức trên, range và sum_range có cùng số hàng và số cột, bao gồm 1 cột và 8 dòng, và trả về kết quả:

Nếu muốn tính tổng giá trị mà không cần tạo cột phụ, cách đơn giản là viết từng công thức SUMIF riêng cho các cột mà bạn muốn tính tổng, sau đó sử dụng hàm SUM để tính tổng:

=SUM[SUMIF[A2:A9,I1,C2:C9], SUMIF[A2:A9,I1,D2:D9], SUMIF[A2:A9,I1,E2:E9]]

Hoặc cách khác là sử dụng công thức mảng phức tạp hơn [đừng quên sử dụng tổ hợp phím Ctrl + Shift + Enter]:

{=SUM[[C2:C9+D2:D9+E2:E9]*[--[A2:A9=I1]]]}

Cả 2 công thức trên đều trả về kết quả là 2070.
 

4. Một số lưu ý khi sử dụng hàm SUMIF

Có nhiều lý do khiến hàm SUMIF không hoạt động, không trả về kết quả. Đôi khi công thức mà bạn sử dụng không trả về giá trị mà bạn mong đợi có thể là do kiểu dữ liệu trong ô hoặc tham số không thích hợp với hàm SUMIF. Nếu không muốn xảy ra lỗi, dưới đây là những gì bạn cần kiểm tra:

1. Các tham số phạm vi và sum_range phải là vùng dữ liệu chứ 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 phải là tham chiếu vùng, chẳng hạn như A1: A10. Nếu là mảng, chẳng hạn như {1,2,3}, Excel sẽ trả lại một thông báo lỗi.

Công thức chính xác: =SUMIF[A1:A3, “hoa”, C1:C3]

Công thức sai: =SUMIF[{1,2,3}, “hoa”, C1:C3]

2. Cách tính tổng các giá trị từ sheet hoặc bảng tính khác.

Giống như hầu hết các hàm Excel khác, hàm SUMIF có thể tham chiếu các sheet hoặc các bảng tính khác, miễn là các sheet và bảng tính đang mở.

Ví dụ: Công thức dưới đây tính tổng các giá trị trong các ô F2:F9 trong Sheet 1 của Book 1 nếu một ô tương ứng trong cột A nằm trong cùng sheet có chứa mặt hàng là “táo”:

=SUMIF[[Book1.xlsx]Sheet1!$A$2:$A$9,“táo”,[Book1.xlsx]Sheet1!$F$2:$F$9]

Tuy nhiên công thức trên sẽ không hoạt động nếu Book 1 bị đóng. Điều này xảy ra vì vùng dữ liệu được tham chiếu bởi công thức SUMIF trong các bảng tính không được tham chiếu đến các mảng, và vì không có mảng nào được tham chiếu trong các tham số range và sum_range, Excel sẽ trả về thông báo lỗi #VALUE! .

3. Để tránh lỗi xảy ra, đảm bảo range và sum_range phải cùng kích thước.

Trong các phiên bản Excel mới hơn, tham số range và sum_range không nhất thiết phải có cùng kích thước. Trong phiên bản Excel 2000 và các phiên bản cũ hơn, nếu kích thước của range và sum_range không bằng nhau sẽ gây ra lỗi. 

Tuy nhiên trong các phiên bản Excel mới hơn như Excel 2010 và Excel 2016, công thức SUMIF phức tạp hơn nhiều, trong đó tham số sum_range có số hàng, số cột ít hơn so với tham số range. Đó là lý do tại sao các tham số range và sum_range phải cùng kích thước, để tránh lỗi xảy ra.

Cách tránh lỗi VALUE khi sử dụng hàm SUMIF giữa các bảng tính

Khi sử dụng hàm SUMIF giữa các bảng tính, trong trường hợp nếu bảng tính nguồn không mở, bạn sẽ nhận được thông báo lỗi VALUE. Lỗi này xảy ra nếu các ô trong bảng tính chứa công thức có các hàm SUMIF, COUNTIF, hoặc hàm COUNTBLANK bị đóng. Để tránh lỗi này bằng cách sử dụng kết hợp hàm SUM và hàm IF trong một công thức mảng.

Công thức mảng là công thức có thể thực hiện nhiều phép tính trên một hoặc nhiều mục trong một mảng. Các công thức mảng hoạt động trên hai hay nhiều giá trị được gọi là tham số mảng.

Trong ví dụ dưới đây là tính tổng doanh thu các loại mặt hàng thủy hải sản. Kết quả được trả về trong bảng tính có chứa công thức và dữ liệu nguồn nằm trong bảng tính dữ liệu.

Lưu ý: Bạn có thể áp dụng cách này cho Excel 2010, 2013, 2016 cho Windows.

Bước 1: Mở bảng tính chứa dữ liệu nguồn [Data workbook].
Bước 2: Mở bảng tính có chứa công thức [Report workbook].
Bước 3: Chọn ô C5 trong bảng tính có chứa công thức.
Bước 4: Sử dụng nút FX trên thanh công thức [Formula Bar], xác định vị trí hàm Sum.
Bước 5: Để lồng hàm IF, trên thanh công thức, tại mục Name Box, từ menu bạn chọn IF.
Bước 6: Nếu hàm IF không hiển thị trong menu, bạn chọn More Functions rồi tìm và chọn hàm IF.
Bước 7: Nhập các đối số như dưới đây:

Logical_test : Data.xlsx!$A$23:$A$30=”Seafood”.
Value_If _true: SUM[Data.xlsx!$D$23:$D$30].
Value_if_false: 0.

Bước 8: Để hoàn tất công thức mảng, sử dụng tổ hợp phím Ctrl + Shift + Enter.
Bước 9: Chọn Yes nếu được hỏi công thức đã chính xác chưa. Tên vùng dữ liệu [name range] có thể xác định cho CategoryNames và ProductSales.

Bằng cách trên bạn không còn phải lo lắng về lỗi sẽ xảy ra nữa, điều này giúp bạn tiết kiệm được một khoảng thời gian đáng kể.

Trên đây là Taimienphi.vn sử dụng SUMIF [một loại hàm cơ bản trong excel] với 1 điều kiện là tính phụ cấp cho người có chức vụ là nhân viên, để sử dụng hàm tính tổng với nhiều điều kiện thì bạn chuyển sang sử dụng SUMIFS. Trong quá trình thực hành tính toán với hàm Sumif, bạn có thể gặp phải một vài lỗi như lỗi cú pháp, lỗi VALUE,... Để khắc phục vấn đề này, bạn cần tìm hiểu bài viết Lỗi hàm SUMIF để hiểu và sửa lỗi.

Ngoài ra bạn một hàm điều kiện tương tự như SUMIF là IF cũng được người dùng Excel sử dụng phổ biến, tuy nhiên hàm IF chỉ truy vấn điều kiện và đưa ra kết quả phù hợp với điều kiện chứ không tính tổng như hàm SUMIF, các bạn nên chú ý để áp dụng cho đúng nhé.

Qua ví dụ trên chắc hẳn bạn đã hiểu được cách dùng hàm SUMIF trên bảng tính Excel. Nhờ đó mà bạn có thể vận dụng hàm cơ bản trong Excel này vào công việc của mình hiệu quả nhất.

Hàm cơ bản SUMIF hàm tính tổng có điều kiện và cách sử dụng hàm SUMIF để tính toán có điều kiện. Để sử dụng tốt hàm cơ bản Vlookup - hàm tìm kiếm trong Excel, các bạn có thể tham khảo cách sử dụng Hàm Vlookup đã được chúng tôi hướng dẫn nhé.

//thuthuat.taimienphi.vn/cach-dung-ham-sumif-trong-excel-636n.aspx
Video hướng dẫn