Khi làm báo cáo, kế hoạch đầu tư hoặc bất kỳ tập dữ liệu nào khác có ngày tháng, bạn thường phải tính tổng các số trong một khoảng thời gian nhất định. Học Excel Online sẽ hướng dẫn này sẽ chỉ cho bạn một giải pháp nhanh chóng và dễ dàng – công thức SUMIFS với ngày làm tiêu chí.

Trong bài viết này chúng ta xem qua cách sử dụng hàm SUMIF lấy ngày làm tiêu chí. Vấn đề là để tính tổng giá trị giữa hai ngày, bạn cần phải xác định cả hai ngày trong khi hàm SUMIF trong Excel chỉ cho phép một điều kiện. Thật may mắn khi có hàm SUMIFS hỗ trợ được nhiều tiêu chí.

Làm thế nào để tính tổng các giá trị giữa hai ngày trong Excel 

Để tính tổng các giá trị trong một chuỗi ngày nhất định, hãy sử dụng hàm SUMIFS với ngày bắt đầu và ngày kết thúc làm tiêu chí. Cú pháp của hàm SUMIFS yêu cầu trước tiên bạn chỉ định các giá trị cần cộng (sum_range) sau đó cung cấp các cặp phạm vi/ tiêu chí. Trong trường hợp này, phạm vi (danh sách các ngày) sẽ giống nhau cho cả hai tiêu chí.

Xét ở trên, các công thức chung để tính tổng các giá trị giữa hai ngày có dạng sau:

Bao gồm các ngày ràng buộc:

SUMIFS(sum_range, dates,”>=”&start_date, dates, “<=”&end_date)

Không bao gồm các ngày ràng buộc:

SUMIFS(sum_range, dates,”>”&start_date, dates, “<“&end_date)

Như có thể thấy, sự khác biệt chỉ là ở các phép toán logic. Trong công thức đầu tiên, chúng ta sử dụng lớn hơn hoặc bằng (>=) và nhỏ hơn hoặc bằng (<=) để bao gồm các giới hạn dưới và trên trong kết quả. Công thức thứ hai kiểm tra xem nếu một ngày lớn hơn (>) hay nhỏ hơn (<) thì loại bỏ ngày bắt đầu và kết thúc.

Trong bảng bên dưới, giả sử bạn muốn tổng hợp các dự án đến hạn trong một chuỗi ngày cụ thể. Để hoàn tất, chúng ta nhập ngày bắt đầu G1 và sử dụng công thức sau để cộng ngân sách trong B2:B10 giữa hai ngày này, bao gồm:

=SUMIFS(B2:B10, C2:C10, “>=”&F1, C2:C10, “<=”&G1)

Nếu bạn muốn thay ngày trong công thức, thì hãy nhập ngày ngay sau phép toán logic và đặt toàn bộ tiêu chí trong dấu ngoặc kép như sau:

=SUMIFS(B2:B10, C2:C10, “>=9/10/2020”, C2:C10, “<=9/20/2020”)

Để tránh những sai lầm có thể xảy ra, bạn có thể định dạng ngày tháng bằng chức năng DATE:

=SUMIFS(B2:B10, C2:C10, “>=”&DATE(2020,9,10), C2:C10, “<=”&DATE(2020,9,20))

Tính tổng dynamic range dựa trên ngày hôm nay

Trong trường hợp khi bạn cần tính tổng dữ liệu với dynamic range (X ngày trở về trước hoặc Y ngày trở về sau), xây dựng tiêu chí bằng cách sử dụng hàm TODAY, hàm này sẽ tự động cập nhật ngày hiện tại.

Ví dụ:  tính tổng ngân sách đến hạn trong 7 ngày qua bao gồm ngày hôm nay, hàm là:

=SUMIFS(B2:B10, C2:C10, “<=”&TODAY(), C2:C10, “>”&TODAY()-7)

Nếu bạn không muốn tính ngày hiện tại trong kết quả cuối cùng, hãy sử dụng phép toán logic nhỏ hơn (<) cho tiêu chí đầu tiên để loại trừ ngày hôm nay và lớn hơn hoặc bằng (>=) cho tiêu chí thứ hai để bao gồm 7 ngày trước đó:

=SUMIFS(B2:B10, C2:C10, “<“&TODAY(), C2:C10, “>=”&TODAY()-7)

Theo cách tương tự, bạn có thể tính tổng các giá trị nếu là một ngày nhất định về sau. 

Ví dụ: Để nhận được tổng số ngân sách đến hạn trong 3 ngày tới, hãy sử dụng một trong các công thức sau:

Ngày hôm nay được bao gồm trong kết quả:

=SUMIFS(B2:B10, C2:C10, “>=”&TODAY(), C2:C10, “<“&TODAY()+3)

Ngày hôm nay không được bao gồm trong kết quả:

=SUMIFS(B2:B10, C2:C10, “>”&TODAY(), C2:C10, “<=”&TODAY()+3)

Tính tổng các giá trị giữa hai ngày và một tiêu chí khác

Để tính tổng các giá trị trong phạm vi ngày được yêu cầu và đáp ứng một số điều kiện trong một cột khác, chỉ cần thêm một phạm vi/ tiêu chí nữa vào công thức SUMIFS:

Ví dụ: Để tổng hợp ngân sách trong một chuỗi ngày nhất định cho tất cả dự án có chứa “tip”, hãy mở rộng công thức với tiêu chí ký tự đại diện:

=SUMIFS(B2:B10, C2:C10, “>=”&F1, C2:C10, “<=”&G1, A2:A10, “tip*”)

Trong đó A2:A10 là tên dự án, B2:B10 là số tổng, C2:C10 là ngày cần kiểm tra, F1 là ngày bắt đầu và G1 là ngày kết thúc.

Tất nhiên, không có gì khó khăn khi bạn nhập tiêu chí thứ ba vào một ô riêng biệt và tham chiếu ô đó như trong hình minh họa:

Cú pháp hàm SUMIFS lấy tiêu chí ngày

Khi nói đến việc sử dụng ngày tháng làm tiêu chí cho các hàm SUMIF và SUMIFS trong Excel, bạn sẽ không phải là người đầu tiên bối rối.

Tuy nhiên, khi xem xét kỹ hơn, tất cả các trường hợp sử dụng đa dạng đều tóm gọn lại một vài quy tắc đơn giản:

Nếu bạn đặt ngày trực tiếp trong tham số tiêu chí, thì hãy nhập phép toán logic  (>, <, =, <>) ngay trước ngày và đặt toàn bộ tiêu chí trong dấu ngoặc kép. Ví dụ:

=SUMIFS(B2:B10, C2:C10, “>=9/10/2020”, C2:C10, “<=9/20/2020”)

Khi ngày tháng được nhập vào ô xác định trước, hãy cung cấp tiêu chí ở dạng chuỗi văn bản: đặt phép toán logic trong dấu ngoặc kép để bắt đầu một chuỗi và sử dụng dấu và (&) để nối và kết thúc chuỗi. Ví dụ:

=SUMIFS(B2:B10, C2:C10, “>=”&F1, C2:C10, “<=”&G1)

Khi ngày được định dạng bởi hàm DATE hoặc TODAY(), hãy nối một phép toán so sánh và một hàm. Ví dụ:

=SUMIFS(B2:B10, C2:C10, “>=”&DATE(2020,9,10), C2:C10, “<=”&TODAY())

Hàm SUMIFS trong Excel giữa các ngày không hoạt động

Trong trường hợp công thức của bnaj không hoạt động hoặc tạo ra kết quả sai, các mẹo khắc phục sự cố sau đây có thể làm sáng tỏ lý do tại sao nó không thành công và giúp bạn khắc phục sự cố.

Kiểm tra định dạng ngày và số 

Nếu công thức SUMIFS có vẻ đúng không trả về giá trị nào ngoài số 0, thì điều đầu tiên cần kiểm tra lsf ngày tháng của bạn có thực sự là ngày tháng, chứ không phải là chuỗi văn bản giống ngày tháng. Tiếp theo, hãy chắc chắn rằng bạn đang tính tổng các số chứ không phải các số được lưu trữ dưới dạng văn bản. Các hướng dẫn sau đây sẽ giúp bạn phát hiện và khắc phục những vấn đề này.

• Cách thay đổi “ngày văn bản” thành ngày thực
• Cách chuyển đổi văn bản thành số

Sử dụng cú pháp chính xác cho tiêu chí

Khi kiểm tra ngày bằng cách sử dụng SUMIFS, một ngày nên được đặt trong dấu ngoặc kép như “>=9/10/2020”; tham chiếu ô và chức năng phải được đặt bên ngoài dấu ngoặc kép như “<=”&G1 hay “<=”&TODAY(). Để biết thêm chi tiết đầy đủ, vui lòng cú pháp tiêu chí ngày.

Xác minh tính logic của hàm 

Một lỗi đánh máy nhỏ trong ngân sách có thể tiêu tốn hàng triệu USD. Một cú pháp sai sót trong công thức có thể tốn hàng giờ để giải quyết. Vì vậy, khi tính tổng giá trị giữa 2 ngày, hãy kiểm tra xem ngày bắt đầu có đứng trước phép toán logic lớn hơn (>) hoặc lớn hơn hoặc bằng (>=) hay không; và ngày kết thúc được bắt đầu bằng phép toán logic nhỏ hơn (<) hoặc nhỏ hơn hoặc bằng (<=). 

Đảm bảo rằng tất cả các phạm vi đều có cùng kích thước

Để hàm SUMIFS hoạt động chính xác, phạm vi tính tổng và phạm vi tiêu chí phải có kích thước bằng nhau, nếu không thì lỗi #VALUE! Lỗi xảy ra. Để khắc phục, hãy đảm bảo rằng tất cả các tham số criteria_range có cùng số hàng và cột như sum_range.

Đó là cách sử dụng hàm SUMIFS trong Excel để tính tổng dữ liệu trong một chuỗi ngày. Nếu bạn có một số giải pháp nào thú vị khác, hãy chia sẻ với trong nhận xét. Cảm ơn bạn đã đọc và hy vọng sẽ gặp lại bạn trong tuần tới.