Hàm SUMIF Trong Excel Và Cách Khắc Phục Một Số Lỗi Thường Gặp ...

Hàm SUMIF là một hàm hữu ích giúp chúng ta tính tổng các giá trị dựa trên một số điều kiện nhất định. Nhưng có những lúc bạn sẽ gặp khó khăn khi làm việc với hàm này. Bạn sẽ nhận thấy rằng hàm SUMIF không hoạt động bình thường hoặc trả về kết quả không chính xác.

Những sai sót này chủ yếu xảy ra khi bạn mới sử dụng hàm hàm SUMIF và chưa quen thuộc với nó. Thể nhưng, nhiều người dùng Excel lâu năm cũng gặp những lỗi tương tự. Có nhiều lý do khiếm hàm SUMIF không hoạt động chính xác.

Trong bài học Excel này, cùng Gitiho thảo luận về các lỗi thường gặp khi sử dụng hàm SUMIF và cách khắc phục.

Trước khi chúng ta thảo luận chi tiết về các điểm vấn đề với hàm SUMIF, hãy kiểm tra công thức SUMIF của bạn, xem có thể khắc phục lỗi nhanh chóng hay không.

Nếu SUMIF trả về lỗi # N/A hoặc bất kỳ lỗi nào khác, hãy đánh giá công thức. Có 80% cơ hội là bạn sẽ làm cho công thức của mình hoạt động. Để đánh giá công thức, hãy chọn ô công thức và chuyển đến tab Formula trong ribbon. Tại đây tìm tùy chọn Evaluate Formula, bên dưới phần Auditing.

Nếu bạn đang viết đúng công thức và khi bạn cập nhật trang tính, hàm SUMIF không trả về giá trị đúng. Hãy nhấn phím F9 để tải và tính toán lại trang tính.

Kiểm tra định dạng của các giá trị liên quan đến phép tính. Nếu bạn nhập dữ liệu từ nguồn khác, có rất có thể có định dạng không đúng trong bảng tính.

Nếu sau ba bước kiểm tra trên, hàm SUMIF của bạn vẫn không hoạt động. Hãy thử các biện pháp bên dưới.

XEM NHANH BÀI VIẾT

• 1 SUMIF không hoạt động do lỗi cú pháp
• 2 SUMIF không hoạt động do định dạng dữ liệu bất thường
• 3 Nếu SUMIF vẫn không hoạt động, hãy sử dụng SUMPRODUCT

SUMIF không hoạt động do lỗi cú pháp

Lỗi cú pháp là lỗi thường gặp ở những người dùng mới làm quen với Excel. Nhưng, ngay cả những người dùng có kinh nghiệm cũng có thể mắc lỗi cú pháp khi sử dụng hàm SUMIF. Vì vậy, trước tiên chúng ta hãy xem cú pháp của hàm SUMIF.

Công thức SUMIF chung trong Excel:

=SUMIF(condition_range,condition,sum range)

Như bạn có thể thấycondition_range là một phạm vi chứa điều kiện của bạn. Điều kiện sẽ chỉ được kiểm tra trong phạm vi này. condition là chính điều kiện. Đây là điều kiện mà bạn muốn kiểm tra trong condition_range. sum range là phạm vi thực tế bạn muốn thực hiện các phép cộng.

Nếu đối số sum_range bị bỏ qua, Excel cộng các ô được xác định trong đối số range (chính các ô đã được áp dụng tiêu chí).

Bây giờ chúng ta hãy xem những lỗi cú pháp bạn có thể mắc phải khi sử dụng hàm SUMIF.

Ngoài ra, bạn có thể xem thêm các bài viết này để hiểu rõ hơn về hàm SUMIFS

Hướng dẫn cách dùng hàm Sumifs đơn giản qua bài tập

Sai định dạng ngày tháng trong công thức

Hầu hết các lỗi gặp phải khi sử dụng Sumif đều do chúng ta xác định tiêu chí sai. Các tiêu chí trong SUMIF được xác định khác nhau trong các trường hợp khác nhau. Để hiểu rõ hơn, chúng ta hãy xem qua một số ví dụ.

Xem thêm: Những cách định dạng ngày, tháng, năm trong Excel chuẩn định dạng

Ở đây tôi có một tập dữ liệu.

Giả sử tôi cần tính tổng số lượng của ngày 1-Mar-13 với công thức này:

= SUMIF(A2:A20,1-mar-13,C2:C20)

Công thức SUMIF sẽ không hoạt động trong trường hợp này. Nó sẽ trả về 0. Tại sao lại như vậy?

• Dữ liệu chúng ta đang làm việc được định dạng là ngày tháng. Và ngày tháng trong Excel thực sự là một con số. Các số thực có thể được viết mà không có dấu ngoặc kép làm tiêu chí. Nhưng excel vẫn không trả về câu trả lời chính xác.
• Trên thực tế, SUMIF trong excel, chấp nhận ngày dưới dạng văn bản trong tiêu chí (nếu không được định dạng là số sê-ri). Vì vậy, nếu bạn viết một trong 3 công thức bên dưới, (số tương đương của 1-mar-13 của 41334) nó sẽ trả về câu trả lời chính xác.

= SUMIF (A2: A20, "1-mar-13", C2: C20) hoặc = SUMIF (A2: A20, "1-mar-2013", C2: C20) = SUMIF (A2: A20,41334, C2: C20)

Bạn cần đặc biệt cẩn thận khi làm việc với ngày tháng trong Excel. Chúng rất dễ lộn xộn. Vì vậy, nếu công thức của bạn bao gồm ngày tháng trong đó, hãy kiểm tra xem nó có đúng định dạng không. Đôi khi khi chúng ta nhập dữ liệu từ các nguồn khác, ngày tháng không được nhập ở các định dạng đúng. Vì vậy, đầu tiên bạn hãy kiểm tra và sửa định dạng ngày trước khi sử dụng.

Sử dụng toán tử so sánh sai trong hàm SUMIF

Hãy lấy một ví dụ khác. Lần này, chúng ta hãy tổng hợp số lượng các ngày muộn hơn 1-tháng 3-13. Đối với điều này, cú pháp chính xác là :

= SUMIF(A2:A20,"> 1-Mar-13",C2:C20)

Dấu > phải nằm trong ngoặc kép.

Bây giờ nếu tiêu chí nằm trong một số ô, giả sử trong F3, bạn sẽ sử dụng hàm SUMIF như sau:

= SUMIF (A2: A20,">" &F3,C2:C20)

Khi bạn cần tính tổng các giá trị nếu một giá trị khớp chính xác trong phạm vi tiêu chí, bạn không cần sử dụng dấu "=". Bạn chỉ cần viết giá trị đó hoặc cung cấp tham chiếu của giá trị đó tại vị trí của tiêu chí, như chúng ta đã làm trong ví dụ đầu tiên.

Tôi nhận thấy rằng một số người dùng mới sử dụng cú pháp dưới đây khi họ muốn có kết quả khớp chính xác.

= SUMIF (A2:A20,A2:A20=F3,C2:C20)

Công thức SUMIF này sẽ không hoạt động. Khi sử dụng tham chiếu làm tiêu chí cho các đối sánh chính xác, bạn chỉ cần đề cập đến tham chiếu. Công thức dưới đây sẽ tính tổng tất cả số lượng ngày được viết trong ô F3:

= SUMIF (A2:A20, F3,C2:C20)

Vậy là bạn đã biết cách sử dụng toán tử so sánh trong hàm Sumif. Chúng ta hãy xem thêm một vài lỗi thường gặp khác bên dưới. Bên cạnh đó, nếu bạn vẫn còn đang loay hoay hỏi Google về cách sử dụng các tính năng hay hàm trong Excel, hãy tham khảo ngay khóa học sau:

EXG01: Tuyệt đỉnh Excel | Khóa học Excel online từ cơ bản đến nâng cao

G-LEARNING 499,000đ 799,000đ Đăng ký Học thử

SUMIF không hoạt động do định dạng dữ liệu bất thường

Khi chúng ta nhập dữ liệu từ các nguồn khác, thường có các định dạng dữ liệu bất thường. Rất có thể có các số được định dạng dưới dạng văn bản. Trong trường hợp đó, các con số sẽ không được tính tổng. Xem ví dụ dưới đây.

Công thức Sumif khi dữ liệu có định dạng văn bản

Bạn có thể thấy rằng phạm vi tổng chứa các giá trị văn bản thay vì số. Và vì các giá trị văn bản không thể được tính tổng, kết quả chúng ta nhận được là 0.

Có thể phạm vi của bạn chứa các định dạng hỗn hợp. Có thể chỉ có một số số được định dạng dưới dạng văn bản và số còn lại là số. Trong trường hợp đó, các số được định dạng văn bản đó sẽ không được tổng hợp và bạn sẽ nhận được kết quả không chính xác.

Để khắc phục sự cố này, hãy chọn một ô có chứa số và văn bản và CTRL + SPACE để chọn toàn bộ cột. Bây giờ hãy nhấp vào dấu chấm than nhỏ ở bên trái của ô. Ở đây bạn sẽ thấy một tùy chọn Convert to numbers ở vị trí thứ hai. Nhấp vào nó và bạn sẽ có tất cả các số trong phạm vi đã chọn được chuyển đổi sang định dạng số.

Nhưng nếu bạn không thể chuyển đổi định dạng từ văn bản sang số theo cách này. Hãy sử dụng hàm VALUE để chuyển văn bản thành số. Hàm VALUE có thể chuyển bất kỳ chuỗi nào thành số (bao gồm cả ngày và giờ).

Đầu tiên, bạn cần sử dụng một cột để chuyển đổi dữ liệu của bạn sang định dạng số cách sử dụng hàm VALUE và sau đó dán giá trị đó vào, trước khi sử dụng nó trong công thức.

Công thức Sumif khi tính tổng thời gian

Bạn có thể sử dụng SUMIF để tính tổng thời gian không? Câu trả lời là có, nhưng rất nhiều người dùng không thể tính chính xác tổng thời gian với Sumif. Hãy quan sát ví dụ bên dưới nhé.

Ở đây tôi có thời gian ở định dạng HH: MM: SS. Và tôi muốn tổng hợp số giờ của ngày 1 tháng 3 năm 13. Vì vậy, tôi sử dụng công thức:

= SUMIF (A2:A20, F3, C2:C20)

Công thức là hoàn toàn chính xác nhưng câu trả lời tôi nhận được có vẻ không đúng.

Tại sao chúng ta nhận được 0,5. Lẽ ra kết quả phải là 12:00:00. Nó không chính xác? Không phải, kết quả như trên là đúng, vấn đề ở đây chỉnh là cách viết mà thôi. Như chúng ta đã thảo luận trước đó, ngày và giờ trong Excel được xử lý khác nhau.

Trong Excel, 1 giờ bằng 1/24 đơn vị. Vì vậy, 12 giờ sẽ bằng 0,5. Nếu bạn muốn xem kết quả theo giờ, chỉ cần chuyển đổi định dạng ô của G3 sang định dạng thời gian.

Nhấp chuột phải vào ô cần định định dạng. Tại đây, bạn chọn định dạng thời gian. Bạn có thể thấy rằng kết quả được chuyển đổi sang định dạng chính xác và trả về một kết quả dễ hiểu.

Nếu SUMIF vẫn không hoạt động, hãy sử dụng SUMPRODUCT

Nếu vì bất kỳ lý do gì, hàm SUMIF không hoạt động, bất kể bạn làm gì, hãy sử dụng một công thức thay thế. Ở đây công thức này sử dụng hàm SUMPRODUCT.

Ví dụ, nếu bạn muốn làm tính tổng như các ví dụ trên, chúng ta có thể sử dụng hàm SUMPRODUCT để thay thế. Để tính tổng phạm vi D2: D20 nếu ngày bằng F3, hãy viết công thức này.

= SUMPRODUCT(D2:D20, - (A2:A20 = F3))

Thứ tự của các biến không quan trọng. Công thức dưới đây sẽ hoạt động hoàn toàn tốt:

= SUMPRODUCT(-(A2:A20 = F3), D2:D20)

hoặc cái này,

= SUMPRODUCT (- (F3 = A2:A20), D2:D20)

Hàm SUMPRODUCT rất linh hoạt và đa năng chức năng, có thể giúp bạn dễ dàng tính tổng theo điều kiện.

Hy vọng bài viết này đã giúp bạn giải quyết vấn đề hàm SUMIF không hoạt động và các lựa chọn thay thế. Bên cạnh đó, để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác, hãy theo dõi Gitiho ngay hôm nay.