Hàm SUMIF trong Excel: ví dụ công thức cụ thể từng trường hợp

13/10/2022 admin

Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn tìm hiểu cách sử dụng hàm SUMIF để tính tổng các ô theo điều kiện. Bài viết sẽ tập trung vào các ví dụ với tiêu chí ngày tháng, văn bản, số, ký tự đại diện, ô rỗng (blank) và ô không rỗng.

Microsoft Excel có chức năng tổng hợp các dữ liệu lớn từ 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 giải quyết một số vấn đề bằng 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 tính tổng giá trị đáp ứng tiêu chí. Hãy cùng Học Excel Online tìm hiểu ngay sau đây.

Hàm SUMIF trong Excel

Hàm SUMIF tính tổng theo điều kiện kèm theo hay tiêu chuẩn mà bạn đưa ra .

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

SUMIF(range, criteria, [sum_range])

Trong đó :

  • Range (Bắt buộc) – vùng dữ liệu được đánh dấu theo tiêu chí.

  • Criteria (Bắt buộc) –

    điều kiện kèm theo hay tiêu chuẩn đưa ra để so sánh với vùng tài liệu ở tham số tiên phong. Nó hoàn toàn có thể được phân phối dưới dạng một số ít, 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) –

    vùng tài liệu sẽ tính tổng. Nếu bị bỏ lỡ, thì khoanh vùng phạm vi sẽ được cộng lại .

Hàm có sẵn trong toàn bộ các phiên bản Excel 365, Excel 2019, Excel năm nay, Excel 2013, Excel 2010 và các phiên bản cũ hơn .

Hàm SUMIF cơ bản

Cho người mới mở màn, tất cả chúng ta hãy kiến thiết xây dựng hàm SUMIF trong Excel ở dạng đơn thuần nhất .Trong bảng mẫu bên dưới, giả sử bạn muốn nhận được tổng doanh thu cho một vùng đơn cử, ví dụ điển hình như North. Để hoàn tất, tất cả chúng ta xác lập các tham số sau :

  • Range – danh sách các vùng (B2:B10).

  • Criterion – “North” hay ô chứa vùng quan tâm (F1).

  • Sum_range – số tiền bán hàng được cộng vào (C2:C10)

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

=SUMIF(B2:B10, “north”, C2:C10)

Hay

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

Cả hai chỉ tính tổng các ô North :
Using SUMIF function in Excel

Làm thế nào để sử dụng hàm SUMIF trong Excel

Thoạt nhìn, SUMIF là một hàm có cú pháp khá phức tạp. Những chú ý quan tâm sau đây sẽ giúp bạn hiểu rõ hơn về logic bên trong của nó và tránh được 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ó 1 điều kiện kèm theo. Để tính tổng với nhiều tiêu chuẩn, hãy sử dụng hàm SUMIFS ( cộng các ô cung ứng tổng thể các điều kiện kèm theo ) hoặc thiết kế xây dựng công thức SUMIF với tiêu chuẩn OR ( tính tổng các ô phân phối bất kỳ điều kiện kèm theo nào ) .

  1. Cú pháp tiêu chí SUMIF

Đối với tiêu chuẩn, hàm SUMIF được cho phép sử dụng các kiểu tài liệu khác nhau gồm có văn bản, số, ngày tháng, tham số ô, phép toán logic ( >, <, =, < > ), ký tự đại diện thay mặt ( ?, *, ~ ) và các hàm khác .
Nếu tham số tiêu chuẩn gồm có giá trị văn bản, ký tự đại diện thay mặt hoặc phép toán logic theo sau là văn bản, số hoặc ngày, hãy đặt hàng loạt tiêu chuẩn 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 phép toán logic được theo sau bởi một tham chiếu ô hoặc một hàm khác, các tiêu chuẩn phải được phân phối dưới dạng một chuỗi. Đối với điều này, bạn sử dụng dấu và ( và ) để nối một phép toán logic và một tham chiếu hoặc hàm. Ví dụ : = SUMIF ( B2 : B10, “ > ” và F1 )
= SUMIF ( C2 : D10, “ < = ” và TODAY ( ), B2 : B10 ) Xin chú ý quan tâm rằng các phép toán so sánh được đặt trong dấu ngoặc kép trong khi tham chiếu ô thì không .

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

Để công thức SUMIF hoạt động giải trí đúng chuẩn, tham số sum_range phải có cùng kích cỡ với dải ô, nếu không bạn hoàn toàn có thể nhận được tác dụng rơi lệch. Vấn đề là sum_range chỉ xác lập ô phía trên bên trái của khoanh vùng phạm vi sẽ được tính tổng, vùng còn lại được xác lập bởi size và hình dạng của tham số khoanh vùng phạm vi .
Với những điều kiện kèm theo 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ì dải ô gồm có 1 cột, 9 hàng và sum_range cũng vậy .
= SUMIF ( B2 : B10, “ north ”, C2 : D10 )

  1. Range và sum_range không thể là mảng

Mặc dù SUMIF hoàn toàn có thể giải quyết và xử lý một hằng số mảng trong các tiêu chuẩn như được hiển thị giống như ví dụ này, nó không tương hỗ các mảng trong dải ô và sum_range. Hai tham số này chỉ hoàn toàn có thể là vùng giá trị .

  1. SUMIF không phân biệt chữ hoa chữ thường

Theo phong cách thiết kế, SUMIF trong Excel không phân biệt chữ hoa chữ thường, nghĩa là nó coi chữ hoa và chữ thường như nhau .. Để tạo công thức SUMIF phân biệt chữ hoa chữ thường, hãy sử dụng hàm SUMPRODUCT cùng với EXACT .

Ví dụ về hàm SUMIF trong Excel

Vì 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 vận dụng kỹ năng và kiến thức thực tiễn và cải tổ các kiến thức và kỹ năng đã học .

Ví dụ 1: SUMIF lớn hơn và nhỏ hơn

Để tính tổng các số lớn hơn hoặc nhỏ hơn một giá trị đơn cử, hãy định dạng tiêu chuẩn SUMIF bằng một trong các phép toán logic sau :

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

“ Trong B2 : B10 ”, giả sử bạn muốn tính tổng các số lớn hơn 200. Để bộc lộ điều kiện kèm theo này, hãy đặt toán phép toán 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 điều kiện kèm theo nằm trong ô khác, giả sử là E1, hãy nối phép toán logic và tham chiếu ô :
= SUMIF ( B2 : B10, “ > ” và E1 )
SUM IF greater than


Theo cách tương tự như, bạn hoàn toàn có thể tính tổng các giá trị nhỏ hơn 200 bằng cách sử dụng phép toán nhỏ hơn ( < ) : = SUMIF ( B2 : B10, “ < 200 ” )

Ví dụ 2. SUMIF bằng

Công thức SUMIF với tiêu chuẩn “ bằng ” hoạt động giải trí cho cả số và văn bản. Trong các tiêu chuẩn 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 loại sản phẩm được giao trong 3 ngày, bất kỳ công thức nào dưới đây sẽ cho tất cả chúng ta hiệu quả đúng :
= SUMIF ( C2 : C10, 3, B2 : B10 )
hay
= SUMIF ( C2 : C10, “ = 3 ”, B2 : B10 )
Để tính tổng nếu bằng ô, chỉ cần phân phối cho tham chiếu ô cho tiêu chuẩn
= SUMIF ( C2 : C10, F1, B2 : B10 )
Trong đó B2 : B10 là số lượng, C2 : C10 là thời hạn luân chuyển và F1 là thời hạn giao hàng mong ước .
SUM IF equal to cell
Tương tự như vậy, bạn hoàn toàn có thể sử dụng tiêu chuẩn “ bằng ” với các giá trị văn bản. Ví dụ : để cộng toàn bộ số lượng Apple, 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à list các mục để so sánh với giá trị trong F1 .
SUM IF equal to text

Lưu ý: 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 thêm doanh số bán hàng của Apple, nhưng không phải là Green Apple. Để tính tổng các kết quả phù hợp, hãy xây dựng tiêu chí “nếu ô chứa” như trong công thức phép toán đại diện SUMIF.

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

Để kiến thiết xây dựng tiêu chuẩn “ không bằng ”, hãy sử dụng phép toán logic “ < > ” .
Khi một giá trị, văn bản hoặc số, được sửa chữa thay thế bằng tiêu chuẩn, hãy nhớ đặt hàng loạt 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 ngoài 3 ngày, công thức như sau :
= SUMIF ( C2 : C10, “ < > 3 ”, B2 : B10 )
Để tìm tổng số toàn bộ các mẫu sản phẩm ngoại trừ Táo, công thức là :
= SUMIF ( A2 : A10, “ < > apples ”, B2 : B10 )
Khi giá trị tiêu chuẩn nằm trong một ô khác, hãy nối phép toán “ không bằng với ” và một tham chiếu ô như sau :
= SUMIF ( A2 : A10, “ < > ” và F1, B2 : B10 )
SUM IF not equal to

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 chuẩn. Thao tác này sẽ cộng toàn bộ các ô chứa bất kể thứ gì trong đó, gồm có cả các chuỗi có độ dài bằng không .
Ví dụ : đây là cách bạn hoàn toàn có thể tính tổng doanh thu bán hàng cho tổng thể các khu vực, tức là trong đó cột B không trống :
= SUMIF ( B2 : B10, “ < > ”, C2 : D10 )
SUM IF not blank

Ví dụ 5. SUMIF trống

Để tính tổng các ô trong 1 số ít 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 chuẩn 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 ô trọn vẹn trống .

Trong bảng mẫu của tất cả chúng ta, công thức sau sẽ tính tổng doanh thu cho các khu vực không xác lập, tức là ô trong cột B trống :
= SUMIF ( B2 : B10, “ ”, C2 : D10 )
SUM IF blank

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

Để tính tổng các ô dựa trên tác dụng khớp từng phần, gồm có một trong các ký tự đại diện thay mặt trong tiêu chuẩn của bạn :

  • Dấu chấm hỏi ( ? ) Để sửa chữa thay thế bất kể ký tự đơn nào .
  • Dấu hoa thị ( * ) để thay thế sửa chữa bất kể số lượng ký tự nào .

Ví dụ : công thức dưới đây sẽ chỉ tính tổng doanh thu cho khu vực phía Bắc :
= SUMIF ( B2 : B10, “ north ”, C2 : D10 )
Để tổng doanh thu bán hàng cho toàn bộ các khu vực, gồm có North, North-East và North-West, hay đặt dấy hoa thị ngay sau văn bản :
= SUMIF ( B2 : B10, “ north * ”, C2 : D10 )
Nếu bạn muốn nhập vùng chăm sóc vào một ô xác lập trước ( F1 ), sau đó nối một tham chiếu ô và một ký tự đại diện thay mặt được đặt trong dấu ngoặc kép :
= SUMIF ( B2 : B10, F1 và ” * ”, C2 : D10 )
SUMIF wildcard formula to sum if cell contains
Để khớp dấu chấm hỏi hoặc dấu hoa thị, hãy đặt dấu ngã ( ~ ) trước ký tự, ví dụ “ ~ ? ” hoặc “ ~ * ” .
Ví dụ : để tổng hợp doanh thu cho các khu vực được ghi lại *, hãy sử dụng “ * ~ * ” cho tiêu chuẩn. Trong trường hợp này, dấu hoa thị tiên phong là ký tự đại diện thay mặt và dấu hoa thị thứ hai là ký tự dấu hoa thị .
= SUMIF ( B2 : B10, “ * ~ * ”, C2 : D10 )
Nếu tiêu chuẩn ( * trong trường hợp này ) được cho là được nhập vào một ô riêng không liên quan gì đến nhau, thì hãy nối một dấu ngã và tham chiếu ô, như sau :
= SUMIF ( B2 : B10, “ * ” và ” ~ ” và F1, C2 : D10 )
SUMIF formula with an asterisk as criteria

Ví dụ 7: SUMIF trong Excel với ngày tháng

Việc sử dụng ngày tháng làm tiêu chuẩn SUMIF rất giống việc sử dụng số. Điều quan trọng nhất là thay thế sửa chữa định dạng ngày mà Excel hoàn toàn có thể hiểu được. Nếu bạn không chắc định dạng ngày nào được tương hỗ và định dạng ngày nào không, hàm DATE hoàn toàn có thể là một giải pháp .
Giả sử bạn đang tìm cách tính tổng doanh thu ngày 10/9/2020, tiêu chuẩn hoàn toàn có thể được thẻ hiện như sau :
= SUMIF ( C2 : C10, “ < 9/10/2020 ”, B2 : B10 ) hay = SUMIF ( C2 : C10, “ < “ và DATE ( 2020,9,10 ), B2 : B10 ) hay = SUMIF ( C2 : C10, “ < “ và F1, B2 : B10 ) Trong đó F1 là ngày tiềm năng . SUMIF using dates as criteria
Để tính tổng các ô dựa trên ngày ngày hôm nay, gồm có hàm TODAY trong tiêu chuẩn của bạn. Ví dụ : công thức dưới đây sẽ tính tổng doanh thu bán hàng với ngày giao hàng trước ngày ngày hôm nay :
= SUMIF ( C2 : C10, “ < “ và TODAY ( ), B2 : B10 ) Sum cells using today's date for criteria
Để tính tổng trong một chuỗi ngày, bạn cần xác lập riêng không liên quan gì đến nhau ngày nhỏ hơn và lớn hơn. Điều này có thế được thực thi với sự trợ giúp của hàm SUMIFS tương hỗ nhiều tiêu chuẩn .
Ví dụ : để tính tổng nếu một ngày nằm giữa hai ngày, thì đây là công thức để sử dụng :
= SUMIFS ( B2 : B10, C2 : C10, “ > = ” và F1, C2 : C10, “ < = ” và G1 ) SUMIF between two dates
Trong đó B2 : B10 là tổng khoanh vùng phạm vi, C2 : C10 là list ngày cần kiểm tra, F1 là ngày khởi đầu và G1 là ngày kết thúc .

Cách thực hiện SUMIF từ một sheet khác

Khi bạn cần tính tổng dữ liệu có điều kiện kèm theo từ một trang tính khác, chỉ cần cũng cấp tham chiếu bên ngoài cho các đối số SUMIF. Cách dễ nhất là khởi đầu nhập công thức, chuyển sang sheet khác và chọn vùng bằng chuột. Excel sẽ tự động hóa chèn tổng thể các tham chiếu mà bạn không cần phải lo ngại về cú pháp đúng .
Ví dụ : công thức dưới đây sẽ có các giá trị trong C2 : C10 trên Data sheet dựa trên tiêu chuẩn tròn B3 trên Sheet 1 :
= SUMIF ( Data ! B2 : B10, B3, Data ! C2 : C10 )
SUMIF from another sheet

Công thức SUMIF phân biệt chữ hoa chữ thường trong Excel

Như đã đề cập, về bản chất, hàm SUMIF trong Excel không phân biệt chữ hoa chữ thường. Để tính tổng các ô tính tổng các ô có điều kiện cần cân nhắc các từ viết hoa, bạn sẽ phải sử dụng một số hàm khác, cụ thể là hàm SUMPRODUCT và EXACT xử lý chữ hoa và chữ thường là ký tự khác nhau:

SUMPRODUCT ( – ( EXACT ( range, criteria ) ), sum_range )
Giả sử bạn có một list các mã loại sản phẩm trong cột A, nơi mà chữ hoa và chữ thường được xác lập 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 đơn cử, ví dụ điển hình A-01 .
Để hoàn tất, bạn hoàn toàn có thể nhập mục tiêu trực tiếp vào đối số thứ 2 của hàm EXACT :
= SUMPRODUCT ( – ( EXACT ( A2 : A10, “ A-01 ” ) ), B2 : B10 )
Hoặc nhập mã loại sản phẩm vào một ô riêng ( E1 ) như trong hình :
= SUMPRODUCT ( – ( EXACT ( A2 : A10, E1 ) ), B2 : B10 )
Case-sensitive SUMIF formula

Hàm này hoạt động như thế nào?

Hàm EXACT so sánh loại sản phẩm tiềm năng với từng loại sản phẩm trong list nguồn và trả về giá trị TRUE nếu tìm hiệu quả không khớp, nếu không thì là FALSE :
{ TRUE ; FALSE ; FALSE ; FALSE ; FALSE ; FALSE ; TRUE ; FALSE ; FALSE }
Toán tử đơn ngôi kép ( – ) quy đổi TRUE và FALSE thành 1 và 0, tương ứng :
{ 1 ; 0 ; 0 ; 0 ; 0 ; 0 ; 1 ; 0 ; 0 }
Hàm SUMPRODUCT nhân các thành phần của mảng trên với các mục tương ứng trong B2 : B10 :
SUMPRODUCT ( { 1 ; 0 ; 0 ; 0 ; 0 ; 0 ; 1 ; 0 ; 0 }, { 250 ; 155 ; 130 ; 255 ; 160 ; 280 ; 170 ; 285 ; 110 } )

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

SUMPRODUCT ( { 250 ; 0 ; 0 ; 0 ; 0 ; 0 ; 170 ; 0 ; 0 } )
Cuối cùng, SUMPRODUCT cộng các loại sản phẩm và xuất ra tổng .

Đó là cách sử dụng SUMIF trong Excel. Hy vọng rằng, các ví dụ về 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, 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!

Sửa Tủ Lạnh

Trung Tâm Bảo Hành

Liên kết:KQXSMB
Alternate Text Gọi ngay