Khi làm việc, làm báo cáo trên Excel, chắc chắn các bạn sẽ cần tính toán với nhiều điều kiện của báo cáo, điều kiện của bài toán cần giải. Vậy trong bài viết này, huannghe.edu.vn sẽ hướng dẫn các bạn cách sử dụng hàm SUMIFS và COUNTIFS để tính toán với nhiều điều kiện trong Excel.
Đang xem: Hàm đếm tổng có điều kiện
Đăng ký học ngay khoá Tuyệt đỉnh Excel – Trở thành bậc thầy Excel trong 16 giờ
Cách sử dụng hàm SUMIFS, COUNTIFS và ví dụ cụ thể
Cách sử dụng hàm SUMIFS kèm ví dụ
Hàm SUMIFS là một trong các hàm Excel được sử dụng để tính tổng. Khác với hàm tính tổng SUM, hàm SUMIFS được sử dụng để tính tổng nhưng có nhiều điều kiện khác đi kèm. Đặc biệt khi các bạn làm báo cáo, bạn sẽ cần lọc các kết quả theo những điều kiện khác nhau để có thể tổng hợp được các số liệu chính xác nhất. Lúc này hàm SUMIFS sẽ hỗ trợ tốt nhất cho bạn.
Chúng ta sẽ có ví dụ sau để thực hành về cách hàm SUMIFS hoạt động và tính toán.
Trong ví dụ trên, chúng ta sẽ ứng dụng hàm SUMIFS để tính tổng số lượng của mặt hàng Cam do nhân viên Thảo bán được.
Như các bạn thấy, đề bài yêu cầu chúng ta tính tổng số lượng, nhưng kèm theo 2 điều kiện là mặt hàng “Cam” và nhân viên “Thảo”. Hàm tính tổng bình thường sẽ không thể giúp chúng ta xử lý yêu cầu này, vì vậy chúng ta cần dùng hàm tính tổng với nhiều điều kiện SUMIFS.
Hàm SUMIFS có cấu trúc như sau:
=SUMIFS(sum_range, criteria_range1, criteria1,
Trong đó các tham số của hàm có ý nghĩa như sau:
Cụ thể trong ví dụ trên:– Vùng tính tổng (sum_range): Là cột Số lượng, chúng ta sẽ cần tính tổng tại cột Số lượng- Vùng chứa điều kiện thứ 1 (criteria_range1): Là vùng chứa điều kiện thứ nhất, ở ví dụ này là cột Tên hàng. Các bạn lưu ý số dòng của các vùng điều kiện phải bằng số dòng của vùng tính tổng.- Điều kiện thứ 1 (criteria1): Điều kiện thứ 1, ở ví dụ trên điều kiện thứ nhất chính là mặt hàng “Cam”
Tương tự với điều kiện thứ 2, chúng ta có vùng điều kiện là cột Nhân viên và điều kiện là “Thảo”.Vì đây là hàm tính tổng theo nhiều điều kiện, nên tham số “Vùng tính tổng” và “Vùng điều kiện, điều kiện” bắt buộc phải có.
Như vậy chúng ta sẽ viết công thức hàm SUMIFS cho ví dụ này như sau:
=SUMIFS(H7:H13, F7:F13, K7, G7:G13, K8)
Trong công thức trên, các bạn có thể điền trực tiếp tên điều kiện vào tham số điều kiện, còn mình đang dùng tham chiếu ô để điền địa chỉ ô có chứa tên điều kiện của bài toán nhé.
Như vậy, kết quả chúng ta thu được là tổng Số lượng được lọc theo 2 điều kiện của bài toán yêu cầu là 61, với mặt hàng “Cam” và nhân viên “Thảo“.
Và trên đây chính là cách hàm SUMIFS tính toán dựa trên nhiều điều kiện
Cách sử dụng hàm COUNTIFS kèm ví dụ
Tiếp theo chúng ta sẽ đến với một hàm tính toán theo nhiều điều kiện khác, đó là hàm COUNTIFS, hàm đếm số lượng theo nhiều điều kiện.
Hàm COUNTIFS có cú pháp như sau:
=COUNTIFS(criteria_range1, criteria1,
Tương tự như cách hàm SUMIFS hoạt động, hàm COUNTIFS sẽ đếm số lượng dựa theo các điều kiện đi kèm. Khác với hàm SUMIFS, hàm COUNTIFS sẽ không có vùng tính tổng, vì vậy chúng ta không cần quan tâm đến vùng tính tổng, hàm sẽ đếm tất cả các giá trị thoả mãn các điều kiện đi kèm.
Cách viết điều kiện theo dạng số, ngày, text trong Excel khi sử dụng hàm SUMIFS, COUNTIFS
Trong công thức hàm SUMIFS và hàm COUNTIFS, các tham số vùng tính tổng và vùng điều kiện rất đơn giản và dễ xác định phải không? Bạn chỉ cần chọn toàn bộ vùng dữ liệu cần tính toán là được. Tuy nhiên tham số Điều kiện (criteria) lại không dễ dàng như vậy. Tuỳ vào yêu cầu của bài toán hay các bản báo cáo, điều kiện khi tính toán có thể là dạng số (number), nhưng đôi khi điều kiện đó lại là dạng ngày tháng (Date) hoặc dạng văn bản (Text). Vậy chúng ta sẽ làm thế nào để định dạng và viết đúng các tham số điều kiện theo từng dạng khác nhau?
Đây là một kiến thức quan trọng mà các bạn cần lưu ý, để khi chúng ta viết công thức kèm điều kiện chính xác, thì kết quả trả về mới có thể chính xác nhất được.
Điều kiện dạng số (Number) trong hàm SUMIFS, COUNTIFS
Đầu tiên với dạng số (Number), chúng ta sẽ có hai kiểu viết cho công thức hàm. Với kiểu thứ 1, chúng ta sẽ viết trực tiếp giá trị số vào tham số điều kiện của hàm. Đồng thời kèm theo dấu biểu hiện vùng của giá trị số đó. Ví dụ nếu điều kiện số bằng 5, bạn chỉ cần viết “5”, nếu điều kiện lớn hơn 5, bạn sẽ cần thêm dấu > vào điều kiện, các bạn có thể tham khảo dưới đây
Các bạn lưu ý các dấu toán tử này phải viết liền với giá trị điều kiện và đặt trong dấu ngoặc kép.
Cách thứ 2 là các bạn sẽ dùng tham chiếu ô, khi đó các dấu toán tử sẽ đặt trong ngoặc kép, tiếp theo là dấu & và địa chỉ ô tham chiếu của điều kiện số.
Điều kiện dạng ngày tháng (Date) trong hàm SUMIFS, COUNTIFS
Với điều kiện dạng ngày tháng (Date), chúng ta không có quy định rõ ràng cho cách viết trực tiếp, vì vậy để tránh sai sót, các bạn nên viết theo tham chiếu ô. Đó là dấu toán tử đặt trong dấu ngoặc kép, dấu & và nối với ô tham chiếu của ô chứa giá trị ngày tháng đó.
Điều kiện dạng văn bản (Text) trong hàm SUMIFS, COUNTIFS
Với điều kiện dạng văn bản, đặc biệt là tên riêng, các bạn có thể viết trực tiếp giá trị vào công thức. Trong trường hợp bạn chỉ muốn lấy tên, không quan tâm đến họ và tên đệm, bạn cần lưu ý thêm dấu sao * vào trước từ đó và dùng địa chỉ ô tham chiếu cho công thức hàm.
Tổng kết
Qua bài viết trên, chúng ta đã cùng nhau tìm hiểu cách hàm SUMIFS và hàm COUNTIFS hoạt động, cách sử dụng hai hàm này để tính toán qua những ví dụ cụ thể.
Chúc các bạn học thật tốt và ứng dụng hàm thành công trong công việc của mình. Đừng quên theo dõi chúng mình để đọc những bài viết bổ ích khác.
Hướng dẫn 5 cách tính tổng một cột trong ExcelHướng dẫn về hàm Hlookup trong excel, cú pháp và cách dùngHướng dẫn cách kết hợp hàm IF và hàm AND trong excelBộ đề thi, bài tập Excel có đáp án về hàm trong Excel quan trọngCÁCH TÍNH ĐIỂM TRUNG BÌNH VÀ XẾP LOẠI TRONG EXCEL (KÈM BÀI TẬP)