Rod có một danh sách thiệp Giáng sinh trong Excel. (Trên thực tế, câu hỏi này có thể áp dụng cho bất kỳ danh sách thiệp chúc mừng nào.) Mỗi ​​tên có một ID số duy nhất được liên kết với nó và được mã hóa màu dựa trên việc tên đó là gia đình, bạn bè, bạn học hay bạn làm việc. Anh ta cũng có chỉ báo Đúng / Sai trong một cột cho biết liệu người đó có nhận được thẻ trong năm nay hay không. Rod muốn đếm xem có bao nhiêu thẻ phù hợp với hai tiêu chí:

chỉ báo “nhận thẻ” là Đúng và ID là 50 trở xuống.

Ngoài ra, anh ấy muốn có một cách để đếm số lượng thẻ trong đó chỉ báo “nhận thẻ” là Đúng và mã màu là màu đỏ.

Nếu bạn chỉ phải nhận các danh sách phụ như vậy theo định kỳ, giải pháp dễ dàng và linh hoạt nhất có thể là sử dụng khả năng lọc của Excel và sau đó sử dụng hàm SUBTOTAL để xác định số lượng. Bạn có thể lọc để hiển thị các hàng dựa trên hầu hết mọi tiêu chí bạn muốn. Khi danh sách đại diện cho những gì bạn muốn, thì bạn có thể sử dụng công thức như sau:

=SUBTOTAL(3,B2:B100)

Biến thể này trên hàm SUBTOTAL trả về tổng số tất cả các hàng được hiển thị trong phạm vi được chỉ định. Chìa khóa ở đây là các hàng duy nhất được hiển thị là những hàng đáp ứng tiêu chí lọc của bạn, vì vậy công thức trả về số lượng bạn muốn.

Cũng có những công thức sẽ trả về thông tin mong muốn. Hàm COUNTIFS sẽ xem xét nhiều tiêu chí và trả về số lượng dựa trên kết quả của các phép so sánh. Ví dụ: công thức sau sẽ cung cấp số lượng thành viên trong danh sách mà “lấy thẻ”

chỉ báo là Đúng và ID là 50 trở xuống:

=COUNTIFS(A2:A100,"<50",B2:B100,TRUE)

Công thức chỉ xem xét các thành viên danh sách trong A2: A100 và nó giả định rằng B2: B100 chứa các giá trị Đúng / Sai đóng vai trò là “thẻ nhận được”

chỉ báo.

Bạn nên nhớ rằng hàm COUNTIFS đã được giới thiệu bắt đầu từ Excel 2007. Nếu bạn đang tạo một sổ làm việc cũng sẽ được sử dụng bởi những người có phiên bản Excel cũ hơn, thì bạn có thể muốn sử dụng một hàm thay thế để trả về số:

=SUMPRODUCT((A2:A100<50)B2:B1001)

Nếu bạn muốn tính đến màu ô trong công thức của mình, thì nó sẽ phức tạp hơn một chút. Bạn có thể tạo ra một hàm do người dùng định nghĩa (một macro)

trả về màu ô và sau đó sử dụng màu đó trong công thức. (Các vấn đề khác của ExcelTips đã thảo luận về các hàm như vậy.) Một cách dễ dàng hơn để tránh macro là thêm một cột vào dữ liệu của bạn có chứa giá trị số cho biết cá nhân đó thuộc về nhóm nào (gia đình, bạn bè, bạn học hoặc bạn làm việc) . Sau đó, bạn có thể bao gồm giá trị cột trong công thức của mình.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (12867) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.