Hàm Sumifs Điều Kiện Ngày Tháng Và Cách Xử Lý Đơn Giản

Hàm sumifs là hàm tính tổng nhiều điều kiện, trong các dạng điều kiện của nó có dạng điều kiện ngày tháng mà mọi người khi làm hay gặp khó khăn nhất. Bài viết này Học Office sẽ hướng dẫn các bạn xử lý dạng điều kiện này một cách chi tiết và dễ hiểu nhất.

Xem thêm:

• Hàm Sumifs: Cách Dùng Hàm Sumifs Trong Excel Hiệu Quả Và Các Ví Dụ
• Sự Khác Biệt Giữa Hàm Sumif Và Hàm Sumifs Qua Các Ví Dụ Cụ Thể

Trước tiên chúng ta cần phải nắm rõ được các khái niệm căn bản của hàm sumifs và các công thức của hàm thời gian ngày tháng năm. Công thức của hàm thời gian như day, month, year, date,…

Hàm Sumifs là gì?

Hàm sumifs là hàm tính tổng một phạm vi theo hàng cột hoặc hàng ngang kèm theo 2 hoặc nhiều hơn 2 điều kiện. Hàm Sum là hàm tính tổng, hàm if là hàm điều kiện và ghép 2 hàm này lại với “s” sẽ được hàm SUMIFS.

Công thức hàm SUMIFS

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,…)

Trong đó:

• Sum_range: Vùng tính tổng theo phạm vi cột hoặc hàng
• Criteria_range1: Vùng điều kiện 1 theo phạm vi cột hoặc hàng
• Criteria1: Điều kiện 1
• Criteria_range2: Vùng điều kiện 2 theo phạm vi cột hoặc hàng
• Criteria2: Điều kiện 2

Hàm Sumifs điều kiện ngày tháng là gì?

Điều kiện ngày tháng hay điều kiện thời gian nói chung trong hàm Sumifs là những điều kiện so sánh bình thường sử dụng các toán tử “>“, “<“, “=” hoặc “<>” đối với một hoặc nhiều mốc thời gian. Thường điều kiện ngày tháng có thể là:

• Trước hoặc sau một mốc thời gian: Điều kiện này có thể kết hợp với một điều kiện khác không phải điều kiện ngày tháng
• Trong một khoảng thời gian: Cần lặp lại 2 lần vùng điều kiện, một điều kiện lớn hơn và một điều kiện nhỏ hơn
• Theo quý, tháng, tuần, thứ: Dạng bài này cần phải thêm cột phụ rồi mới tính tổng được. Vì hàm sumifs là hàm dạng AND.

Trên đây là 3 trường hợp theo điều kiện ngày tháng hàm sumifs, chúng ta sẽ đi từng trường hợp để giải quyết nhé.

Ví dụ và cách xử lý hàm Sumifs có điều kiện ngày tháng

Các bạn có thể tải ví dụ về để thực hành trong quá trình đọc bài viết:

Link tải Google Drive: Bài tập hàm sumifs điều kiện ngày tháng

Trước hoặc sau một mốc thời gian

Trong trường hợp này nếu chỉ có một điều kiện là mốc thời gian thì chúng ta nên dùng hàm sumif. Còn nếu yêu cầu là hai điều kiện thì chúng ta sử dụng hàm sumifs.

Ví dụ 1: Tính tổng thành tiền của những mặt hàng sau ngày mùng 2 tháng 9

Bài tập dạng đầu tiên này khá đơn giản, bạn chỉ cần xác định mốc thời gian đó rồi cho toán tử vào so sánh. Bạn hãy nhập công thức như phía dưới của mình, không cần phải cho vào hàm date. Câu một do chỉ có một điều kiện nên mình sẽ để là hàm sumif.

Công thức của câu này là:

=SUMIF(D5:D11,”>2021/09/02″,H5:H11)

Ngày tháng các bạn cho vào bên trong dấu ngoặc kép, các thành phần bên trong hàm sumif các bạn cũng nên cố định công thức bằng F4. Và kết quả sẽ là: 608800.

Ví dụ 2: Tính tổng thành tiền mà năm của ngày nhập hàng là 2021 và có số lượng lớn hơn 50.

Ví dụ này nâng cao hơn một chút, các bạn sẽ phải tạo thêm một cột phụ là cột năm bằng cách dùng hàm Year để lấy năm từ cột “Ngày nhập hàng“.

Sau khi thêm cột năm xong thì bài toán đã quay trở về ví dụ 1, khác một chút là nó có thêm một điều kiện số lượng lớn hơn 50. Công thức sẽ như sau:

=SUMIFS(J4:J10,F4:F10,”=2021″,H4:H10,”>50″)

Đối với định dạng Number các bạn bắt buộc khi cho vào điều kiện phải cho trong dấu ngoặc kép nếu không sẽ bị lỗi. Và kết quả là: 755000.

Ví dụ này có một cách khác khá hay đó là có thể dùng hàm mảng, các bạn có thể tham khảo cách dưới đây:

=SUM(IF((YEAR(E4:E10)=2021)*(H4:H10>50),J4:J10))

Giải thích một chút về hàm mảng này:

Hàm If phía bên trong sẽ có 2 điều kiện là Year(E4:E10)=2021 và H4:H10>50 nối với nhau bởi dấu * hay AND trong công thức bình thường. Sau khi 2 điều kiện này chạy sẽ trả về các kết quả TRUE, sau đó những kết quả TRUE này sẽ nhân với J4:J10.

Hàm Sum làm nốt công việc còn lại là cộng những kết quả nhân với J4:J10.

Các bạn nhớ điền công thức xong hãy bấm tổ hợp phím Ctrl + Shift + Enter vì là công thức mảng. Các bạn muốn hiểu hơn về hàm mảng hoặc những hàm khác nhau trong Excel có thể tham giá khóa học Excel online của mình hoặc đọc các bài viết miễn phí tại Excel cơ bản nhé.

Trong một khoảng thời gian

Ví dụ 3: Tính tổng thành tiền các mặt hàng trong khoảng ngày từ 01/10/2021 – 31/10/2021

Dạng bài này chỉ thêm một điều kiện so với dạng bài thứ nhất, lặp lại hai lần vùng điều kiện, cú pháp cũng giống như dạng bài thứ nhất. Các phần tử của điều kiện bao gồm toán tử và ngày tháng đều cho vào trong ngoặc kép và sau đó lặp lại vùng điều kiện rồi thêm một điều kiện ngày tháng tiếp.

Ở công thức này sẽ dùng hàm sumifs điều kiện ngày tháng vì có 2 điều kiện từ ngày này đến ngày kia. Và kết quả là: 150000.

Theo quý, tháng, tuần, thứ

Ví dụ 4: Tính tổng thành tiền theo quý 2 và có đơn giá nhỏ hơn 6000

Dạng bài này mình sẽ phải tạo thêm cột phú là cột quý để kết hợp với điều kiện đơn giá nhỏ hơn 6000. Câu ví dụ này các bạn có thể sử dụng hàm mảng để làm và nhớ ấn tổ hợp phím ctrl + shift + Enter cuối hàm nhé.

Các bạn nhập vào cột “Quý” công thức: =INT((MONTH(E4)-1)/3)+1

Giải thích công thức: 

Hàm Month sẽ trả về kết quả tháng của “Ngày nhập hàng” rồi trừ đi 1 rồi chia cho 3 vì theo lịch thì cứ 3 tháng được tính là một quý. Sau đó dùng hàm INT để lấy số nguyên rồi cộng với 1 sẽ ra quý.

Sau khi tìm được “Quý” rồi thì chúng ta tiếp tục tính tổng thành tiền như sau:

Công thức: SUMIFS(J4:J10, G4:G10,”=2″, I4:I10,”<6000″)

Giống như ví dụ 1, sau khi đã tìm được quý rồi các bạn sẽ thêm nốt điều kiện còn lại là “<6000” là hoàn thành công thức. Lưu ý dấu ngoặc kép không được thiếu.

Công thức hàm mảng của ví dụ này như sau, các bạn có thể tham khảo và mình sẽ giải thích phía bên dưới công thức.

=SUM(IF((INT((MONTH(E4:E10)-1)/3)+1=2)*(I4:I10<6000),J4:J10))

Giải thích công thức:

Chúng ta sẽ bóc tách hàm mảng từ hàm bên trong nó, hàm MONTH sẽ là vị trí đầu tiên, các bạn có thể xem thứ tự cách hoạt động của công thức bằng cách bấm vào Formulars/ Evaluate Formular.

• INT((MONTH(E4:E10)-1)/3)+1=2 là so sánh tất cả các tháng với 2, nếu đúng sẽ trả về TRUE.
• I4:I10<6000 là điều kiện thứ 2 đơn giá nhỏ hơn 6000

Sau khi 2 điều kiện này nhân với nhau (*) hay (AND) sẽ trả về kết quả là 1 và FALSE thì sẽ nhân với J4:J10. Hàm SUM còn lại sẽ tính tổng kết quả nhân vừa rồi.

Ví dụ 5: Tính tổng thành tiền mà ngày nhập hàng vào thứ 7 và chủ nhật

Giống như ví dụ trên các bạn cũng sẽ phải tạo thêm một cột phụ “Thứ” để tính tổng thành tiền. Có 2 điều kiện một là thứ 7 và chủ nhật, các bạn đừng nhầm tưởng “thứ 7 và chủ nhật” là sẽ dùng hàm “AND” nhé. Vì không có chuyện một ngày trong tháng vừa là thứ 7 vừa là chủ nhật nên trong trường hợp này phải dùng hàm “OR“.

Bài này sẽ có 2 cách, một là dùng hàm bình thường, hai là dùng hàm mảng để tính. Với cách 1 các bạn sẽ chỉ để hàm WEEKDAY không thêm CHOOSE, còn cách 2 các bạn có thể thêm CHOOSE để tạo thành thứ nhìn đẹp mắt hơn.

Nhưng lý thuyết là thế khi vào công thức các bạn phải biến hóa mới sử dụng được với hàm SUMIFS. Các bạn hãy theo dõi cách làm của mình bên dưới nhé.

CÁCH 1:

Cột “Thứ” các bạn dùng hàm WEEKDAY và chọn cho mình chỉ số 11 để tí nữa mình sẽ giải thích vì sao lại chọn chỉ số 11 này. Công thức tìm thứ là: WEEKDAY(D5,11).

Phân tích một chút, bài này dùng hàm SUMIFS điều kiện ngày tháng mà điều kiện là thứ 7 và chủ nhật thì không thể được vì hàm SUMIFS bản chất là điều kiện hàm AND. Nên mình chọn chỉ số 11 trên để trong hàm SUMIFS chúng ta sẽ cho nó trở về bài toán “Trong một khoảng thời gian” là như thế. Chỉ số 11 khi được chọn sẽ cho thứ 2 bắt đầu là 1 và chủ nhật kết thúc là 7 (Chủ nhật), điều kiện trong khoảng thời gian là >5 (thứ 6) và <8 (chủ nhật).

Các bạn điền vào công thức như sau: SUMIFS(H5:H11, E5:E11,”>5″, E5:E11,”<8″)

Như mình đã phân tích ở trên, hàm SUMIFS bản chất là điều kiện hàm AND không phải hàm OR nên nếu cho vào =6, =7 sẽ trả về kết quả là 0. Còn ở đây mình đã đưa về khoảng tức là đổi từ AND về OR và sẽ được công thức như trên kia.

Đó là cách 1, còn dưới đây là cách 2 sử dụng hàm mảng linh hoạt và dễ kiểm soát hơn. Ở cách 2 chúng ta sẽ thêm vào hàm CHOOSE để trả về kết quả thứ dễ hình dung hơn.

Công thức sẽ như sau: =CHOOSE(WEEDAY(D11,11),”Thứ 2″,”Thứ 3″,”Thứ 4″,”Thứ 5″,”Thứ 6″,”Thứ 7″,”Chủ nhật”)

Và công thức hàm SUMIFS điều kiện ngày tháng sẽ như sau:

=SUM(IF((E5:E11=”Thứ 7″)+(E5:E11=”Chủ nhật”),H5:H11))

Và kết thúc công thức các bạn bấm tổ hợp phím Ctrl + Shift + Enter nhé.

Lưu ý quan trọng khi sử dụng

Khi dùng hàm SUMIFS điều kiện ngày tháng các bạn nhớ xác định các dạng cùa bài này gồm 3 dạng như mình đã hướng dẫn các bạn qua ví dụ trên. Các toán tử và điều kiện thời gian cần được đặt trong dấu ngoặc kép tránh thừa hoặc thiếu ngoặc.

Hàm SUMIFS bản chất là hàm điều kiện (AND) nên các bạn phải tùy biến khi ghép các điều kiện vào với nhau. Nên mở rộng thêm học hàm mảng để sử dụng một cách linh hoạt hơn, không bị bó buộc bởi các hàm cơ bản.

Bạn có thể tham khảo các bài viết các hàm cơ bản trong Excel khác tại mục Excel cơ bản hoặc tham khảo khóa học Excel online tại Học Office.

Liên hệ ngay với Học Office

• Số điện thoại: 0399162445
• Địa chỉ: Số 36, Đường Phạm Dùng, An Hồng, An Dương, Hải Phòng
• Email: hocofficecom@gmail.com
• Website: https://hocoffice.com/
• Fanpage: https://www.facebook.com/hocofficetl
• Google maps: https://goo.gl/maps/iWnK4DFBRq4XVp9B8
• Hastag: #hocoffice, #học_office, #hocofficecom, #excel, #word, #powerpoint, #congnghe, #phancung, #phanmem, #thuthuat