Hàm SUMIF: Công thức và cách sử dụng

Hướng dẫn cho thấy cách thực hiện hàm SUMIF trong Excel để tính tổng các ô có điều kiện. Trọng tâm chính của chúng tôi sẽ là các ví dụ về công thức trong cuộc sống thực với tất cả các loại tiêu chí bao gồm ngày tháng, văn bản, số, ký tự đại diện, khoảng trống và không khoảng trống.

Microsoft Excel có một số chức năng để tóm tắt các tập dữ liệu lớn cho các báo cáo và phân tích. Một trong những hàm hữu ích nhất có thể giúp bạn hiểu được một tập hợp dữ liệu đa dạng khó hiểu là hàm SUMIF. Thay vì cộng tất cả các số trong một phạm vi, nó cho phép bạn chỉ tính tổng những giá trị đáp ứng tiêu chí của bạn.

Hàm SUMIF trong Excel

Hàm SUMIF trong Excel trả về tổng giá trị trong một phạm vi đáp ứng điều kiện bạn chỉ định.

Nó có cú pháp và đối số sau:

SUMIF (phạm vi, tiêu chí, [sum_range])

Ở đâu:

  • Phạm vi (bắt buộc) – phạm vi ô được đánh giá bởi tiêu chí.
  • Tiêu chí (bắt buộc) – điều kiện xác định ô nào sẽ được thêm vào. Nó có thể được cung cấp dưới dạng số, văn bản, ngày tháng, tham chiếu ô, biểu thức logic hoặc hàm.
  • Sum_range (tùy chọn) – phạm vi để cộng các số. Nếu bỏ qua, thì phạm vi là tổng hợp.

Hàm có sẵn trong tất cả các phiên bản Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 và các phiên bản cũ hơn.

Công thức hàm SUMIF cơ bản

Đối với người mới bắt đầu, hãy xây dựng công thức hàm SUMIF trong Excel ở dạng đơn giản nhất.

Trong bảng mẫu bên dưới, giả sử bạn muốn nhận được tổng doanh số cho một khu vực cụ thể như North. Để hoàn tất, chúng tôi xác định các đối số sau:

  • Phạm vi – danh sách các vùng (B2: B10).
  • Tiêu chuẩn – “North” hoặc ô chứa vùng quan tâm (F1).
  • Sum_range – số tiền bán hàng được cộng dồn (C2: C10).

Đặt các đối số lại với nhau, chúng ta nhận được các công thức sau:

=SUMIF(B2:B10, "north", C2:C10)

hoặc là

=SUMIF(B2:B10, F1, C2:C10)

Cả hai chỉ tính tổng các ô cho Bắc khu vực:
Sử dụng hàm SUMIF trong Excel

Cách sử dụng hàm SUMIF trong Excel

Thoạt nhìn đơn giản, SUMIF là một hàm khá phức tạp. Những lưu ý sau đây sẽ giúp bạn hiểu rõ hơn về logic bên trong của nó và tránh những lỗi thường gặp.

1. SUMIF chỉ hỗ trợ một tiêu chí

Cú pháp của hàm SUMIF chỉ có chỗ cho một điều kiện. Để tính tổng với nhiều tiêu chí, hãy sử dụng SUMIFS chức năng (thêm các ô đáp ứng tất cả các điều kiện) hoặc xây dựng một Công thức SUMIF với nhiều tiêu chí OR (tính tổng các ô đáp ứng bất kỳ điều kiện nào).

2. Cú pháp hàm SUMIF

Đối với tiêu chí, hàm SUMIF cho phép sử dụng các kiểu dữ liệu khác nhau bao gồm văn bản, số, ngày tháng, tham chiếu ô, toán tử logic (>, <, =, <>), ký tự đại diện (?, *, ~) Và các hàm khác. Cú pháp của các tiêu chí như vậy là khá cụ thể.

Nếu tiêu chí đối số bao gồm một giá trị văn bản, ký tự đại diện hoặc toán tử lôgic theo sau là văn bản, số hoặc ngày, đặt toàn bộ tiêu chí trong dấu ngoặc kép. Ví dụ:

=SUMIF(B2:B10, "north*", C2:D10)

=SUMIF(C2:D10, ">100")

=SUMIF(B2:B10, "<>north", C2:D10)

=SUMIF(C2:C10, "<=9/10/2020", B2:B10)

Khi một toán tử logic được theo sau bởi một tham chiếu ô hoặc một hàm khác, các tiêu chí phải được cung cấp dưới dạng một chuỗi. Đối với điều này, bạn sử dụng dấu và (&) để nối một toán tử logic và một tham chiếu hoặc hàm. Ví dụ:

=SUMIF(B2:B10, ">"&F1)

=SUMIF(C2:D10, "<="&TODAY(), B2:B10)

Xin lưu ý rằng các toán tử so sánh được đặt trong dấu ngoặc kép trong khi tham chiếu ô thì không.

3. Range và sum_range phải có cùng kích thước

Để công thức hàm SUMIF hoạt động chính xác, sum_range đối số phải có cùng kích thước với phạm vi, nếu không bạn có thể nhận được kết quả sai lệch. Vấn đề là sum_range chỉ xác định ô phía trên bên trái của phạm vi sẽ được tính tổng, khu vực còn lại được xác định bởi kích thước và hình dạng của phạm vi tranh luận.

Với những điều trên, công thức dưới đây sẽ thực sự tính tổng các ô trong C2: C10 chứ không phải trong C2: D10. Tại sao? Bởi vì phạm vi bao gồm 1 cột và 9 hàng, và sum_range.

=SUMIF(B2:B10, "north", C2:D10)

4. Range và sum_range không được là mảng

Mặc dù SUMIF là hàm có thể xử lý một hằng số mảng trong tiêu chí như được hiển thị trong ví dụ này, nó không hỗ trợ các mảng trong phạm visum_range. Hai đối số này chỉ có thể là phạm vi ô.

5. Hàm SUMIF không nhận dạng chữ hoa chữ thường

Theo thiết kế, SUMIF trong Excel là hàm không phân biệt chữ hoa chữ thường, nghĩa là nó xử lý các chữ cái viết hoa và viết thường là các ký tự giống nhau. Để làm một công thức SUMIF phân biệt chữ hoa chữ thường, sử dụng hàm SUMPRODUCT cùng với EXACT.

Ví dụ về công thức hàm SUMIF trong Excel

Bây giờ bạn đã biết các quy tắc chính của việc sử dụng hàm SUMIF trong Excel, đã đến lúc áp dụng kiến ​​thức của bạn vào một số thứ thực tế và cải thiện các kỹ năng bạn đã học.

Ví dụ 1. SUMIF lớn hơn hoặc nhỏ hơn

Để tính tổng các số lớn hơn hoặc nhỏ hơn một giá trị cụ thể, hãy định so sánh dùng hàm SUMIF với một trong các điều sau toán tử logic:

  • Lớn hơn (>)
  • Lớn hơn hoặc bằng (> =)
  • Nhỏ hơn (<)
  • Nhỏ hơn hoặc bằng (<=)

Trong phạm vi B2: B10, giả sử bạn muốn tính tổng các số lớn hơn 200. Để thể hiện điều kiện này, hãy đặt toán tử so sánh (>) trước số và đặt cấu trúc trong dấu ngoặc kép:

=SUMIF(B2:B10, ">200")

Nếu số mục tiêu là một ô khác, giả sử E1, nối toán tử logic và tham chiếu ô:

=SUMIF(B2:B10, ">"&E1)

SUM IF lớn hơn

Theo cách tương tự, bạn có thể tính tổng các giá trị nhỏ hơn 200 bằng cách sử dụng toán tử nhỏ hơn (<):

=SUMIF(B2:B10, "<200")

Ví dụ 2. SUMIF bằng

Công thức SUMIF với tiêu chí “bằng” hoạt động cho cả số và văn bản. Trong các tiêu chí như vậy, dấu bằng không thực sự bắt buộc.

Ví dụ: để tìm tổng số các mặt hàng được giao trong 3 ngày, bất kỳ công thức nào dưới đây sẽ thực hiện:

=SUMIF(C2:C10, 3, B2:B10)

hoặc là

=SUMIF(C2:C10, "=3", B2:B10)

Đến tổng nếu bằng ô, chỉ cần cung cấp tham chiếu ô cho các tiêu chí:

=SUMIF(C2:C10, F1, B2:B10)

Trong đó B2: B10 là số lượng, C2: C10 là thời gian vận chuyển và F1 là thời gian giao hàng mong muốn.
SUM IF bằng ô

Tương tự như vậy, bạn có thể sử dụng tiêu chí “bằng” với các giá trị văn bản. Ví dụ, để thêm tất cả Apples số tiền, hãy chọn bất kỳ công thức nào dưới đây:

=SUMIF(A2:A10, "apples", B2:B10)

=SUMIF(A2:A10, "=apples", B2:B10)

=SUMIF(A2:A10, F1, B2:B10)

Trong đó A2: A10 là danh sách các mục để so sánh với giá trị trong F1.
SUM IF bằng văn bản

Ghi chú. Các công thức trên ngụ ý rằng tiêu chí phù hợp với toàn bộ nội dung ô. Do đó, hàm SUMIF sẽ cộng lại Apples đã bán để tính tổng các kết quả phù hợp từng phần, hãy tạo “nếu ô chứa“tiêu chí như thế này Công thức ký tự đại diện SUMIF.

Ví dụ 3. SUMIF không bằng

Để xây dựng tiêu chí “không bằng”, hãy sử dụng toán tử logic “<>”.

Khi một giá trị, văn bản hoặc số, được mã hóa cứng trong tiêu chí, hãy nhớ đặt toàn bộ cấu trúc bằng dấu ngoặc kép.

Ví dụ, để tính tổng số tiền với lô hàng khác 3 ngày, công thức như sau:

=SUMIF(C2:C10, "<>3", B2:B10)

Để tìm tổng số tất cả các mục ngoại trừ Apples, công thức là:

=SUMIF(A2:A10, "<>apples", B2:B10)

Khi giá trị tiêu chí nằm trong một ô khác, hãy nối toán tử “không bằng với” và tham chiếu ô như sau:

=SUMIF(A2:A10, "<>"&F1, B2:B10)

SUM NẾU không bằng

Ví dụ 4. SUMIF không trống

Để tạo loại công thức “nếu ô không trống thì tính tổng”, hãy sử dụng “<>” làm tiêu chí. Điều này sẽ cộng tất cả các ô có chứa bất kỳ thứ gì trong đó, bao gồm cả các chuỗi có độ dài bằng không.

Ví dụ: đây là cách bạn có thể tính tổng doanh số bán hàng cho tất cả các khu vực, tức là trong đó cột B không trống:

=SUMIF(B2:B10, "<>", C2:D10)

SUM NẾU không trống

Ví dụ 5. SUMIF trống

Để tính tổng các ô trong một số cột nếu ô tương ứng trong một cột khác trống, hãy sử dụng một trong các tiêu chí sau:

  • “” – tính tổng các ô nếu một ô khác trống; các ô chứa chuỗi trống cũng được coi là trống.
  • “=” – để tính tổng các ô hoàn toàn trống.

Trong bảng mẫu của chúng tôi, công thức sau sẽ tính tổng doanh số bán hàng cho các vùng không xác định, tức là ô trong cột B trống:

=SUMIF(B2:B10, "", C2:D10)

SUM IF trống

Ví dụ 6. Ký tự đại diện SUMIF trong Excel

Để tính tổng các ô dựa trên trận đấu từng phần, hãy bao gồm một trong các ký tự đại diện sau trong tiêu chí của bạn:

  • Dấu hỏi (?) Để thay thế bất kỳ ký tự đơn nào.
  • Dấu hoa thị

để thay thế bất kỳ số ký tự nào. Ví dụ: công thức dưới đây sẽ chỉ tính tổng doanh số cho phía North

=SUMIF(B2:B10, "north", C2:D10)

khu vực: Tổng doanh số cho tất cả các khu vực phía North, bao gồm North ,North-East North-West

=SUMIF(B2:B10, "north*", C2:D10)

, hãy đặt một dấu hoa thị ngay sau văn bản:

=SUMIF(B2:B10, F1&"*", C2:D10)

Nếu bạn muốn nhập vùng quan tâm trong một ô được xác định trước (F1), sau đó nối một tham chiếu ô và một ký tự đại diện được đặt trong dấu ngoặc kép:

Công thức ký tự đại diện SUMIF để tính tổng nếu ô chứa Để phù hợp với một dấu chấm hỏi chữ hoặc là dấu hoa thị

, đặt dấu ngã (~) trước ký tự, ví dụ: “~?” hoặc “~ *”.

=SUMIF(B2:B10, "*~*", C2:D10)

Ví dụ: để tổng hợp doanh số cho các khu vực được đánh dấu *, hãy sử dụng “* ~ *” cho tiêu chí. Trong trường hợp này, dấu hoa thị đầu tiên là ký tự đại diện và dấu hoa thị thứ hai là ký tự dấu hoa thị theo nghĩa đen:

=SUMIF(B2:B10, "*"&"~"&F1, C2:D10)

Nếu tiêu chí (* trong trường hợp này) được cho là được nhập vào một ô riêng biệt, thì hãy nối một dấu ngã và tham chiếu ô, như sau:

Công thức SUMIF có dấu hoa thị làm tiêu chí

Ví dụ 7. Hàm SUMIF trong Excel với ngày tháng Việc sử dụng ngày làm tiêu chí SUMIF rất giống với việc sử dụng số. Điều quan trọng nhất là cung cấp ngày ở định dạng mà Excel hiểu được. Nếu bạn không chắc định dạng ngày nào được hỗ trợ và định dạng nào không, Hàm DATE có thể là một giải pháp.

=SUMIF(C2:C10, "<9/10/2020", B2:B10)

Giả sử bạn đang muốn tính tổng doanh số cho các mặt hàng được giao trước ngày 10 tháng 9 năm 2020, tiêu chí có thể được thể hiện theo cách sau:

=SUMIF(C2:C10, "<"&DATE(2020,9,10), B2:B10)

hoặc là

=SUMIF(C2:C10, "<"&F1, B2:B10)

hoặc là
Trong đó F1 là ngày mục tiêu.

SUMIF sử dụng ngày làm tiêu chí Để tính tổng các ô dựa trên ngày hôm nay , bao gồm cái TODAY

=SUMIF(C2:C10, "<"&TODAY(), B2:B10)

chức năng trong tiêu chí của bạn. Ví dụ: công thức dưới đây sẽ tính tổng doanh số bán hàng với ngày giao hàng trước ngày hôm nay:

Tính tổng các ô sử dụng ngày hôm nay cho tiêu chí Tổng hợp trong một phạm vi ngày , bạn cần xác định ngày nhỏ hơn và lớn hơn một cách riêng biệt. Điều này có thể được thực hiện với sự trợ giúp của SUMIFS

chức năng hỗ trợ nhiều tiêu chí. Ví dụ, để tính tổng nếu một ngày là giữa hai ngày

=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1)

, đây là công thức để sử dụng:
Trong đó B2: B10 là phạm vi tổng, C2: C10 là danh sách các ngày cần kiểm tra, F1 là ngày bắt đầu và G1 là ngày kết thúc.

SUMIF giữa hai ngày

Công thức SUMIF phân biệt chữ hoa chữ thường trong Excel Như đã đề cập, hàm SUMIF trong Excel là trường hợp không nhạy cảm theo tự nhiên. Để tính tổng các ô có điều kiện xem xét chữ hoa, bạn sẽ phải sử dụng một vài hàm khác, cụ thể là hàm SUMPRODUCT và EXACT xử lý chữ hoa và chữ thường là các ký tự khác nhau:SUMPRODUCT (- (CHÍNH XÁC (phạm vi ,tiêu chí )),sum_range) Giả sử bạn có một danh sách các mã mặt hàng trong cột A. Trong đó chữ hoa và chữ thường xác định các mặt hàng khác nhau. Mục tiêu của bạn là tính tổng các số trong cột B cho một mục cụ thể, chẳng hạn A-01.

Để hoàn tất, bạn có thể nhập trực tiếp mục đích vào 2 nd

=SUMPRODUCT(--(EXACT(A2:A10, "A-01")), B2:B10)

đối số của hàm EXACT:

=SUMPRODUCT(--(EXACT(A2:A10, E1)), B2:B10)

Hoặc nhập mã mặt hàng vào một ô riêng biệt (E1) như trong ảnh chụp màn hình:

Công thức hàm SUMIF phân biệt chữ hoa chữ thường

Công thức này hoạt động như thế nào:

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

Ở trung tâm của công thức, hàm EXACT so sánh mục đích với từng mục trong danh sách nguồn và trả về TRUE. Nếu tìm thấy kết quả khớp chính xác, nếu không thì là FALSE: A toán tử kép một ngôi

{1;0;0;0;0;0;1;0;0}

(-) chuyển đổi TRUE và FALSE thành 1 và 0, tương ứng:

SUMPRODUCT({1;0;0;0;0;0;1;0;0}, {250;155;130;255;160;280;170;285;110})

Hàm SUMPRODUCT nhân các phần tử của mảng trên với các mục tương ứng trong B2: B10:

SUMPRODUCT({250;0;0;0;0;0;170;0;0})

Và bởi vì nhân với 0 cho không, chỉ những mục mà EXACT trả về TRUE (1) mới tồn tại:

Cuối cùng, SUMPRODUCT cộng các sản phẩm và xuất ra tổng.

Đó là cách sử dụng hàm SUMIF trong Excel. Hy vọng rằng, các ví dụ công thức của chúng tôi đã cung cấp cho bạn một số hiểu biết tốt. Dù sao, tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Trả lời