Top 2 # Không Có Hàm Datedif Trong Excel 2007 Xem Nhiều Nhất, Mới Nhất 1/2023 # Top Trend | Trucbachconcert.com

Hướng Dẫn Cách Tính Số Ngày Trong Excel Bằng Hàm Datedif

Hàm DATEDIF trong Excel thường được dùng để tính toán một khoảng thời gian hoặc chênh lệch giữa hai thời điểm tính theo ngày, tháng và năm. Bạn có thể sử dụng hàm DATEDIF để xác định khoảng thời gian cho dự án sắp tới hoặc cũng có thể dùng nó để cộng trừ và tính số làm việc của các nhân viên trong công ty.

Ghi chú: Những hướng dẫn trong bài viết này áp dụng cho Excel 2019, 2016, 2013, 2010, and Excel của Microsoft Office 365.

Công thức hàm DATEDIF

Cú pháp của hàm đề cập đến cách bố trí của hàm và bao gồm tên, dấu ngoặc và các đối số bên trong. Công thức của hàm DATEDIF là:

=DATEDIF(start_date,end_date,"unit")

Start_date (bắt buộc): Bạn có thể nhập ngày bắt đầu của khoảng thời gian đã chọn hoặc tham chiếu ô đến vị trí của dữ liệu này trong bảng tính.

End_date (bắt buộc): Cũng như ngày bắt đầu, bạn có thể nhập ngày kết thúc của khoảng thời gian đã chọn hoặc sử dụng tham chiếu ô tương ứng.

Unit (bắt buộc): Đơn vị này cho biết chức năng tìm số ngày (“D”), số tháng (“M”), hoặc số năm (“Y”) giữa hai khoảng thời gian xác định. Bạn phải sử dụng dấu ngoặc kép để bao quanh số liệu này, ví dụ như “D” hay “M”.

Ngoài ra, D, M và Y còn có ba tùy chọn khác mà bạn có thể nhìn thấy như bên dưới:

“YD” dùng để tính toán số ngày giữa hai thời điểm, nhưng bỏ qua đối số Năm (hàng 5).

“YM” dùng để tính toán số tháng giữa hai thời điểm, nhưng bỏ qua đối số Ngày và Năm (hàng 6).

“MD” dùng để tính toán số ngày giữa hai thời điểm, nhưng bỏ qua đối số Tháng và Năm (hàng 7).

Hướng dẫn cách tính số ngày trong Excel với hàm DATEDIF

Công thức bạn cần nhập vào ô B2 là =DATEDIF(A2,A3,”D”).

Bước 1: Bạn nhấn chuột trái vào ô B2, đây là nơi hiển thị số ngày giữa hai khoảng thời gian bạn cần tính toán.

Bước 4: Nhập dấu ( , ) vào trong ô B2 để phân cách hai đối số của hàm.

Bước 5: Nhấp tiếp vào ô A3 để nhập dữ liệu này làm đối số end_date của hàm.

Bước 7: Đối với đối số Unit, bạn nhập chữ D trong dấu ngoặc kép ( “D” ) để tính toán số ngày giữa hai thời điểm.

Bước 8: Nhập dấu Ngoặc đơn để đóng công thức lại.

Bước 9: Nhấn Enter trên bàn phím để hiển thị kết quả.

Bước 10: Số ngày 829 sẽ xuất hiện trong ô B2 của bảng tính.

Bước 11: Khi bạn nhấp chuột vào ô B2, công thức của hàm DATEDIF sẽ được hiển thị trong Thanh công thức nằm ở phía trên của bảng tính. Hoặc bạn có thể nhìn sang cột Công thức ở trên hình để biết cách tính số ngày trong Excel.

Các lỗi thường gặp khi dùng hàm DATEDIF

Nếu bạn không nhập chính xác các đối số của hàm DATEDIF, thì các lỗi sau sẽ xuất hiện:

#VALUE!: Bạn sẽ gặp lỗi này nếu Start_date hoặc End_date không phải là ngày thực tế (trong hình ảnh ví dụ bên dưới, ô A8 chứa dữ liệu văn bản chứ không phải là ngày tháng cụ thể).

#NUM!: Bạn sẽ thấy lỗi này khi biến End_date sớm hơn so với biến Start_date (Dòng 9 bên dưới).

Hướng Dẫn Cách Sử Dụng Hàm Datedif Trong Google Sheets Và Excel

Rất nhiều người dùng bảng tính cảm thấy ngày tháng khó hiểu, nếu không muốn nói là cực kỳ khó xử lý. Nhưng bạn có tin hay không, Goolge Sheets có một vài hàm rất tiện dụng và đơn giản cho nhiệm vụ này và đó là hàm DATEDIF và NETWORKDAYS.

Như tên gọi của hàm, DATEDIF trong Google Sheets dùng để tính toán chênh lệch ngày giữa hai ngày. Hàm này yêu cầu ba đối số với công thức tổng quát như sau:

=DATEDIF(start_date, end_date, unit)

Trong đó:

Start_date: Là ngày được sử dụng làm điểm bắt đầu và nó phải là một trong những điều sau:

– Ngày phải đặt trong dấu ngoặc kép: “8/13/2020”.

– Tham chiếu đến ô có ngày: A2

– Công thức trả về ngày: DATE (2020, 8, 13)

– Một số đại diện cho một ngày cụ thể và có thể được Google Sheets hiểu là ngày, ví dụ: 44056 đại diện cho ngày 13 tháng 8 năm 2020 .

End_date: Một ngày được sử dụng làm điểm cuối. Nó phải có cùng định dạng với start_date.

– “D” – (Viết tắt của Date) trả về số ngày giữa hai ngày.

– “M” – (Months) số tháng đầy đủ giữa hai ngày.

– “Y” – (Year) số năm đầy đủ.

– “MD” – (Days ignoring Months): Số ngày sau khi trừ đi cả tháng.

– “YD” – (Days ignoring Years): Số ngày sau khi trừ đi cả năm.

– “YM” – (Months ignoring years): Số tháng đầy đủ sau khi trừ đi các năm đầy đủ.

Lưu ý: Tất cả các đơn vị phải được đặt vào công thức giống như cách chúng xuất hiện ở trên – trong dấu ngoặc kép.

Bây giờ, hãy ghép tất cả các phần này lại với nhau và xem công thức DATEDIF hoạt động như thế nào trong Google Sheets.

Tính số ngày giữa hai ngày trong Google Sheets

Ví dụ 1. Đếm tất cả các ngày

Bạn có một bảng dữ liệu Goolge Sheets để theo dõi một số đơn đặt hàng. Tất cả chúng đã được xuất xưởng vào nửa đầu tháng 8 – Ngày vận chuyển (Shipping date) sẽ là ngày bắt đầu. Ngoài ra còn có một ngày giao hàng gần đúng – Ngày đến hạn (Due date).

Bây giờ bạn cần tính số ngày – “D” – giữa ngày vận chuyển và ngày đến hạn để xem mất bao lâu để các mặt hàng đến nơi. Đây là công thức nên sử dụng:

=DATEDIF(B2,C2,”D”)

Bạn chỉ cần nhập công thức DATEDIF vào ô D2 và sau đó sao chép xuống các ô khác trong cột để áp dụng cho các hàng khác.

Lưu ý: Bạn cũng có thể tính toán toàn bộ cột cùng một lúc bằng một công thức duy nhất thông qua hàm ARRAYFORMULA:

=ArrayFormula(DATEDIF(B2:B13,C2:C13,”D”))

Ví dụ 2. Đếm ngày bỏ qua tháng

Bằng cách bỏ qua những tháng đầy đủ đã trôi qua. DATEDIF tính toán điều này tự động khi bạn sử dụng đơn vị “MD” :

=DATEDIF(A2, B2,”MD”)

Như vậy hàm DATEDIF sẽ trừ các tháng đã qua và đếm số ngày còn lại.

Ví dụ 3. Đếm ngày bỏ qua năm

Một đơn vị khác là “YD” sẽ hỗ trợ khi các ngày có hơn một năm giữa chúng:

=DATEDIF(A2,B2,”YD”)

Công thức trên của hàm DATEDIF sẽ trừ năm đầu tiên, sau đó tính các ngày còn lại giống như các ngày đó trong cùng một năm.

Đếm ngày làm việc trong Google Sheets

Start_date: Ngày được sử dụng làm điểm bắt đầu. Cần thiết.

Có một trường hợp đặc biệt khi bạn chỉ cần tính ngày làm việc trong Google Sheets. Hàm DATEDIF sẽ không hữu ích nhiều ở đây. Và bạn sẽ đồng ý rằng trừ các ngày cuối tuần theo cách thủ công không phải là cách nhanh nhất. Tuy nhiên Google Sheets có một vài cách để giúp bạn làm điều đó nhanh hơn.

End_date: Một ngày được sử dụng làm điểm cuối. Cần thiết.

Ví dụ 1. Hàm NETWORKDAYS

Holidays: Cái này là tùy chọn khi bạn cần chỉ ra các ngày lễ cụ thể. Nó phải là một phạm vi ngày hoặc số đại diện cho ngày tháng.

Hàm đầu tiên mà bạn có thể sử dụng là NETWORKDAYS. Hàm này sẽ tính toán số ngày làm việc giữa hai ngày không bao gồm cuối tuần (thứ bảy và chủ nhật) và thậm chí cả ngày lễ nếu cần:

=NETWORKDAYS(start_date, end_date, [holidays])

Trong đó:

Lưu ý: Nếu ngày này không phải là ngày nghỉ thì nó được tính là ngày làm việc.

Lưu ý: Nếu ngày này không phải là ngày nghỉ thì nó được tính là ngày làm việc.

Để minh họa cách hoạt động của hàm trên, chúng tôi sẽ thêm danh sách các ngày lễ diễn ra giữa ngày giao hàng và ngày đến hạn:

=NETWORKDAYS(B2,C2,$E$2:$E$4)

Start_date: Ngày được sử dụng làm điểm bắt đầu. Cần thiết.

End_date: Một ngày được sử dụng làm điểm cuối. Cần thiết.

Mẹo: Nếu bạn định sao chép công thức sang các ô khác, hãy sử dụng tham chiếu ô tuyệt đối cho ngày lễ để tránh lỗi hoặc kết quả không chính xác. Hoặc xem xét việc xây dựng một công thức mảng.

Từ kết quả trên bạn có thể thấy số ngày giảm so với sử dụng công thức DATEDIF ở trên. Điều này là vì lúc này hàm NETWORKDAYS sẽ tự động trừ đi tất cả các ngày thứ bảy, chủ nhật và hai ngày lễ diễn ra vào thứ sáu và thứ hai.

Lưu ý: Không giống như hàm DATEDIF trong Google Sheets, hàm NETWORKDAYS sẽ tính Start_day và End_day là ngày làm việc trừ khi chúng là ngày lễ. Do đó, D7 trả về 1.

Ví dụ 2. chúng tôi cho Google Sheets

Nếu bạn có lịch làm việc cuối tuần tùy chỉnh, trong trường hợp này bạn sẽ cần sử dụng hàm NETWORKDAYS.INTL. Nó cho phép bạn tính số ngày làm việc trong Google Sheets dựa trên các ngày cuối tuần do cá nhân đặt. Công thức tổng quát của hàm này như sau:

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Trong đó:

Holidays: Là tham số tùy chọn và được sử dụng để chỉ định ngày nghỉ.

Lưu ý: Hàm chúng tôi trong Google Sheets cũng tính start_day và end_day là ngày làm việc trừ khi chúng là ngày lễ.

CHỈ 7 GIỜ HỌC BÀI BẢN, TIẾP KIỆM HÀNG CHỤC NGHÌN GIỜ TRA CỨU

Weekend: Cái này là tùy chọn. Nếu bỏ qua, thứ bảy và chủ nhật được coi là ngày nghỉ cuối tuần. Nhưng bạn có thể thay đổi điều đó bằng hai cách là Masks và Numbers.

Masks: là một mẫu gồm bảy chữ số gồm 1 và 0. 1 là viết tắt của một ngày cuối tuần, 0 cho một ngày làm việc. Chữ số đầu tiên trong mẫu luôn là thứ Hai, chữ số cuối cùng sẽ là Chủ nhật.

Ví dụ: “1100110” có nghĩa là bạn làm việc vào Thứ Tư, Thứ Năm, Thứ Sáu và Thứ Bảy.

Lưu ý: Masks phải được đặt trong dấu ngoặc kép và cách này là hoàn hảo cho những ngày nghỉ của bạn rải rác trong tuần.

Chức năng này có vẻ phức tạp vì tất cả những con số đó, nhưng lời khuyên là bạn nên thử. Trước tiên, chỉ cần hiểu rõ về những ngày nghỉ của bạn. Sau đó, quyết định cách để chỉ ra những ngày cuối tuần của bạn.

Nếu bạn chọn Masks thì dãy số sẽ có cấu tạo là 1000001, nên hàm chúng tôi trong trường hợp này sẽ là:

=NETWORKDAYS.INTL(B2,C2,”1000001″)

Nhưng vì bạn muốn có hai ngày cuối tuần liên tiếp nên có thể sử dụng một số từ các bảng trên, 2 trong trường hợp này:

=NETWORKDAYS.INTL(B2, C2, 2)

Sau đó, chỉ cần thêm đối số cuối cùng – tham chiếu đến các ngày lễ trong cột E và công thức đã sẵn sàng:

=NETWORKDAYS.INTL(B2,C2,2,$E$2:$E$4)

Google Sheets và sự khác biệt về ngày tháng

Đôi khi vài tháng quan trọng hơn ngày. Nếu điều này đúng với bạn và muốn nhận được chênh lệch ngày theo tháng hơn là ngày, hãy để Google Sheets với hàm DATEDIF thực hiện công việc.

Ví dụ 1. Tính số tháng đầy đủ giữa hai ngày

Cách thực hiện cũng giống như vậy: Start_date là đối số đầu tiên, tiếp theo là end_date và “M” – viết tắt của Months như một đối số cuối cùng:

=DATEDIF(A2,B2,”M”)

Mẹo: Đừng quên về hàm ARRAUFORMULA có thể giúp bạn đếm tháng trên tất cả các hàng cùng một lúc:

=ARRAYFORMULA(DATEDIF(A2:A13, B2:B13,”M”))

Ví dụ 2. Tính số tháng bỏ qua năm

Bạn có thể không cần tính số tháng trong suốt tất cả các năm giữa ngày bắt đầu và ngày kết thúc. Và hàm DATEDIF cho phép bạn làm điều đó.

Chỉ cần sử dụng đơn vị “YM” và công thức sẽ trừ nguyên năm trước, sau đó đếm số tháng giữa các ngày với công thức sau:

=DATEDIF(A2,B2,”YM”)

Tính số năm giữa hai ngày trong Google Sheets

Điều cuối cùng (nhưng không kém phần quan trọng) cho bạn thấy là cách Google Sheets sử dụng hàm DATEDIF để tính toán chênh lệch ngày tháng theo năm.

Ví dụ tính số năm của các cặp vợ chồng đã kết hôn dựa trên ngày cưới của họ và ngày hiện tại:

Như bạn có thể đã đoán ra, trong trường hợp này sẽ sử dụng đơn vị “Y” cho điều đó với công thức sau:

=DATEDIF(A2, B2,”Y”)

Tất cả các công thức sử dụng hàm DATEDIF này là công thức đầu tiên cần thử khi tính toán ngày, tháng và năm giữa hai ngày trong Google Sheets.

Hàm Đếm Có Điều Kiện Trong Excel

Mô tả

Hàm COUNTIF là hàm đếm có điều kiện trong Excel, các bạn có thể đếm dữ liệu dựa trên điều kiện chỉ định sẵn.

Cú pháp

=COUNTIF(range;criteria)

Trong đó range là bắt buộc, là vùng mà các bạn muốn đếm dữ liệu. Nó có thể chứa số, mảng hoặc tham chiếu có chứa số… Các giá trị trống được bỏ qua.

Criteria (bắt buộc), đây là điều kiện để đếm các giá trị trong range, nó có thể là số, biểu thức, tham chiếu ô hoặc chuỗi văn bản.

Nếu các bạn muốn đếm với nhiều điều kiện thì các bạn sử dụng hàm COUNTIFS. (chèn link http://thuthuatphanmem.vn/ham-countifs-trong-excel/ vào tên COUNTIFS giúp mình)

Lưu ý

Hàm COUNTIF trả về kết quả sai khi bạn sử dụng điều kiện với các chuỗi dài hơn 255 ký tự.

Đối số criteria cần ghi trong dấu ngoặc kép.

Criteria không phân biệt chữ hoa/thường.

Có thể sử dụng ký tự đại diện dấu chấm hỏi (?) và dấu sao (*) trong điều kiện criteria (1 dấu hỏi tương ứng với 1 ký tự, 1 dấu sao tương ứng với một chuỗi ký tự). Nếu các bạn muốn sử dụng dấu chấm hỏi hay dấu sao không phải làm ký tự đại diện thì các bạn cần thêm ký tự ~ phía trước.

Ví dụ

Cho bảng số liệu như sau:

1. Đếm số lượng đơn hàng có tên mặt hàng là Cam.

Để thực hiện yêu cầu này các bạn nhập công thức hàm CountIf như sau:

=COUNTIF(B6:B14;”Cam”)

Kết quả các bạn sẽ thấy có 3 đơn hàng có mặt hàng là Cam.

2. Đếm số lượng đơn hàng có tên mặt hàng không phải là Cam.

4. Đếm số đơn hàng có tên mặt hàng là Cam sử dụng ký tự thay thế *

Thay vì các bạn nhập Cam trong điều kiện hàm CountIf thì các bạn có thể nhập C* để đếm.

=COUNTIF(B6:B14;”C*”)

Kết quả các bạn cũng được 3 đơn hàng:

5. Đếm số lượng đơn hàng có tên mặt hàng khác ô B7 (Cam).

Các bạn có thể sử dụng ký tự “&” trước ô tham chiếu trong điều kiện hàm CountIf như sau:

Hàm Sumif Trong Excel, Có Ví Dụ Và Video, Cách Dùng Hàm Tính Tổng Có Đ

SUMIF là một hàm phổ biến và được ưa dùng, nếu bạn thường xuyên phải làm việc với Excel thì đây sẽ là một hàm mà bạn nên biết về cú pháp cũng như cách sử dụng hàm, nó sẽ giúp bạn rất nhiều trong công việc của bạn đó.

Hướng dẫn dùng hàm SUMIF, cú pháp và ví dụ minh họa

Cách dùng hàm SUMIF – Ví dụ minh họa

1. Cấu trúc hàm SUMIF trong EXCEL Cấu trúc hàm SUMIF: SUMIF(range, criteria,sum_range)

Trong đó:Range: Là vùng được chọn có chứa các ô điều kiện.Criteria: Là điều kiện để thực hiện hàm này.Sum_range: Vùng cần tính tổng.

Chú ý: Do tính toán trong ô của Excel, nên hàm SUMIF tính tổng này trên các phiên bản Excel 2016, Excel 2013, hay các phiên bản đời trước như Excel 2010, 2007, 2003 đều áp dụng cấu trúc hàm như nhau.

Với công thức cho ô D15 là : =SUMIF(C5:C14,”Nhân Viên”,D5:D14)

2. cách sử dụng hàm SUMIF trong EXCEL

Ta thu được kết quả như sau: 2500000. Khi chúng ta kiểm tra lại kết quả thì thấy rằng trong VD hàm Sumif này có 5 người có chức vụ là “Nhân viên”, như vậy tổng phụ cấp cho những người đó với kết quả là 2500000 là chính xác.

2.2 Cách sử dụng hàm SUMIF với điều kiện là text

Lưu ý: Lưu ý trong các công thức Excel SUMIF, theo sau toán tử so sánh là một số hoặc text phải luôn luôn được đóng trong dấu ngoặc kép (“”).

Hàm SUMIF cho phép bạn có thể thêm các giá trị tùy thuộc vào ô tương ứng trong cột khác có chứa text hoặc không.

Trong trường hợp này bạn không cần thay đổi công thức để tính tổng giá trị dựa trên các tiêu chí khác, chỉ cần nhập một giá trị mới vào ô được tham chiếu.

Công thức 1: =SUMIF(A2:A8, “=”&F1, C2:C8)

2.4 Công thức SUMIF với ký tự đại diện

Công thức 2: =SUMIF(A2:A8, F1, C2:C8)

Nếu mục đích của bạn là tính tổng các ô dựa trên các điều kiện “text” và muốn tổng hợp bằng cách đối sánh một phần, trường hợp này bạn sẽ phải sử dụng các ký tự đại diện trong công thức SUMIF.

– Dấu hoa thị (*) – đại diện cho bất kỳ ký tự nào

Ví dụ 1: Tính tổng giá trị dựa trên kết hợp từng phần

– Dấu hỏi chấm (?) – đại diện cho một ký tự duy nhất tại một vị trí cụ thể

=SUMIF(A2:A8, “*chuối*”, C2:C8) – điều kiện bao gồm phần text được đặt trong dấu hoa thị (*).

=SUMIF(A2:A8, “*”&F1&”*”, C2:C8) – điều kiện bao gồm các ô tham chiếu được đặt trong dấu hoa thị, sử dụng dấu (&) trước và sau một tham chiếu ô để nối chuỗi.

Nếu chỉ muốn đếm những ô bắt đầu hoặc kết thúc bằng một text cụ thể nào đó, chỉ cần thêm * vào trước hoặc sau text:

=SUMIF(A2:A8, “chuối*”, C2:C8) – tổng giá trị trong C2:C8 nếu một ô tương ứng trong cột A bắt đầu bằng từ “chuối”.

Mẹo:

=SUMIF(A2:A8, “*chuối”, C2:C8) – tổng giá trị trong C2:C8 nếu một ô tương ứng trong cột A kết thúc bằng từ “chuối”.

=SUMIF(A2:A8, “*”&F1&”*”, C2:C8) Ví dụ 2. Tính tổng giá trị với các ký tự nhất định

Sử dụng toán tử nối (&) để nối các ký tự đại diện với tham chiếu ô. Trong ví dụ trên, bạn cũng có thể sử dụng công thức sau để tính tổng các mặt hàng “chuối” trong kho:

=SUMIF(A2:A8,”?*”, C2:C8) – cộng giá trị trong các ô C2:C8 nếu một ô tương ứng trong cột A chứa ít nhất 1 biểu tượng.

=SUMIF(A2:A8,”*”,C2:C8) – bao gồm các ô trống, chứa các chuỗi độ dài bằng 0 được trả về bởi các công thức khác, ví dụ: = “”.

Ví dụ 4: Sử dụng các ký tự * hoặc ? như các ký tự bình thường

Cả 2 công thức trên đều bỏ qua các giá trị không phải là text, chẳng hạn như các lỗi, các phép toán logic, số và ngày tháng.

Nếu muốn sử dụng các ký tự * hoặc ? giống như các ký tự thông thường chứ không phải các ký tự đại diện, chỉ cần thêm toán tử (~) trước đó.

Ví dụ 1: Thêm giá trị lớn nhất / giá trị nhỏ nhất

Để tính tổng lớn nhất hoặc nhỏ nhất trong một vùng, sử dụng kết hợp hàm SUM với các hàm LARGE hoặc SMALL.

=SUM(LARGE(B1:B10,{1,2,3,4,5})) – tổng của 5 giá trị lớn nhất

=SUM(SMALL(B1:B10,{1,2,3,4,5})) – tổng của 5 giá trị nhỏ nhất

Ví dụ 2. Tính tổng giá trị trên / dưới

Lưu ý: Nếu có 2 hoặc nhiều giá trị giống nhau, thì chỉ có giá trị đầu tiên được tính.

Nếu muốn tính tổng nhiều giá trị, thay vì liệt kê tất cả các giá trị này trong công thức, bạn có thể lồng các hàm ROW và INDIRECT trong công thức SUM. Trong hàm INDIRECT sử dụng số hàng đại diện cho giá trị mà bạn muốn thêm.

Ví dụ 3. Tính tổng giá trị lớn nhất / nhỏ nhất của các biến

Vì đây là các công thức mảng, nhớ truy cập các mảng bằng cách sử dụng tổ hợp phím Ctrl + Shift + Enter.

Nếu không muốn phải thay đổi công thức mỗi lần tính tổng giá trị các ô khác nhau, bạn có thể nhập giá trị trong một ô nào đó, thay vì nhập vào công thức. Sau đó sử dụng hàm ROW và INDIRECT, tham chiếu một ô chứa biến, trong ví dụ này là ô E1:

=SUM(LARGE(B1:B50,ROW(INDIRECT(“1:”&E1)))) – tổng các biến các giá trị trên cùng.

=SUM(SMALL(B1:B50,ROW(INDIRECT(“1:”&E1)))) – tổng các biến các giá trị dưới cùng.

2.6 Cách tính tổng các ô tương ứng với các ô trống

Cần lưu ý một điều đây là các công thức mảng nên bạn sẽ phải sử dụng tổ hợp phím Ctrl + Shift + Enter để hoàn tất.

=SUMIF(A2:A10,””,C2:C10)

Nếu ô trống bao gồm các chuỗi có độ dài bằng không (ví dụ: các ô có công thức như = “”), sử dụng “” làm điều kiện:

2.7 Cách tính tổng các ô tương ứng với các ô không phải trống

Trong cả 2 công thức trên đánh giá các ô trong cột A và nếu phát hiện có bất kỳ ô trống nào, các giá trị tương ứng sẽ được thêm vào cột C.

2.8 Cách sử dụng hàm SUMIF với điều kiện ngày tháng

Công thức trên đếm các giá trị tương ứng với tất cả các ô không trống, bao gồm các chuỗi ký tự có độ dài bằng không.

Về cơ bản sử dụng hàm SUMIF để tính tổng các giá trị dựa trên điều kiện ngày tháng giống như cách sử dụng hàm SUMIF với các điều kiện chuẩn như text và số.

Nếu muốn tính tổng giá trị tương ứng với các ngày lớn hơn, nhỏ hơn hoặc bằng với ngày mà bạn chỉ định, chỉ cần sử dụng các toán tử mà chúng tôi giới thiệu ở phần trên.

Khá nhiều người dùng đặt ra câu hỏi cách tính tổng giá trị giữa 2 ngày như thế nào?

Câu trả lời là sử dụng kết hợp hoặc chính xác hơn là sử dụng 2 hàm SUMIF khác nhau. Trong Excel 2007 hoặc cao hơn, bạn có thể sử dụng hàm SUMIFS cho nhiều điều kiện. Tuy nhiên trong bài viết này chúng tôi sẽ chỉ giới thiệu cho bạn về hàm SUMIF.

Công thức trên tính tổng giá trị trong các ô C2:C9 nếu ngày trong cột B nằm trong khoảng từ 1/10/2014 – 31/10/2014.

2.10 Tính tổng giá trị trong các cột

Hàm SUMIF đầu tiên tính giá trị các ô C2:C9 trong đó ô tương ứng trong cột B lớn hơn hoặc bằng ngày đầu tiên (trong ví dụ trên là ngày 1/10). Sau đó trừ đi các giá trị rơi vào sau ngày cuối cùng (ngày 31/10) được trả về bởi hàm SUMIF thứ hai.

Chẳng hạn như bạn muốn tính tổng số táo bán ở tất cả các cửa hàng trong 3 tháng qua.

Như bạn đã biết, kích thước của sum_range được xác định bởi kích thước tham số range. Đó là lý do tại sao bạn không thể sử dụng công thức như =SUMIF(A2:A9,”apples”,C2:E9), vì công thức này sẽ thêm các giá trị tương ứng “táo” trong cột C. Đây không phải là những gì mà bạn đang tìm kiếm.

Giải pháp hợp lý nhất là tạo một cột phụ để tính tổng từng mặt hàng một, sau đó tham chiếu cột phụ đó trong sum_range.

Nhập công thức SUM đơn giản trong ô F2, sau đó nhập vào cột F công thức: = SUM (C2: E2).

=SUMIF(A2:A9, H1, F2:F9)

Hoặc:

Trong các công thức trên, range và sum_range có cùng số hàng và số cột, bao gồm 1 cột và 8 dòng, và trả về kết quả:

=SUM(SUMIF(A2:A9,I1,C2:C9), SUMIF(A2:A9,I1,D2:D9), SUMIF(A2:A9,I1,E2:E9))

Nếu muốn tính tổng giá trị mà không cần tạo cột phụ, cách đơn giản là viết từng công thức SUMIF riêng cho các cột mà bạn muốn tính tổng, sau đó sử dụng hàm SUM để tính tổng:

{=SUM((C2:C9+D2:D9+E2:E9)*(–(A2:A9=I1)))}

Hoặc cách khác là sử dụng công thức mảng phức tạp hơn (đừng quên sử dụng tổ hợp phím Ctrl + Shift + Enter):

Tại sao hàm SUMIF không trả về kết quả?

Cả 2 công thức trên đều trả về kết quả là 2070.

1. Các tham số phạm vi và sum_range phải là vùng dữ liệu chứ không phải mảng.

Các tham số đầu tiên (range) và thứ ba (sum_range) trong công thức SUMIF phải là tham chiếu vùng, chẳng hạn như A1: A10. Nếu là mảng, chẳng hạn như {1,2,3}, Excel sẽ trả lại một thông báo lỗi.

Công thức chính xác: =SUMIF(A1:A3, “hoa”, C1:C3)

Công thức sai: =SUMIF({1,2,3}, “hoa”, C1:C3)

2. Cách tính tổng các giá trị từ sheet hoặc bảng tính khác.

Giống như hầu hết các hàm Excel khác, hàm SUMIF có thể tham chiếu các sheet hoặc các bảng tính khác, miễn là các sheet và bảng tính đang mở.

Tuy nhiên công thức trên sẽ không hoạt động nếu Book 1 bị đóng. Điều này xảy ra vì vùng dữ liệu được tham chiếu bởi công thức SUMIF trong các bảng tính không được tham chiếu đến các mảng, và vì không có mảng nào được tham chiếu trong các tham số range và sum_range, Excel sẽ trả về thông báo lỗi #VALUE! .

3. Để tránh lỗi xảy ra, đảm bảo range và sum_range phải cùng kích thước.

Trong các phiên bản Excel mới hơn, tham số range và sum_range không nhất thiết phải có cùng kích thước. Trong phiên bản Excel 2000 và các phiên bản cũ hơn, nếu kích thước của range và sum_range không bằng nhau sẽ gây ra lỗi.

Tuy nhiên trong các phiên bản Excel mới hơn như Excel 2010 và Excel 2016, công thức SUMIF phức tạp hơn nhiều, trong đó tham số sum_range có số hàng, số cột ít hơn so với tham số range. Đó là lý do tại sao các tham số range và sum_range phải cùng kích thước, để tránh lỗi xảy ra.

Cách tránh lỗi VALUE khi sử dụng hàm SUMIF giữa các bảng tính

Khi sử dụng hàm SUMIF giữa các bảng tính, trong trường hợp nếu bảng tính nguồn không mở, bạn sẽ nhận được thông báo lỗi VALUE. Lỗi này xảy ra nếu các ô trong bảng tính chứa công thức có các hàm SUMIF, COUNTIF, hoặc hàm COUNTBLANK bị đóng. Để tránh lỗi này bằng cách sử dụng kết hợp hàm SUM và hàm IF trong một công thức mảng.

Công thức mảng là công thức có thể thực hiện nhiều phép tính trên một hoặc nhiều mục trong một mảng. Các công thức mảng hoạt động trên hai hay nhiều giá trị được gọi là tham số mảng.

Lưu ý: Bạn có thể áp dụng cách này cho Excel 2010, 2013, 2016 cho Windows.

Bước 1: Mở bảng tính chứa dữ liệu nguồn (Data workbook).

Bước 2: Mở bảng tính có chứa công thức (Report workbook).

Bước 3: Chọn ô C5 trong bảng tính có chứa công thức.

Bước 4: Sử dụng nút FX trên thanh công thức (Formula Bar), xác định vị trí hàm Sum.

Bước 5: Để lồng hàm IF, trên thanh công thức, tại mục Name Box, từ menu bạn chọn IF.

Bước 6: Nếu hàm IF không hiển thị trong menu, bạn chọn More Functions rồi tìm và chọn hàm IF.

Bước 8: Để hoàn tất công thức mảng, sử dụng tổ hợp phím Ctrl + Shift + Enter.

Bước 9: Chọn Yes nếu được hỏi công thức đã chính xác chưa. Tên vùng dữ liệu (name range) có thể xác định cho CategoryNames và ProductSales.

Bằng cách trên bạn không còn phải lo lắng về lỗi sẽ xảy ra nữa, điều này giúp bạn tiết kiệm được một khoảng thời gian đáng kể.

Qua ví dụ trên chắc hẳn bạn đã hiểu được cách dùng hàm SUMIF trên bảng tính Excel. Nhờ đó mà bạn có thể vận dụng hàm cơ bản trong Excel này vào công việc của mình hiệu quả nhất.

https://thuthuat.taimienphi.vn/cach-dung-ham-sumif-trong-excel-636n.aspx Video hướng dẫn