Ngoài lỗi hàm SUMIF không hoạt động, trả về kết quả không chính xác là một trong những lỗi mà người dùng hàm Excel thường gặp phải. Trong quá trình sử dụng hàm để tính tổng giá trị, người dùng có thể sẽ gặp phải một số lỗi khác.
Hàm SUMIF trong Excel được sử dụng để tính tổng các giá trị dựa trên một số điều kiện cụ thể. Trong quá trình sử dụng hàm Excel này bạn có thể sẽ phải đối mặt với một số lỗi xảy ra.
Sửa lỗi hàm Sumif trong Excel thường gặp
Vậy đó những lỗi nào, cách xử lý ra sao? Mời các bạn cùng theo dõi nội dung dưới đây của chúng tôi
Lỗi thường gặp khi dùng hàm SUMIF trong Excel
1. Lỗi cú pháp hàm SUMIF Excel không hoạt động
Lỗi cú pháp hàm SUMIF Excel là lỗi phổ biến mà người dùng thường gặp phải, thậm chí là cả người dùng đã có nhiều kinh nghiệm cũng hay gặp phải lỗi này.
Về cơ bản, cấu trúc hàm SUMIF có dạng:
=SUMIF(condition_range,condition,sum range)
Trong đó:
- Tham số đầu tiên condition_range: Là vùng được chọn có chứa các ô điều kiện. Điều kiện của bạn sẽ chỉ được kiểm tra trong vùng này.
- Tham số thứ 2, condition: Là điều kiện kiểm tra cho tham số condition_range.
- sum range: Là phạm vi, vùng cần tính tổng.
Dưới đây là chi tiết các lỗi cú pháp hàm SUMIF Excel phổ biến mà người dùng thường gặp phải và cách sửa lỗi:
1.1. Định nghĩa sai tiêu chí
Các tiêu chí hàm SUMIF Excel được định nghĩa khác nhau trong các trường hợp khác nhau. Để dễ hình dung, chúng ta sẽ xem qua một số ví dụ dưới đây:
Ví dụ 1: Giả sử chúng ta có tập dữ liệu như dưới đây:
Yêu cầu đưa ra ở đây là tính tổng số lượng (quantity) trong ngày 1-mar-13, và sử dụng cú pháp hàm có dạng như dưới đây:
=SUMIF(A2:A20,1-mar-13,C2:C20)
Tuy nhiên trong trường hợp này cú pháp hàm SUMIF Excel trên sẽ trả về giá trị là 0. Vậy nguyên nhân là do đâu?
Dữ liệu chúng ta đang làm việc được định dạng dưới dạng dữ liệu ngày tháng. Trong Excel, dữ liệu ngày tháng phải được biểu diễn dưới dạng chữ số.
Tuy nhiên vì hàm SUMIF Excel chấp nhận cả dữ liệu định dạng dưới dạng văn bản làm tiêu chí, vì vậy ngay cả khi chúng ta sử dụng cú pháp như dưới đây, hàm cũng sẽ trả về giá trị:
=SUMIF(A2:A20,"1-mar-13",C2:C20)
Hoặc:
=SUMIF(A2:A20,"1-mar-2013",C2:C20)
Giá trị số tương đương của 1-mar-13 là 41334. Vì vậy nếu sử dụng cú pháp hàm dưới đây cũng trả về giá trị tương đương:
=SUMIF(A2:A20,41334,C2:C20)
Lưu ý: trong trường hợp này tiêu chí là số nên chúng ta không cần sử dụng dấu ngoặc kép.
Một lưu ý quan trọng là nếu hàm Excel bao gồm dữ liệu ngày tháng, chúng ta cần kiểm tra định dạng dữ liệu đã chính xác chưa. Đôi khi nguyên nhân gây lỗi hàm SUMIF không hoạt động, không trả về giá trị chính xác là do lđịnh dạng dữ liệu không chính xác.
1.2. Lỗi khi sử dụng toán tử so sánh trong hàm SUMIF Excel
Cho ví dụ, ở đây yêu cầu đưa ra là tính tổng số lượng các ngày sau ngày 1-Mar-13, và cú pháp hàm chuẩn phải có dạng:
=SUMIF(A2:A20,">1-Mar-13",C2:C20)
Mà không phải là:
=SUMIF(A2:A20,A2:A20>"1-Mar-13",C2:C20)
Dấu > phải nằm trong dấu ngoặc kép.
Giả sử nếu tiêu chí nằm trong một ô, chẳng hạn là ô F3, cú pháp hàm chuẩn có dạng như dưới đây:
=SUMIF(A2:A20,">"&F3,C2:C20)
Trong trường hợp này chúng ta sử dụng toán tử so sánh và toán tử này phải được đặt trong dấu ngoặc kép.
Lưu ý: Khi cần tính tổng giá trị, nếu một giá trị khớp trong phạm vi tiêu chí, chúng ta không cần sử dụng dấu "=", chỉ cần viết giá trị hoặc cung cấp tham chiếu của giá trị đó làm tiêu chí.
1.3. Lỗi hàm SUMIF không hoạt động do định dạng dữ liệu sai
Hàm Excel SUMIF sử dụng để tính tổng các giá trị là số. Do đó nếu hàm không hoạt động đúng cách, trước hết chúng ta cần kiểm tra phạm vi tổng và định dạng phù hợp hay chưa.
Đôi khi chúng ta nhập dữ liệu từ các nguồn khác nhau có thể sử dụng các định dạng dữ liệu khác nhau, thậm chí dữ liệu số có thể được định dạng dưới dạng văn bản, và gây ra lỗi hàm SUMIF.
Để khắc phục lỗi này, đầu tiên chọn một ô có chứa giá trị số và văn bản, nhấn Ctrl + Space để chọn toàn bộ cột. Tiếp theo, click chọn biểu tượng dấu chấm than nhỏ nằm góc bên trái ô. Trên màn hình sẽ hiển thị menu có chứa tùy chọn Convert to numbers. Click chọn tùy chọn này để chuyển đổi tất cả giá trị trong phạm vi, vùng đã chọn sang định dạng số.
Trong trường hợp nếu giải pháp trên không khả dụng, một cách khác là sử dụng hàm VALUE để chuyển đổi chuỗi định dạng văn bản thành chuỗi định dạng số.
Sau khi hoàn tất, dán giá trị vào công thức hàm SUMIF.
Cú pháp hàm SUMIF tính tổng thời gian
Trong một số trường hợp sử dụng hàm SUMIF để tính tổng thời gian bạn có thể sẽ gặp phải một số lỗi.
Cho ví dụ dưới đây: Giả sử chúng ta sử dụng định dạng thời gian: Giờ (HH) : phút (MM) : giây (SS). Yêu cầu đưa ta là tính tổng thời gian ngày 1-mar-13, và cú pháp hàm có dàng:
=SUMIF(A2:A20,F3,C2:C20)
Tuy nhiên hàm trên sẽ trả về kết quả không chính xác. Điều này là bởi vì giá trị ngày và giờ, thời gian trong Excel được xử lý khác nhau.
Trong Excel, 1 giờ tương đương 1/24 đơn vị (unit). Vì vậy 12 giờ tương đương 0.5.
Để tính tổng thời gian, chúng ta sẽ phải thao tác thêm bước chuyển đổi định dạng ô G3 sang định dạng thời gian.
Kích chuột phải vào ô có giá trị cần định dạng, và chọn định dạng thời gian. Lúc này hàm SUMIF sẽ trả về kết quả chính xác.
1.4. Sử dụng hàm SUMPRODUCT nếu hàm SUMIF vẫn không hoạt động
Trong trường hợp nếu hàm SUMIF vẫn không hoạt động, chúng ta sẽ sử dụng hàm SUMPRODUCT để thay thế.
Ví dụ, giả sử yêu cầu đưa ra là tính tổng giá trị trong phạm vi D2:D20 nếu ngày bẳng F3, chúng ta sẽ sử dụng hàm SUMPRODUCT thay thế và công thức có dạng như dưới đây:
=SUMPRODUCT(D2:D20,--(A2:A20=F3))
Vị trí, thứ tự các biến trong hàm SUMPRODUCT không quan trọng, công thức dưới đây cũng trả về kết quả tương tự:
=SUMPRODUCT(--(A2:A20=F3),D2:D20)
Hoặc:
=SUMPRODUCT(--(F3=A2:A20),D2:D20)
2. Lỗi VALUE khi sử dụng hàm SUMIF Excel
Lỗi VALUE xảy ra là do công thức có chứa hàm SUMIF, COUNTIF hoặc COUNTBLANK tham chiếu đến các ô trong Workbook làm việc đã đóng. Để khắc phục lỗi VALUE này, chúng ta sẽ sử dụng kết hợp các hàm SUM và IF trong 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ông thức mảng hoạt động trên 2 hoặc nhiều bộ dữ liệu được gọi là tham số mảng.
Ví dụ: trong ví dụ này yêu cầu là tính tổng doanh thu sản phẩm thủy sản. Kết quả sẽ được thêm trong Workbook báo cáo và dữ liệu nguồn nằm trong Workbook dữ liệu.
Thực hiện theo các bước dưới đây (áp dụng trên cả Excel 2010, 2013, 2016):
Bước 1: Đầu tiên mở workbook có chứa dữ liệu nguồn (Workbook dữ liệu).
Bước 2: Mở Workbook có chứa công thức (Workbook báo cáo).
Bước 3: Chọn ô C5 trong Workbook báo cáo.
Bước 4: Click chọn nút FX trên thanh công thức tìm hàm SUM.
Bước 5: Tiếp theo trên thanh công thức, tại memu Name Box, tìm và click chọn IF để lồng hàm IF với hàm SUM.
Bước 6: Nếu hàm IF không hiển thị, click chọn tùy chọn More Functions (thêm hàm) và chọn hàm IF.
Bước 7: Nhập các đối số dưới đây:
Logical_test : Data.xlsx!$A$23:$A$30="Seafood".
Value_If _true: SUM(Data.xlsx!$D$23:$D$30).
Value_if_false: 0.
Bước 8: Nhấn Ctrl + Shift + Enter để hoàn tất công thức mảng.
Bước 9: Nếu trên màn hình hiển thị thông báo yêu cầu sửa công thức, clicl chọn Yes để tiếp tục.
Bằng cách sử dụng phương pháp này để tránh gặp phải các lỗi giá trị khi làm việc với Workbook không mở.
3. Lỗi chuỗi ký tự trong tiêu chí nhiều hơn 255 ký tự
Các hàm SUMIF và SUMIFS có thể trả về kết quả không chính xác nếu chuỗi ký tự dài hơn 255 ký tự.
Giải pháp để sửa lỗi trong trường hợp này là rút ngắn chuỗi ký tự. Cách đơn giản nhất để rút ngắn chuỗi ký tự là sử dụng hàm CONCATENATE hoặc toán tử (&) để chia nhỏ giá trị thành nhiều chuỗi.
Ví dụ:
=SUMIF(B2:B12,"chuoi dai"&"chuoi dai khac")
https://thuthuat.taimienphi.vn/loi-thuong-gap-khi-su-dung-ham-sumif-trong-excel-61845n.aspx
Trên đây là tổng hợp một số lỗi thường gặp khi sử dụng hàm SUMIF trong Excel và cách sửa lỗi. Ngoài ra bạn đọc có thể tham khảo thêm một số bài viết đã có trên Taimienphi.vn để tìm hiểu thêm cách sửa lỗi hàm Sum không cộng được trong Excel nhé.
- Xem thêm: Sửa lỗi hàm Sum không cộng được trong Excel