Cách sử dụng Hàm SUMIF trong Excel chính xác

Hàm SUMIF là hàm tính tổng có điều kiện phổ biến được dùng nhiều đối với nhiều người dùng Excel. Hàm này vô cùng tiện lợi khi có thể kết hợp nhiều hàm khác lại với nhau. Người dùng có thể sử dụng hàm này để tính toán cho một thuộc tính trong nhiều thuộc tính có trong cột đó mà không cần phải phân chia thành nhiều bảng hoặc tính tổng riêng lẻ. Để tìm hiệu về cách sử dụng hàm SUMIF này, các bạn hãy theo dõi bài viết dưới đây ngay nhé.

CÁCH DÙNG HÀM SUMIF - VÍ DỤ MINH HỌA

Cấu Trúc Hàm SUMIF: SUMIF(range, criteria,sum_range)

Chú thích:

• Range: vùng ô được chọn có chứa các ô điều kiện.

• Criteria: điều kiện để thực hiện hàm này.

• Sum_range: Vùng ô cần tính tổng.

VD: Bài toán sử dụng hàm SumIF để tính tổng phụ cấp cho "Nhân Viên" trong bảng dưới đây:

Với công thức ta tính tổng phụ cấp tại ô D15 là : =SUMIF(C5:C14,"Nhân Viên",D5:D14) cho ra kết quả bằng 2500000.

Tính phụ cấp cho “Nhân Viên” thì trong bảng có 5 người như vậy tổng phụ cấp cho 5 người này bằng 2500000 là tuyệt đối chính xác.

CÁCH DÙNG HÀM SUMIF – VÍ DỤ CÔNG THỨC

SUMIF Lớn Hơn, Nhỏ Hơn Hoặc Bằng

Một số công thức SUMIF mà bạn sử dụng để so sánh giá trị lớn hơn, nhỏ hơn hoặc bằng với giá trị được cho trước.

Lưu ý: Lưu ý trong các công thức Excel SUMIF, theo sau toán tử so sánh là một số hoặc text phải luôn luôn được đóng trong dấu ngoặc kép ("").

Cách Sử Dụng Hàm SUMIF Với Điều Kiện Là Text

Hàm SUMIF cho phép bạn có thể thêm các giá trị tùy thuộc vào ô tương ứng trong cột khác có chứa text hoặc không.

Lưu ý bạn sẽ phải sử dụng các công thức SUMIF khác nhau để đối sánh chính xác hoặc đối sánh từng phần, các công thức này được liệt kê trong bảng dưới đây.

Tiếp theo cùng xem công thức hàm Sum không tương đương hoạt động như thế nào. Trong ví dụ dưới đây là tổng số tiền tất cả các mặt hàng không phải là “goldfinger bananas”:

=SUMIF(A2:A7,"<> goldfinger bananas", C2:C7)

Lưu ý: Cũng giống như các hàm Excel khác, hàm SUMIF không phân biệt chữ hoa và chữ thường, tức là dù bạn có viết “<>bananas”, “<>Bananas” hay  “<>BANANAS” thì cuối cùng cũng trả lại một kết quả mà thôi.

Sử Dụng Toán Tử So Sánh Với Tham Chiếu Ô

Nếu muốn công thức Excel SUMIF chung, bạn có thể thay thế giá trị số hoặc text trong các điều kiện bằng tham chiếu ô:

=SUMIF(A2:A8,"<>"&F1, C2:C8)

Trong trường hợp này bạn không cần thay đổi công thức để tính tổng giá trị dựa trên các tiêu chí khác, chỉ cần nhập một giá trị mới vào ô được tham chiếu.

Lưu ý: Khi sử dụng một biểu thức logic với tham chiếu ô, bạn phải sử dụng dấu ngoặc kép (“”) để bắt đầu chuỗi text và dấu và (&) để nối và kết thúc chuỗi, ví dụ “<>” & F1.

Không cần sử dụng toán tử “bằng” (=) trước một tham chiếu ô, vì vậy cả hai công thức dưới đây đều chính xác và cho kết quả tương đương nhau:

Công thức 1: =SUMIF(A2:A8, "="&F1, C2:C8)

Công thức 2: =SUMIF(A2:A8, F1, C2:C8)

Công Thức SUMIF Với Ký Tự Đại Diện

Nếu mục đích của bạn là tính tổng các ô dựa trên các điều kiện “text” và muốn tổng hợp bằng cách đối sánh một phần, trường hợp này bạn sẽ phải sử dụng các ký tự đại diện trong công thức SUMIF.

Dưới đây là các ký tự có sẵn:

- Dấu hoa thị () - đại diện cho bất kỳ ký tự nào

- Dấu hỏi chấm (?) - đại diện cho một ký tự duy nhất tại một vị trí cụ thể

Ví dụ 1: Tính tổng giá trị dựa trên kết hợp từng phần

Giả sử nếu muốn tính tổng số tiền của tất cả các mặt hàng là chuối, sử dụng công thức SUMIF dưới đây:

=SUMIF(A2:A8, “chuối”, C2:C8) - điều kiện bao gồm phần text được đặt trong dấu hoa thị ().

=SUMIF(A2:A8, “”&F1&“”, C2:C8) – điều kiện bao gồm các ô tham chiếu được đặt trong dấu hoa thị, sử dụng dấu (&) trước và sau một tham chiếu ô để nối chuỗi.

Nếu chỉ muốn đếm những ô bắt đầu hoặc kết thúc bằng một text cụ thể nào đó, chỉ cần thêm vào trước hoặc sau text:

=SUMIF(A2:A8, “chuối”, 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ừ “chuối”.

=SUMIF(A2:A8, “chuối”, 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ừ “chuối”.

Mẹo:

Sử dụng toán tử nối (&) để nối các ký tự đại diện với tham chiếu ô. Trong ví dụ trên, bạn cũng có thể sử dụng công thức sau để tính tổng các mặt hàng “chuối” trong kho:

=SUMIF(A2:A8, ""&F1&"", C2:C8)

Ví dụ 2. Tính tổng giá trị với các ký tự nhất định

Trong trường hợp nếu muốn tính tổng giá trị một số ký tự, chính xác là 6 ký tự, bạn sử dụng công thức dưới đây:

=SUMIF(A2:A8, "??????", C2:C8)

Ví dụ 3. Tính tổng các ô tương ứng với các giá trị text

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ính tổng các ô tương ứng giá trị text, sử dụng công thức SUMIF dưới đây:

=SUMIF(A2:A8,“?”, C2:C8) - cộng giá trị trong các ô C2:C8 nếu một ô tương ứng trong cột A chứa ít nhất 1 biểu tượng.

=SUMIF(A2:A8,“”,C2:C8) - bao gồm các ô trống, chứa các chuỗi độ dài bằng 0 được trả về bởi các công thức khác, ví dụ: = “”.

Cả 2 công thức trên đều bỏ qua các giá trị không phải là text, chẳng hạn như các lỗi, các phép toán logic, số và ngày tháng.

Tính Tổng Giá Trị Lớn Nhất Hoặc Nhỏ Nhất Trong Một Vùng Dữ Liệu

Để tính tổng lớn nhất hoặc nhỏ nhất trong một vùng, sử dụng kết hợp hàm SUM với các hàm LARGE hoặc SMALL.

Ví dụ 1: Thêm giá trị lớn nhất / giá trị nhỏ nhất

Nếu muốn tính tổng các giá trị lớn nhất, nhỏ nhất, chẳng hạn như 5, bạn có thể nhập trực tiếp giá trị như trong công thức như dưới đây:

=SUM(LARGE(B1:B10,{1,2,3,4,5})) - tổng của 5 giá trị lớn nhất

=SUM(SMALL(B1:B10,{1,2,3,4,5})) - tổng của 5 giá trị nhỏ nhất

Lưu ý: Nếu có 2 hoặc nhiều giá trị giống nhau, thì chỉ có giá trị đầu tiên được tính.

Ví dụ 2. Tính tổng giá trị trên / dưới

Nếu muốn tính tổng nhiều giá trị, thay vì liệt kê tất cả các giá trị này trong công thức, bạn có thể lồng các hàm ROW và INDIRECT trong công thức SUM. Trong hàm INDIRECT sử dụng số hàng đại diện cho giá trị mà bạn muốn thêm.

Ví dụ các công thức tương ứng dưới đây tính tổng giá trị trên và dưới 15:

=SUM(LARGE(B1:B50,ROW(INDIRECT("1:15"))))

=SUM(SMALL(B1:B50,ROW(INDIRECT("1:15"))))

Vì đây là các công thức mảng, nhớ truy cập các mảng bằng cách sử dụng tổ hợp phím Ctrl + Shift + Enter.

Ví dụ 3. Tính tổng giá trị lớn nhất / nhỏ nhất của các biến

Nếu không muốn phải thay đổi công thức mỗi lần tính tổng giá trị các ô khác nhau, bạn có thể nhập giá trị trong một ô nào đó, thay vì nhập vào công thức. Sau đó sử dụng hàm ROW và INDIRECT, tham chiếu một ô chứa biến, trong ví dụ này là ô E1:

=SUM(LARGE(B1:B50,ROW(INDIRECT("1:"&E1)))) - tổng các biến các giá trị trên cùng.

=SUM(SMALL(B1:B50,ROW(INDIRECT("1:"&E1)))) - tổng các biến các giá trị dưới cùng.

Cần lưu ý một điều đây là các công thức mảng nên bạn sẽ phải sử dụng tổ hợp phím Ctrl + Shift + Enter để hoàn tất.

Cách Tính Tổng Các Ô Tương Ứng Với Các Ô Trống

Ô trống là các ô không chứa gì, không có công thức, không có chuỗi ký tự có độ dài bằng không, được trả về bởi một hàm Excel khác, bạn sử dụng "=" làm điều kiện, như trong công thức SUMIF dưới đây:

=SUMIF(A2:A10,"=",C2:C10)

Nếu ô trống bao gồm các chuỗi có độ dài bằng không (ví dụ: các ô có công thức như = ""), sử dụng "" làm điều kiện:

=SUMIF(A2:A10,"",C2:C10)

Trong cả 2 công thức trên đánh giá các ô trong cột A và nếu phát hiện có bất kỳ ô trống nào, các giá trị tương ứng sẽ được thêm vào cột C.

Cách Tính Tổng Các Ô Tương Ứng Với Các Ô Không Phải Trống

Nếu muốn tính tổng giá trị các ô trong cột C khi một ô tương ứng trong cột A là ô trống, sử dụng "<>" làm điều kiện trong công thức SUMIF:

=SUMIF(A2:A10,"<>",C2:C10)

Công thức trên đế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 ký tự có độ dài bằng không.

Cách Sử Dụng Hàm SUMIF Với Điều Kiện Ngày Tháng

Về cơ bản sử dụng hàm SUMIF để tính tổng các giá trị dựa trên điều kiện ngày tháng giống như cách sử dụng hàm SUMIF với các điều kiện chuẩn như text và số.

Nếu muốn tính tổng giá trị tương ứng với các ngày lớn hơn, nhỏ hơn hoặc bằng với ngày mà bạn chỉ định, chỉ cần sử dụng các toán tử mà Taimienphi.vn giới thiệu ở phần trên.

Dưới đây là một vài ví dụ về công thức Excel SUMIF:

Trường hợp nếu muốn tính tổng các giá trị dựa trên ngày hiện tại, bạn sẽ phải sử dụng kết hợp hàm SUMIF với hàm TODAY() như trong bảng dưới đây:

Ảnh chụp màn hình dưới đây sử dụng công thức cuối cùng để tính tổng số tiền các sản phẩm được ship trong tuần.

Cách Tính Tổng Giá Trị Trong Phạm Vi Ngày Cụ Thể

Khá nhiều người dùng đặt ra câu hỏi cách tính tổng giá trị giữa 2 ngày như thế nào?

Câu trả lời là sử dụng kết hợp hoặc chính xác hơn là sử dụng 2 hàm SUMIF khác nhau. Trong Excel 2007 hoặc cao hơn, bạn có thể sử dụng hàm SUMIFS cho nhiều điều kiện. Tuy nhiên trong bài viết này Taimienphi.vn sẽ chỉ giới thiệu cho bạn về hàm SUMIF.

Dưới đây 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 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.

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.

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é.