Excel SUMIFS: Chỉ tính tổng các giá trị đáp ứng nhiều tiêu chí

2024 Tác giả: Abigail Brown | [email protected]. Sửa đổi lần cuối: 2023-12-17 07:05

Điều cần biết

Nhập dữ liệu đầu vào > sử dụng cú pháp "=SUMIFS (Sum_range, Criteria_range1, Criteria1,…)".
Chức năng bắt đầu: Chọn ô mong muốn > chọn Formulastab > Math &Trig> SUMIFS.
Hoặc: Chọn ô mong muốn > chọn Chèn Hàm> Math &Trig> SUMIFSđể bắt đầu chức năng.

Bài viết này giải thích cách sử dụng hàm SUMIFS trong Excel 2019, 2016, 2013, 2010, Excel cho Microsoft 365, Excel 2019 cho Mac, Excel 2016 cho Mac, Excel cho Mac 2011 và Excel Online.

Nhập Dữ liệu Hướng dẫn

Bước đầu tiên để sử dụng hàm SUMIFS trong Excel là nhập dữ liệu.

Nhập dữ liệu vào các ô từ D1 đến F11của trang tính Excel, như trong hình trên.

Hàm SUMIFS và tiêu chí tìm kiếm (ít hơn 275 đơn đặt hàng và đại lý bán hàng từ khu vực bán hàng phía Đông) nằm ở hàng 12 bên dưới dữ liệu.

Hướng dẫn hướng dẫn không bao gồm các bước định dạng cho trang tính. Mặc dù định dạng sẽ không ảnh hưởng đến việc hoàn thành hướng dẫn, nhưng trang tính của bạn sẽ trông khác với ví dụ được hiển thị. Hàm SUMIFS sẽ cho bạn kết quả tương tự.

Cú pháp của hàm SUMIFS

Trong Excel, cú pháp của hàm đề cập đến bố cục của hàm và bao gồm tên, dấu ngoặc và đối số của hàm.

Cú pháp của hàm SUMIFS là:

=SUMIFS (Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2,…)

Có thể chỉ định tối đa 127 cặp Criteria_range / Criteria trong hàm.

Khởi động Hàm SUMIFS

Mặc dù có thể chỉ hàm SUMIFS trực tiếp vào một ô trong trang tính, nhưng nhiều người thấy việc sử dụng hộp thoại của hàm để nhập hàm dễ dàng hơn.

Nhấp vào ô F12 để biến nó thành ô hoạt động; F12 là nơi bạn sẽ nhập hàm SUMIFS.
Nhấp vào tab Công thức.
Nhấp vào Math & Trigtrong nhóm Thư viện Hàm.
Nhấp vào SUMIFStrong danh sách để hiển thị hộp thoại của hàm SUMIFS.

Excel Online không có tab Công thức. Để sử dụng SUMIFS trong Excel Online, hãy đi tới Chèn> Hàm.

Nhấp vào ô F12để đặt nó thành ô hoạt động để bạn có thể nhập hàm SUMIFS.
Nhấp vào nút Chèn Hàm. Hộp thoại Chèn Hàm mở ra.
Nhấp vào Math & Trigtrong danh sách Hạng mục.
Nhấp vào SUMIFStrong danh sách để bắt đầu chức năng.

Dữ liệu mà chúng ta nhập vào các dòng trống trong hộp thoại sẽ tạo thành các đối số của hàm SUMIFS.

Các đối số này cho hàm biết điều kiện nào chúng tôi đang kiểm tra và phạm vi dữ liệu sẽ tổng hợp khi nó đáp ứng các điều kiện đó.

Nhập Đối số Sum_range

Đối số Sum_rangechứa tham chiếu ô đến dữ liệu chúng ta muốn thêm vào.

Trong hướng dẫn này, dữ liệu cho đối số Sum_range nằm trong cột Tổng doanh số.

Các bước hướng dẫn

Nhấp vào dòng Sum_rangetrong hộp thoại.
Đánh dấu các ô từ F3 đến F9trong trang tính để thêm các tham chiếu ô này vào dòng Sum_range.

Nhập Criteria_range1 Đối số

Trong hướng dẫn này, chúng tôi đang cố gắng khớp hai tiêu chí trong mỗi bản ghi dữ liệu:

Đại lý bán hàng từ miền Đông bán hàng
Đại lý bán hàng có doanh số bán hàng ít hơn 275 trong năm nay

Đối số Criteria_range1 cho biết phạm vi ô mà SUMIFS sẽ tìm kiếm khi cố gắng khớp với tiêu chí đầu tiên: khu vực bán hàng phía Đông.

Các bước hướng dẫn

Nhấp vào dòng Criteria_range1trong hộp thoại.
Đánh dấu các ô D3 đến D9 trong trang tính để nhập các tham chiếu ô này làm phạm vi được hàm tìm kiếm.

Nhập Đối số Tiêu chí1

Tiêu chí đầu tiên chúng tôi đang tìm kiếm để đối sánh là nếu dữ liệu trong phạm vi D3: D9 bằng Đông.

Mặc dù dữ liệu thực tế, chẳng hạn như từ Đông, có thể được nhập vào hộp thoại cho đối số này, tốt nhất là thêm dữ liệu vào một ô trong trang tính và sau đó nhập tham chiếu ô đó vào hộp thoại.

Các bước hướng dẫn

Nhấp vào dòng Criteria1trong hộp thoại.
Nhấp vào ô D12để nhập tham chiếu ô đó. Hàm sẽ tìm kiếm dữ liệu phù hợp với các tiêu chí này trong phạm vi đã chọn ở bước trước.

Cách Tham chiếu Ô Tăng Tính linh hoạt của SUMIFS

Nếu một tham chiếu ô, chẳng hạn như D12, được nhập làm Đối số Tiêu chí, thì hàm SUMIFS sẽ tìm kiếm các kết quả phù hợp với bất kỳ dữ liệu nào có trong ô đó trong trang tính.

Vì vậy, sau khi tìm thấy số lượng bán hàng cho khu vực phía Đông, sẽ dễ dàng xác định cùng một dữ liệu cho khu vực bán hàng khác chỉ bằng cách thay đổi từ Đông sang Bắc hoặc Tây trong ô D12. Chức năng sẽ tự động cập nhật và hiển thị kết quả mới.

Nhập Đối số Criteria_range2

Đối số Criteria_range2 cho biết phạm vi ô mà SUMIFS sẽ tìm kiếm khi cố gắng khớp với tiêu chí thứ hai: đại lý bán hàng đã bán được ít hơn 275 đơn đặt hàng trong năm nay.

Nhấp vào dòng Criteria_range2trong hộp thoại.
Đánh dấu các ô từ E3 đến E9trong trang tính để nhập các tham chiếu ô này làm phạm vi thứ hai được hàm tìm kiếm.

Nhập Đối số Criteria2

Tiêu chuẩn thứ hai mà chúng tôi đang tìm kiếm để đối sánh là nếu dữ liệu trong phạm vi E3: E9 ít hơn 275 đơn đặt hàng bán hàng.

Cũng như đối số Criteria1, chúng tôi sẽ nhập tham chiếu ô đến vị trí của Criteria2 vào hộp thoại chứ không phải chính dữ liệu.

Nhấp vào dòng Criteria2trong hộp thoại.
Nhấp vào ô E12để nhập tham chiếu ô đó. Hàm sẽ tìm kiếm dữ liệu phù hợp với tiêu chí trong phạm vi đã chọn ở bước trước.
Nhấp vào OKđể hoàn thành chức năng SUMIFS và đóng hộp thoại.

Một câu trả lời bằng không (0) sẽ xuất hiện trong ô F12 (ô mà chúng tôi đã nhập hàm) vì chúng tôi chưa thêm dữ liệu vào các trường Criteria1 và Criteria2 (C12 và D12). Cho đến khi chúng tôi thực hiện, không có gì để hàm cộng lại và do đó, tổng số vẫn ở mức 0.

Thêm Tiêu chí Tìm kiếm và Hoàn thành Hướng dẫn

Bước cuối cùng trong hướng dẫn là thêm dữ liệu vào các ô trong trang tính được xác định là có chứa các đối số Tiêu chí.

Để được trợ giúp với ví dụ này, hãy xem hình ảnh ở trên.

Trong ô D12nhập Đôngvà nhấn phím Nhậptrên bàn phím.
Trong ô E12nhập <275và nhấn phím Entertrên bàn phím (phím "<" là ký hiệu cho ít hơn trong Excel).

Câu trả lời $ 119, 719,00 sẽ xuất hiện trong ô F12.

Chỉ có hai bản ghi, những bản ghi ở hàng 3 và 4 phù hợp với cả hai tiêu chí và do đó, chỉ tổng doanh số cho hai bản ghi đó mới được hàm tính tổng.

Tổng của $ 49, 017 và $ 70, 702 là $ 119, 719.

Khi bạn nhấp vào ô F12, hàm hoàn chỉnh=SUMIFS (F3: F9, D3: D9, D12, E3: E9, E12) xuất hiện trong thanh công thức phía trên trang tính.

Cách hoạt động của hàm SUMIFS

Thông thường, SUMIFS hoạt động với các hàng dữ liệu được gọi là bản ghi. Trong bản ghi, tất cả dữ liệu trong mỗi ô hoặc trường trong hàng đều có liên quan, chẳng hạn như tên, địa chỉ và số điện thoại của công ty.

Đối số SUMIFS tìm kiếm các tiêu chí cụ thể trong hai hoặc nhiều trường trong bản ghi và chỉ khi nó tìm thấy kết quả phù hợp cho từng trường được chỉ định thì dữ liệu cho bản ghi đó mới được tổng hợp.

Trong hướng dẫn từng bước về SUMIF, chúng tôi phù hợp với tiêu chí duy nhất của các đại lý bán hàng đã bán được hơn 250 đơn đặt hàng trong một năm.

Trong hướng dẫn này, chúng tôi sẽ đặt hai điều kiện bằng cách sử dụng SUMIFS: điều kiện của các đại lý bán hàng ở khu vực bán hàng phía Đông có doanh số bán hàng ít hơn 275 trong năm qua.

Có thể thực hiện việc đặt nhiều hơn hai điều kiện bằng cách chỉ định các đối số Criteria_range và Criteria bổ sung cho SUMIFS.

Đối số của Hàm SUMIFS

Các đối số của hàm cho biết điều kiện nào cần kiểm tra và phạm vi dữ liệu cần tính tổng khi nó đáp ứng các điều kiện đó.

Tất cả các đối số trong hàm này là bắt buộc.

Sum_range- dữ liệu trong phạm vi ô này được tính tổng khi tìm thấy kết quả khớp giữa tất cả các Tiêu chí được chỉ định và các đối số Criteria_range tương ứng của chúng.

Criteria_range- nhóm ô có chức năng tìm kiếm kết quả phù hợp với đối số Tiêu chí tương ứng.

Criteria- giá trị này được so sánh với dữ liệu trong tương ứng.

Criteria_range- dữ liệu thực tế hoặc tham chiếu ô đến dữ liệu cho đối số.