Cách sử dụng hàm SUMIF trong Excel và ví dụ cụ thể

Hàm SUMIF tính tổng các ô trong một phạm vi đáp ứng một điều kiện duy nhất, được gọi là tiêu chí. Hàm SUMIF là một hàm phổ biến, được sử dụng rộng rãi trong Excel và có thể được sử dụng để tính tổng các ô dựa trên ngày, giá trị văn bản và số. Lưu ý rằng SUMIF chỉ có thể áp dụng một điều kiện. Để tính tổng các ô bằng nhiều tiêu chí, hãy xem hàm SUMIFS

cú pháp

Cú pháp chung cho SUMIF trông như thế này

=SUMIF(range,criteria,[sum_range])

Hàm SUMIF có ba đối số. Đối số đầu tiên, phạm vi, là phạm vi ô để áp dụng tiêu chí cho. Đối số thứ hai, tiêu chí, là tiêu chí để áp dụng, cùng với bất kỳ toán tử logic nào. Đối số cuối cùng, sum_range, là phạm vi sẽ được tính tổng. Lưu ý rằng sum_range là tùy chọn. Nếu sum_range không được cung cấp, SUMIF sẽ tính tổng các ô trong đối số đầu tiên, phạm vi

ví dụ.   . . . . .  .  

Tiêu chí áp dụng

The SUMIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. The tricky part about using the SUMIF function is the syntax needed to apply criteria. This is because SUMIF is in a group of eight functions that split logical criteria into two parts, range and criteria. Because of this design, operators need to be enclosed in double quotes (""). The table below shows examples of the syntax needed for common criteria:

TargetCriteriaCells greater than 75">75"Cells equal to 100100 or "100"Cells less than or equal to 100"<=100"Cells equal to "Red""red"Cells not equal to "Red""<>red"Cells that are blank """"Cells that are not blank"<>"Cells that begin with "X""x*"Cells less than A1"<"&A1Cells less than today"<"&TODAY()

Lưu ý hai ví dụ cuối liên quan đến nối với ký tự dấu và (&). Bất cứ khi nào bạn đang sử dụng một giá trị từ một ô khác hoặc sử dụng kết quả của một công thức trong tiêu chí với toán tử logic như "<", bạn sẽ cần nối. Điều này là do Excel cần đánh giá các tham chiếu ô và công thức để nhận một giá trị trước khi giá trị đó có thể được nối với một toán tử

Hạn chế

Có một số hạn chế với SUMIF mà bạn nên biết. Đầu tiên, SUMIF chỉ hỗ trợ một điều kiện duy nhất. Nếu bạn cần tính tổng các ô bằng nhiều tiêu chí, hãy sử dụng hàm SUMIFS. Thứ hai, hàm SUMIF yêu cầu phạm vi thực cho đối số phạm vi; . Điều này có nghĩa là bạn không thể thực hiện những việc như trích xuất năm từ một phạm vi chứa ngày bên trong hàm SUMIF. Nếu bạn cần thao tác các giá trị xuất hiện trong đối số trước khi áp dụng tiêu chí, thì hàm SUMPRODUCT là một giải pháp linh hoạt

sử dụng cơ bản

Với các số trong phạm vi A1. A10, bạn có thể sử dụng SUMIF để tính tổng các ô lớn hơn 5 như thế này

=SUMIF(A1:A10,">5")

Nếu phạm vi B1. B10 chứa các tên màu như "đỏ", "xanh lam" và "xanh lục", bạn có thể sử dụng SUMIF để tính tổng các số trong A1. A10 khi màu trong B1. B10 là "đỏ" như thế này

=SUMIF(B1:B10,"red",A1:A10)

thông báo A1. A10 hiện được nhập dưới dạng dải_tổng, vì nó khác với dải chỉ chứa tên màu. Tóm lại. tiêu chí được áp dụng cho các ô trong phạm vi. Khi các ô trong phạm vi đáp ứng tiêu chí, các ô tương ứng trong sum_range được tính tổng. Đối số sum_range là tùy chọn. Nếu sum_range bị bỏ qua, thay vào đó, các ô trong phạm vi được tính tổng

Ví dụ về bảng tính

Trong trang tính được hiển thị, có ba công thức SUMIF. Trong công thức đầu tiên (G5), SUMIF trả về tổng Doanh số trong đó Tên = "jim". Trong công thức thứ hai (G6), SUMIF trả về tổng Doanh số trong đó Bang = "ca" (California). Trong công thức thứ ba (G7), SUMIF trả về tổng Doanh thu > 100

=SUMIF(B5:B15,"jim",D5:D15) // name = "jim"
=SUMIF(C5:C15,"ca",D5:D15) // state = "ca"
=SUMIF(D5:D15,">100") // sales > 100

Lưu ý không bắt buộc phải có dấu bằng (=) khi xây dựng tiêu chí "bằng". Ngoài ra, hãy lưu ý SUMIF không phân biệt chữ hoa chữ thường; . Cuối cùng, lưu ý rằng công thức cuối cùng không bao gồm sum_range, vì vậy thay vào đó, phạm vi được tính tổng.

Tiêu chí trong một ô khác

Một giá trị từ một ô khác có thể được đưa vào tiêu chí bằng cách sử dụng phép nối. Trong ví dụ bên dưới, SUMIF sẽ trả về tổng của tất cả doanh số trên giá trị trong G4. Lưu ý toán tử lớn hơn (>), là văn bản, phải được đặt trong dấu ngoặc kép. Công thức trong G5 là

=SUMIF(D5:D9,">"&G4) // sum if greater than G4

không bằng

Để thể hiện tiêu chí "không bằng", hãy sử dụng toán tử "<>" được bao quanh bởi dấu ngoặc kép ("")

=SUMIF(B5:B9,"<>red",C5:C9) // not equal to "red"
=SUMIF(B5:B9,"<>blue",C5:C9) // not equal to "blue"
=SUMIF(B5:B9,"<>"&E7,C5:C9) // not equal to E7

Một lần nữa thông báo SUMIF không phân biệt chữ hoa chữ thường

ô trống

SUMIF có thể tính tổng dựa trên các ô trống hoặc không trống. Trong ví dụ dưới đây,  SUMIF được dùng để tính tổng các số tiền trong cột C tùy thuộc vào việc cột D có chứa "x" hay trống

=SUMIF(D5:D9,"",C5:C9) // blank
=SUMIF(D5:D9,"<>",C5:C9) // not blank

ngày

Cách tốt nhất để sử dụng SUMIF với ngày là tham chiếu đến một ngày hợp lệ trong một ô khác hoặc sử dụng hàm DATE. Ví dụ dưới đây cho thấy cả hai phương pháp

=SUMIF(B5:B9,"<"&DATE(2019,3,1),C5:C9)
=SUMIF(B5:B9,">="&DATE(2019,4,1),C5:C9)
=SUMIF(B5:B9,">"&E9,C5:C9)

Lưu ý rằng chúng ta phải nối một toán tử với ngày trong E9. Để sử dụng tiêu chí ngày nâng cao hơn (i. e. tất cả các ngày trong một tháng nhất định hoặc tất cả các ngày giữa hai ngày), bạn sẽ muốn chuyển sang hàm SUMIFS, hàm này có thể xử lý nhiều tiêu chí

ký tự đại diện

Hàm SUMIF hỗ trợ ký tự đại diện, như trong ví dụ bên dưới

________số 8

Dấu ngã (~) là một ký tự thoát để cho phép bạn tìm các ký tự đại diện theo nghĩa đen. Ví dụ: để khớp với dấu chấm hỏi bằng chữ (?), dấu hoa thị (*) hoặc dấu ngã (~), hãy thêm dấu ngã vào trước ký tự đại diện (i. e. ~?, ~*, ~~)

Phạm vi trung bình thận trọng

SUMIF đưa ra một số giả định nhất định về kích thước của dải_tổng, về cơ bản là thay đổi kích thước của nó khi cần thiết để khớp với đối số dải, sử dụng ô phía trên bên trái trong dải làm gốc. Trong một số trường hợp, hành vi này có thể tạo ra một kết quả có vẻ hợp lý nhưng thực tế lại không chính xác. Để biết ví dụ về vấn đề này, hãy xem bài viết này

Làm cách nào để sử dụng Sumif cho văn bản cụ thể?

Bạn có thể sử dụng Sumifs với một tiêu chí không?

Làm thế nào để bạn tính tổng của một mục cụ thể trong Excel?

Làm cách nào để thực hiện Sumif với nhiều điều kiện?