Bài 1 · Chương 8: Thống kê có điều kiện

COUNTIF và SUMIF: đếm và cộng theo một điều kiện

Dùng COUNTIF để đếm số hàng thỏa một điều kiện chữ hoặc số, dùng SUMIF để cộng cột tiền theo điều kiện ở cột khác, viết điều kiện so sánh như ">5000000" trong dấu nháy kép và phân biệt vùng điều kiện (range) với vùng cộng (sum_range).

15 phút2 bài tậpTrung cấp

Mục lục bài

Mục tiêu
Ví dụ có lời giải
Điền tiếp công thức
Tìm chỗ sai
Kiểm tra nhanh
Lỗi hay gặp
Trên Excel thật
Luyện tập (2 bài tập)

Ôn lại trước khi học

Thử nhớ lại trước, rồi mới xem đáp án.

Từ bài: Thiết lập trang in, in và xuất PDF
Bảng lương dài 3 trang nhưng trang 2 và trang 3 thiếu hàng tiêu đề. Dùng lệnh nào để hàng tiêu đề lặp lại ở mỗi trang in?
Xem đáp án
Dùng Print Titles ở thẻ Page Layout, rồi nhập địa chỉ hàng tiêu đề vào mục Rows to repeat at top.
Từ bài: Hàm IF: chọn một trong hai kết quả
Viết công thức ở D2: nếu Điểm TB ở C2 từ 5 trở lên thì hiện Đạt, ngược lại hiện Không đạt.
Xem đáp án
Công thức là =IF(C2>=5,"Đạt","Không đạt"). Dấu >= cho phép điểm bằng 5 vẫn đạt, và chữ phải nằm trong dấu nháy kép.
Từ bài: Địa chỉ tuyệt đối ($) và phím F4
Khi sao chép công thức xuống, vì sao vùng điều kiện nên viết $B$2:$B$9 thay vì B2:B9?
Xem đáp án
Địa chỉ có dấu $ giữ nguyên khi sao chép. Không có $ thì địa chỉ tương đối đổi theo từng hàng và vùng trượt xuống, nên các hàng dưới đọc sai vùng.

Mục tiêu

Sau bài này bạn có thể:

Viết COUNTIF để đếm số hàng thỏa một điều kiện chữ hoặc số.
Viết SUMIF để cộng cột tiền theo điều kiện ở một cột khác.
Viết điều kiện so sánh trong dấu nháy kép, ví dụ ">5000000".
Phân biệt vùng điều kiện (range) với vùng cộng (sum_range).

Ví dụ có lời giải

Sổ bán hàng ghi mỗi đơn của một chi nhánh. Thay vì lọc rồi đếm bằng mắt, bạn hỏi công thức: bao nhiêu đơn của chi nhánh Hà Nội? Với vùng chi nhánh B2:B9, câu trả lời là =COUNTIF(B2:B9,"Hà Nội").

Cú pháp=COUNTIF(range, criteria)

rangevùng ô cần kiểm tra điều kiện
Bắt buộc. Nhóm ô bạn muốn đếm.
criteriađiều kiện
Bắt buộc. Một số, biểu thức, địa chỉ ô hoặc chữ xác định ô nào được đếm. Điều kiện là chữ hoặc có dấu so sánh thì đặt trong dấu nháy kép; số và địa chỉ ô thì không.

Cú pháp=SUMIF(range, criteria, [sum_range])

rangevùng ô kiểm tra điều kiện
Bắt buộc. Vùng ô mà điều kiện được dùng để xét.
criteriađiều kiện
Bắt buộc. Điều kiện xác định ô nào được cộng.
sum_rangevùng ô cần cộngTùy chọn
Tùy chọn. Các ô thật sự được cộng, khi bạn muốn cộng ô khác với vùng điều kiện.

Dưới đây là sổ bán hàng tám đơn.

	A	B	C
1	Mã đơn	Chi nhánh	Giá trị
2	DH001	Hà Nội	4,200,000
3	DH002	Đà Nẵng	6,500,000
4	DH003	Hà Nội	7,800,000
5	DH004	Cần Thơ	3,100,000
6	DH005	Đà Nẵng	5,200,000
7	DH006	Hà Nội	5,000,000
8	DH007	Cần Thơ	9,000,000
9	DH008	Đà Nẵng	2,300,000

Đếm và cộng theo chi nhánh và theo giá trị

Làm gì: Đếm đơn của Hà Nội: gõ =COUNTIF(B2:B9,"Hà Nội"). Ô hiện 3.
Vì sao: Đối số thứ nhất là vùng cần kiểm tra, cột Chi nhánh. Đối số thứ hai là điều kiện, và chữ Hà Nội nằm trong dấu nháy kép. Ba đơn DH001, DH003 và DH006 thỏa điều kiện.
Chỗ hay sai
Làm gì: Đếm đơn có giá trị lớn hơn 5,000,000: gõ =COUNTIF(C2:C9,">5000000"). Ô hiện 4.
Vì sao: Dấu so sánh và số ghép thành một điều kiện nằm trong dấu nháy kép, và số gõ liền, không có dấu phân cách hàng nghìn. Dấu > loại đơn đúng 5,000,000 của DH006, nên có bốn đơn: 6,500,000, 7,800,000, 5,200,000 và 9,000,000.
Chỗ hay sai
Làm gì: Cộng giá trị của Hà Nội: gõ =SUMIF(B2:B9,"Hà Nội",C2:C9). Ô hiện 17,000,000.
Vì sao: Vùng điều kiện là cột Chi nhánh (B), còn vùng cộng là cột Giá trị (C). Hai vùng là hai đối số khác nhau và không được đảo chỗ. Cộng ba đơn của Hà Nội: 4,200,000 + 7,800,000 + 5,000,000 = 17,000,000.
Làm gì: Lấy điều kiện từ một ô: ghi tên chi nhánh ở E2 (Hà Nội), E3 (Đà Nẵng), E4 (Cần Thơ). Ở F2 gõ =SUMIF($B$2:$B$9,E2,$C$2:$C$9) rồi sao chép xuống F4.
Vì sao: Điều kiện là địa chỉ ô E2 nên mỗi hàng đọc một chi nhánh khác. Hai vùng có dấu $ để không trượt xuống khi sao chép. Kết quả: Hà Nội 17,000,000, Đà Nẵng 14,000,000, Cần Thơ 12,100,000.

Tự hỏi mình

Vì sao hai vùng trong công thức ở bước 4 cần dấu $, còn ô E2 thì không?
Nếu chi nhánh trong ô E2 có thêm một dấu cách ở cuối, kết quả sẽ ra sao, và bạn kiểm tra thế nào?

Lưu ý: điều kiện chữ của COUNTIF không phân biệt hoa thường, nên "hà nội" và "Hà Nội" đếm cùng các ô.

Điền tiếp công thức

Mỗi câu bỏ trống nhiều hơn câu trước. Dữ liệu vẫn là sổ bán hàng ở trên: chi nhánh ở B2:B9, giá trị ở C2:C9.

Câu 1. Đếm số đơn của chi nhánh Cần Thơ.

Câu 2. Cộng Giá trị của chi nhánh Đà Nẵng.

Câu 3. Đếm số đơn có Giá trị lớn hơn 5,000,000 (viết số liền khi đưa vào điều kiện).

Tìm chỗ sai

Bảng tổng hợp theo chi nhánh có kết quả sai từ hàng thứ hai trở đi: ô F2 đúng, nhưng F3 sai. Bấm vào phần bạn nghĩ là sai trong công thức ở F3.

Tìm chỗ sai

Công thức này có một chỗ sai. Bấm vào phần bạn nghĩ là sai.

Sau đây là phần luyện tập, gồm hai bài trên danh sách 41 nhân viên: đếm theo điều kiện chữ và điều kiện số, rồi cộng lương theo từng chi nhánh.

Đọc xong, giờ thử

Luyện tập ngay (2 bài tập)

Làm trực tiếp trên lưới trong trình duyệt. Mỗi lần bấm chấm bài, DeskLab kiểm tra kết quả và gợi ý nếu bạn chưa làm đúng.

Bắt đầu luyện tập

Chấm tự động ngay trên máy bạn. Không cần đăng nhập. Tiến độ lưu trên trình duyệt này.

Kiểm tra nhanh

Công thức nào cộng Giá trị (cột C) của các đơn thuộc chi nhánh Hà Nội (cột B)?

=SUMIF(B2:B9,"Hà Nội",C2:C9)=SUMIF(C2:C9,"Hà Nội",B2:B9)=SUMIF(B2:B9,C2:C9,"Hà Nội")

Vì sao điều kiện lớn hơn 5000000 phải viết là ">5000000" trong dấu nháy kép?

Vì điều kiện có dấu so sánh phải đặt trong dấu nháy képĐể Excel hiển thị số đẹp hơnĐể Excel chia số cho 1000

Bạn nhìn thấy năm hàng Hà Nội nhưng COUNTIF cho kết quả khác năm. Một nguyên nhân Microsoft nêu khi đếm giá trị chữ là gì?

Có ô chứa dấu cách thừa ở đầu hoặc cuối chữCOUNTIF chỉ đếm tối đa bốn ôCOUNTIF phân biệt chữ hoa và chữ thường

Lỗi hay gặp

Kết quả SUMIF sai vì đảo thứ tự hai vùng.
Nguyên nhân
Vùng điều kiện (range) và vùng cộng (sum_range) bị đặt nhầm chỗ.
Cách sửa
Đặt đúng thứ tự: =SUMIF(vùng_điều_kiện,điều_kiện,vùng_cộng). Ví dụ =SUMIF(B2:B9,"Hà Nội",C2:C9).
Điều kiện so sánh không được nhận hoặc cho kết quả sai.
Nguyên nhân
Điều kiện có dấu so sánh, như >5000000, không nằm trong dấu nháy kép.
Cách sửa
Đặt cả điều kiện trong dấu nháy kép: =COUNTIF(C2:C9,">5000000").
Sao chép công thức cho nhiều chi nhánh thì kết quả sai từ hàng thứ hai.
Nguyên nhân
Quên dấu $ ở vùng điều kiện hoặc vùng cộng, nên vùng trượt xuống khi sao chép.
Cách sửa
Cố định cả hai vùng: =SUMIF($B$2:$B$9,E2,$C$2:$C$9). Ô điều kiện E2 giữ nguyên dạng tương đối.
Tên chi nhánh nhìn giống nhau nhưng điều kiện "Hà Nội" không khớp một số hàng.
Nguyên nhân
Một số ô có dấu cách thừa ở đầu hoặc cuối chữ, và khi đó COUNTIF có thể trả về giá trị không mong đợi.
Cách sửa
Xóa dấu cách thừa trong dữ liệu rồi tính lại.

Trên Excel thật

Chữ hoa, chữ thường: Lưới DeskLab và Excel đều không phân biệt chữ hoa, chữ thường trong điều kiện của COUNTIF. Vì vậy "hà nội" và "Hà Nội" cho cùng kết quả.
Điều kiện số: Trong lưới, số tiền hiện dạng 12,000,000. Trong bài tập hãy gõ số liền như ">12000000", để dấu phẩy không bị nhầm với dấu ngăn cách đối số.