Cách Dùng Hàm Sumif Trong Excel

Trong bài viết này, Học Excel Online sẽ giải thích và cung cấp một số ví dụ công thức cho dữ liệu dạng số, văn bản, ngày tháng và những kí tự đại diện.

Nếu bạn đang phải đối mặt với nhiệm vụ tính tổng các dữ liệu thỏa điều kiện trong Excel, thì hàm là những gì mà bạn cần. Hướng dẫn này sẽ giải thích ngắn gọn về cú pháp của hàm và cách sử dụng chung, và tiếp đó bạn sẽ bổ sung thêm kiến thức trong việc thực hành với một số ví dụ công thức của hàm .

Hàm SUMIF, còn được gọi là tính tổng có điều kiện của Excel, được sử dụng để thêm các ô, dựa trên một điều kiện hoặc tiêu chí nhất định.

Nếu bạn đã đọc hướng dẫn về hàm trên trang blog này, thì bạn sẽ không gặp bất kỳ khó khăn nào khi hiểu hàm SUMIF, bởi cú pháp và cách sử dụng của nó cũng tương tự. Do đó, cú pháp của hàm SUMIF như sau:

Như bạn thấy, hàm SUMIF có 3 đối số - 2 đối số đầu là bắt buộc, còn đối số thứ 3 là tùy chọn.

1. - dải của các ô được đánh giá theo tiêu chí của bạn, ví dụ A1: A10.
2. - điều kiện/tiêu chí cần phải đáp ứng. Các tiêu chí có thể được cung cấp dưới dạng số, văn bản, ngày, biểu thức logic, tham chiếu ô, hoặc một hàm Excel khác. Ví dụ: bạn có thể nhập các tiêu chí như "5", "", "10/25/2014", "<5", "A1", v.v ...

Lưu ý: Hãy chú ý rằng bất kỳ điều kiện dạng văn bản hoặc điều kiện có chứa các ký hiệu toán học thì đều phải được đặt trong dấu nháy kép (""). Đối với các điều kiện số, dấu nháy kép là không bắt buộc.

- các ô dùng để tính tổng, nếu điều kiện được đáp ứng. Đối số này là tùy bạn chọn, và bạn chỉ cần sử dụng nó nếu bạn muốn tính tổng các ô khác (mà đã được định nghĩa trong đối số range). Nếu đối số sum_range bị bỏ qua, khi đó Excel sẽ tính tổng các ô tương tự mà các tiêu chí được đặt ra (nghĩa là chúng đã được chỉ định trong đối số range).

• range-A2: A8
• criteria - "bananas"
• sum_range - C2: C8

Đặt nó lại với nhau, và bạn nhận được công thức: = SUMIF (A2: A8, "bananas", C2: C8)

: Tham số sum_range thực sự chỉ xác định ô trên cùng bên trái của dải được tính tổng. Khu vực còn lại thì lại được xác định bởi kích thước của đối số range.

Trong thực tế, điều này có nghĩa là đối số sum_range không nhất thiết phải có cùng kích thước với đối số dải, nghĩa là nó có thể khác nhau về số lượng hàng và cột. Tuy nhiên, ô đầu tiên (tức là ô trên cùng bên trái) của dải tính tổng phải luôn luôn ở đúng cột. Ví dụ, trong công thức SUMIF ở trên, bạn có thể lấy ô C2, hoặc C2: C4, hay thậm chí là C2: C100 làm đối số , và kết quả vẫn sẽ như cũ. Tuy nhiên, hướng đi tốt nhất vẫn là cung cấp và có cùng k&# 237;ch thước.

với dấu lớn hơn, nhỏ hơn hoặc bằng:

Hãy nhìn vào một số công thức mà bạn có thể sử dụng để thêm vào các giá trị lớn hơn, nhỏ hơn hoặc bằng một giá trị nhất định.

Lưu ý: Trong các công thức hàm , một dấu so sánh theo sau bởi một số hoặc một văn bản phải luôn luôn được đặt trong dấu nháy kép ("").

Cách sử dụng Hàm SUMIF với tiêu chí văn bản

Ngoài các con số, hàm SUMIF cho phép bạn thêm các giá trị mà có sự phụ thuộc vào việc liệu một ô tương ứng trong một cột khác có thể chứa một văn bản cho trước hay không.

Để biết thêm thông tin về giá trị khớp 1 phần, vui lòng xem ví dụ về hàm SUMIF với các kí tự đại diện.

Và bây giờ, hãy xem công thức chính xác "Tính tổng nếu khác" qua các thao tác. Như được minh họa trong ảnh chụp màn hình bên dưới, nó cho biết lượng tồn kho của tất cả các sản phẩm khác ngoài "goldfinger bananas":

Sử dụng dấu so sánh với tham chiếu ô

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 bạn mục đích của bạn là việc tính tổng có điều kiện các ô dựa trên tiêu chí "văn bản" và bạn muốn tính tổng bằng cách đối chiếu từng phần, thì bạn cần phải sử dụng các kí tự đại diện trong công thức SUMIF của bạn.

Hãy xem xét các kí tự đại diện sau đây:

• Dấu hoa thị (*) - đại diện cho nhiều kí tự
• Dấu hỏi (?) - đại diện cho một kí tự duy nhất ở một vị trí cụ thể

Ví dụ 1. Các giá trị tính tổng dựa trên đối chiếu từng phần:

= SUMIF (A2: A8, "bananas *", C2: C8) - tiêu chí bao gồm các văn bản kết thúc với dấu hoa thị (*).

Nếu bạn chỉ muốn tính tổng những ô bắt đầu hoặc kết thúc với một văn bản nhất định, thì bạn chỉ cần thêm một dấu * trước hoặc sau văn bản:

- tổng giá trị trong C2: C8 nếu một ô tương ứng trong cột A bắt đầu bằng từ "bananas".

) - 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ừ "bananas".

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

Trong trường hợp bạn muốn tính tổng một số giá trị nếu giá trị này có chính xác 6 chữ cái, thì bạn sẽ sử dụng công thức sau:

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

Ví dụ 3. Tính tổng các ô có giá trị tương ứng với giá trị văn bản

Nếu bảng tính của bạn chứa các loại dữ liệu khác nhau và bạn chỉ muốn tổng các ô tương ứng với giá trị văn bản, thì các công thức SUMIF sau sẽ có ích cho bạn:

= SUMIF (A2: A8, "? *", C2: C8) - cộng giá trị trong các ô C2: C8 nếu ô tương ứng trong cột A chứa ít nhất 1 kí tự.

= SUMIF (A2: A8, "*", C2: C8) - bao gồm các ô có vẻ là rỗng, chứa các chuỗi có độ dài bằng 0 (là kết quả của các công thức khác), ví dụ: = "".

Cả hai công thức trên đều bỏ qua các giá trị không phải là văn bản như các lỗi, các phép toán luận, các chữ số và ngày tháng.

Ví dụ 4. Sử dụng * hoặc ? như những kí tự bình thường

Nếu bạn muốn * hoặc ? được coi như là một chữ chứ không phải là một kí tự đại diện, thì hãy sử dụng dấu ngã trước đó (~). Ví dụ: công thức SUMIF sau sẽ thêm các giá trị trong các ô C2: C8 nếu một ô trong cột A nằm trong cùng một hàng mà chứa một dấu chấm hỏi:

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 dải, hãy sử dụng hàm SUM cùng với hàm

Ví dụ 1. Thêm một vài số lớn nhất / nhỏ nhất

Nếu bạn muốn tính tổng chỉ một số ít, ví dụ như 5 số, thì bạn có thể gõ chúng trực tiếp trong công thức, như sau:

- tổng 5 số lớn nhất

Chú thích. Nếu có 2 hoặc nhiều hơn số được buộc cho vị trí cuối cùng, chỉ có số đầu tiên xuất hiện sẽ được tính. Như bạn thấy, số 9 thứ hai không được thêm vào trong ví dụ trên.

Ví dụ 2. Thêm vào các giá trị lớn/nhỏ

Nếu bạn muốn thêm nhiều số, thay vì liệt kê tất cả trong công thức, bạn có thể tổ chức các hàm ROW và hàm INDIRECT trong công thức SUM của bạn. Trong hàm INDIRECT, hãy sử dụng số hàng đại diện cho số giá trị bạn muốn thêm vào. Ví dụ, các công thức sau đây tính tổng 15 số lớn nhất và nhỏ nhất, lần lượt là:

Ví dụ 3. Tính tổng một biến số trong các giá trị lớn nhất / nhỏ nhất

Nếu bạn không muốn thay đổi công thức mỗi khi bạn muốn tính tổng một số ô khác nhau, bạn có thể đặt số đó trong một ô nào đó thay vì nhập nó vào công thức. Và sau đó, bạn có thể sử dụng hàm ROW và INDIRECT như đã trình bày ở trên và tham chiếu một ô chứa biến, như trong trường hợp này là ô E1:

- tính tổng các giá trị hàng đầu mà có chứa 1 ô thay đổi

Hãy nhớ rằng, đây là các công thức mảng, vì vậy bạn phải nhấn Ctrl + Shift + Enter để hoàn thành chúng.

Làm thế nào để tính tổng các ô tương ứng với các ô trống

Nếu "trống" thì có nghĩa là các ô đó hoàn toàn không có chứa gì - không có công thức, không có chuỗi chiều dài bằng 0 được trả lại bởi một số hàm Excel khác, khi đó bạn có thể sử dụng "=" làm tiêu chí, như trong công thức SUMIF sau:

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

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

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

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 trên tính tổng các giá trị tương ứng với tất cả các ô không rỗng, bao gồm các chuỗi có độ dài bằng không.

Cách sử dụng Hàm SUMIF với ngày tháng

Nói chung, bạn sử dụng hàm SUMIF để tính tổng một cách có điều kiện các giá trị dựa trên ngày cũng giống như cách bạn sử dụng văn bản và các tiêu chí số.

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