Hàm SUMIFS điều kiện ngày tháng dùng để báo cáo các doanh thu trong các khoảng thời gian. Hàm này được ứng dụng rất nhiều trong công việc phải tính toán, nó hỗ trợ tính tổng nhanh cho bạn có thể có kết quả nhanh chóng.
Hôm nay Blog phần mềm chi sẻ cho bạn cách sử dụng hàm SumIFS, công dụng và bài tập chi tiết của hàm SumIFS nhé!
Bạn đang xem: Hàm SumIFS điều kiện ngày tháng: công dụng, bài tập chi tiết nhất 2022
Nội dung bài viết
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 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ể liên kết 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 chính 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 để xử lý trên Microsoft Office nhé1
Hàm SUMIFS – tính tổng trong 1 tháng
Để tính tổng theo tháng dùng hàm sumifs, ta dùng cú pháp sau đây với giả sử vùng cần tính tổng là A1:A10 và vùng chứa điều kiện ngày tháng là B1:B10
=SUMIFS(B1:B10,A1:A10,”>=”&DATE(2017,1,1),A1:A10,”<=”&DATE(2017,1,31))
công thức trên tính tổng với điều kiện ngày từ 01.01.2017 đến 31.01.2017 (tháng 1 năm 2017)
Hàm SUMIFS – tính tổng trong 1 năm
Để tính tổng theo năm dùng hàm sumifs, ta dùng cú pháp sau đây với giả sử vùng cần tính tổng là A1:A10 và vùng chứa điều kiện ngày tháng là B1:B10
=SUMIFS ( A1:A10, B1:B10 , “>=” & DATE ( 2017 , 1 , 1), B1:B10, “<=” & DATE(2017,12,31))
công thức trên sẽ tính tổng của vùng A1:A10 trong khoảng thời gian từ 01.01.2017 đến 31.12.2017 (trong năm 2017)
Hàm sumifs – tính tổng trong 1 khoảng thời gian
Để tính tổng sử dụng hàm sumifs, ta dùng như sau:
=SUMIFS ( A1:A10, B1:B10 , “>=” & DATE ( 2017 , 1 , 18), B1:B10, “<=” & DATE(2017,4,23))
cũng như VD tính năm ở trên, bạn chỉ cần thay điều kiện là đã đạt được công thức của riêng mình để tính tổng trong vùng A1:A10 trong khoảng thời gian từ 18.01.2017 tới 23.04.2017.
Như vậy trong bài viết này, các bạn đã có thể trải nghiệm hàm SUMIFS để tính tổng theo điều kiện thời gian liên kết với hàm DATE, từ những bí quyết này, việc tính tổng dựa trên thời gian liên kết với hàm TIME cũng sẽ được giải quyết tương tự. Hi vọng bài content hữu ích cho công việc của các bạn.
Làm sao để sử dụng SUMIFS trong Excel – Bài tập chi tiết
Trước hoặc sau một mốc thời gian
Trong hoàn cảnh này nếu như chỉ có một điều kiện là mốc thời gian thì con người nên dùng hàm sumif. Còn nếu như đòi hỏi 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
hàm sumifs điều kiện ngày tháng
Bài tập dạng đầu tiên này khá dễ dàng, bạn chỉ phải xác định mốc thời gian đấy rồi cho toán tử vào ghen tị. Bạn hãy nhập công thức như phía dưới của mình, không hẳ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.
ham sumifs dieu kien ngay thang
bí quyết 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 chúng ta cũng cần cố định bí quyết 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.
bai tap ham sumifs trong excel
ví dụ này nâng cao hơn một tí, các bạn có thể phải tạo thêm một cột phụ là cột năm bằng cách sử dụng hàm Year để thu thập 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 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 không thể không khi cho vào điều kiện phải cho trong dấu ngoặc kép nếu như 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 đấy là có khả năng sử dụng hàm mảng, các chúng ta có thể tham khảo cách dưới đây:
VD 2 hàm sumifs
=SUM(IF((YEAR(E4:E10)=2021)*(H4:H10>50),J4:J10))
trình bày một 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 đấy 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.
Trong một khoảng thời gian
ví dụ 3: Tính tổng thành tiền các sản phẩm trong khoảng ngày từ 01/10/2021 – 31/10/2021
hàm sumifs điều kiện ngày tháng
Dạng bài này chỉ thêm một điều kiện đối 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 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.
hàm sumifs
Ở công thức này sẽ 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ứ
VD 4: Tính tổng thành tiền theo quý 2 và có đơn giá nhỏ hơn 6000
bai tap ham sumifs trong excel
Dạng bài này mình sẽ phải tạo thêm cột phú là cột quý để liên kết với điều kiện đơn giá nhỏ hơn 6000. Câu VD 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ý.
một khi tìm được “Quý” rồi thì con người bắt đầu tính tổng thành tiền như sau:
công thức tổng thành tiền theo quý 2
Công thức: SUMIFS(J4:J10, G4:G10,”=2″, I4:I10,”<6000″)
chẳng hạn 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 chúng ta có thể tham khảo và mình sẽ trình bày phía phía dưới bí quyết.
bí quyết mảng VD hàm sumifs
=SUM(IF((INT((MONTH(E4:E10)-1)/3)+1=2)*(I4:I10<6000),J4:J10))
giải thích công thức:
con người 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 bí quyết bằng việc nhấn vào Formulars/ Evaluate Formular.
- INT((MONTH(E4:E10)-1)/3)+1=2 là so sánh toàn bộ các tháng với 2, nếu như đúng sẽ trả về TRUE.
- I4:I10<6000 là điều kiện thứ hai đơn giá nhỏ hơn 6000
một 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.
Xem thêm: Cách tính tổng trong Word với hàm SUM dễ hiểu nhất 2022
VD 5: Tính tổng thành tiền mà ngày nhập hàng vào thứ 7 và chủ nhật
hàm sumifs điều kiện ngày tháng
giống như VD 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ì vẫn chưa có chuyện một ngày trong tháng vừa là thứ 7 vừa là chủ nhật nên trong hoàn cảnh 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 xinh đẹp hơn.
nhưng lý thuyết là thế khi vào bí quyết 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:
nắm rõ ràng thứ dựa vào hàm weekday
Cột “Thứ” các bạn dùng hàm WEEKDAY và chọn cho mình thông số 11 để tí nữa mình sẽ giải thích vì sao lại chọn chỉ số 11 này. bí quyết 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 thực 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 con người sẽ cho nó trở về bài toán “Trong một khoảng thời gian” là như thế. thông 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 nhập vào công thức như sau: SUMIFS(H5:H11, E5:E11,”>5″, E5:E11,”<8″)
Như mình đã đo đạt ở trên, hàm SUMIFS thực 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 bí quyết như trên kia.
đó là cách 1, còn dưới đây là cách 2 dùng hàm mảng linh hoạt và dễ kiểm soát hơn. Ở cách 2 con người sẽ thêm vào hàm CHOOSE để trả về kết quả thứ dễ hình dung hơn.
Tìm thứ trong Excel
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:
hàm mảng sumifs
=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é
Tổng kết
Bài viết trên của blogphanmem.vn đã tổng hợp những thông tin cần biết về hàm SumIFS mà bạn muốn biết. Hi vọng qua bài viết này các bạn có thể sử dụng thành thạo sử dụng hàm SumIFS điều kiện ngày tháng nhé!
Chúc các bạn một ngày tốt lành!
