Hãy tin tôi, tôi sẽ chia sẻ với bạn một công thức Sumif rất hữu ích! Nếu bạn thường xuyên sử dụng hàm SUMIF trong Google Sheets, bạn sẽ rất thích bài hướng dẫn này và công thức. Hãy xem cách bao gồm các ô trống kề cạnh trong phạm vi SUMIF trong Google Sheets.
Tôi chắc chắn rằng bạn chưa từng thấy một công thức SUMIF như vậy trước đây! Tôi rất vui mừng được chia sẻ công thức này với bạn. Hãy tiếp tục đọc.
Như bạn có thể biết, hàm SUMIF trong Google Sheets nhận một phạm vi, tiêu chí (tiêu chí sử dụng ArrayFormula) và một phạm vi_tổng làm đối số.
SUMIF(phạm_vi, tiêu_chí, [phạm_vi_tổng])
Điều này giúp chúng ta tính tổng có điều kiện (tiêu chí) trên một phạm vi (phạm_vi).
Đôi khi phạm vi có thể chứa các ô trống nhưng phạm_vi_tổng có giá trị. Nếu vậy, giá trị như vậy trong tổng có điều kiện sẽ như thế nào?
=sumif(A2:A9,””,C2:C9)
Công thức này dựa trên dữ liệu mẫu dưới đây (ảnh chụp màn hình số 1). Nó sẽ trả về 7000.00 vì tiêu chí là trống “”.
Đây là tổng của các giá trị trong cột C khi cột A chứa các ô trống. Nhưng tôi không muốn điều này.
Tôi muốn các ô trống trong cột A (phạm_vi) lấy giá trị từ các ô không trống phía trên trong SUMIF. Tôi không muốn sử dụng ô trống làm tiêu chí.
Bao gồm tất cả các giá trị trong phạm_vi_tổng ngay cả khi phạm_vi chứa các ô trống trong SUMIF
Hãy xem ví dụ đơn giản dưới đây:
Ở ví dụ này, như bạn có thể thấy ô A3, A5, A6 và A9 là trống nhưng các ô tương ứng C3, C5, C6 và C9 chứa giá trị.
Công thức SUMIF thông thường dưới đây sẽ trả về giá trị 4500. Bởi vì công thức chỉ tìm thấy tiêu chí “Kim Robinson” trong ô A2. Vì vậy nó trả về giá trị từ ô C2.
=sumif(A2:A9,”Kim Robinson”,C2:C9)
Tôi muốn công thức đọc ô A3 như là “Kim Robinson” và trả về tổng của ô C2 và C3 là 9000. Tương tự, tôi muốn làm với nhiều tiêu chí trong SUMIF.
Công thức mảng SUMIF của tôi trong ô F3 lấy các tiêu chí nhiều trong ô E3:E6 (hãy xem ảnh phía trên). Công thức xem xét các ô trống trong phạm vi A2:A9 có giá trị từ các ô không trống phía trên. Nó tính tổng phạm_vi_tổng C2:C9 tương ứng. Bạn sẽ nhận được công thức đó trong bài hướng dẫn này.
Trước khi tiếp tục, tôi sẽ cho bạn xem thêm một hình ảnh nữa. Trong đó, tôi sẽ không chỉ định các tiêu chí một cách riêng biệt. Trong ví dụ trước (ảnh chụp màn hình số 1), phạm vi tiêu chí là E3:E6.
Ở đây, phạm vi và tiêu chí đều trỏ đến cùng một dải dữ liệu là A2:A9. Trong đó, tôi muốn kết quả ở một cột tổng (cột D, ô D2). Vì vậy tôi không muốn chỉ định các tiêu chí một cách riêng biệt.
Nếu bạn so sánh cả ảnh chụp màn hình số 1 và 2, bạn sẽ hiểu sự khác biệt. Trong ví dụ đầu tiên, tôi đã chỉ định các tiêu chí SUMIF một cách riêng biệt nhưng trong ví dụ thứ hai, tôi không làm như vậy.
Công thức của tôi bao gồm các ô trống kề cạnh trong phạm vi SUMIF sẽ giống nhau cho cả hai ví dụ. Bạn chỉ cần thay đổi tham chiếu tiêu chí trong công thức.
Công thức để bao gồm các ô trống kề cạnh trong phạm vi Sumif trong Google Sheets
Thay vì diễn giải công thức, ở đây, tôi sẽ chia sẻ các công thức trước.
Công thức 1 được sử dụng trong ví dụ 1 trong ô F3 (ảnh chụp màn hình số 1):
=ArrayFormula(if(len(E3:E),sumif(lookup(row(A2:A),row(A2:A)/if(A2:A<>"",TRUE,FALSE),A2:A),E3:E,C2:C),))Công thức 2 được sử dụng trong ví dụ 2 trong ô D2 (ảnh chụp màn hình số 2):
=ArrayFormula(if(len(A2:A),sumif(lookup(row(A2:A),row(A2:A)/if(A2:A<>"",TRUE,FALSE),A2:A),A2:A,C2:C),))Như tôi đã nói, sự khác biệt trong các công thức này là tối thiểu. Tôi đã thay đổi phạm vi tiêu chí E3:E thành A2:A trong ví dụ thứ hai. Ngoài ra, để đưa ra đầu ra công thức giới hạn trong các ô không trống trong phạm vi tiêu chí, tôi đã sử dụng hàm LEN.
Tôi đã điều chỉnh hàm LEN theo phạm vi tiêu chí. Tức là, nó là len(E3:E) trong công thức đầu tiên và len(A2:A) trong công thức thứ hai.
Bây giờ tôi sẽ chú ý của bạn vào phần quan trọng nhất của hướng dẫn này, đó là giải thích công thức trên. Vì vậy, tôi sẽ lấy công thức thứ hai (vui lòng tham khảo ảnh chụp màn hình số 2).
Giải thích Công thức SUMIF thứ 2
Tôi có thể dễ dàng làm cho bạn hiểu công thức 2 ở trên. Trước tiên, hãy điền công thức mảng SUMIF này vào ô D2.
=ArrayFormula(if(len(A2:A),sumif(A2:A,A2:A,C2:C),))
Công thức SUMIF trên bỏ qua các ô được tô sáng trong tổng do các ô tương ứng trong cột A chứa các ô trống.
Vì vậy, để bao gồm các giá trị tương ứng với các ô trống trong tổng SUMIF, hãy thay thế phạm vi A2:A bằng một phạm vi ảo. Nghĩa là bạn chỉ cần thay thế A2:A trong công thức trên bằng công thức dưới đây.
lookup(row(A2:A),row(A2:A)/if(A2:A<>"",TRUE,FALSE),A2:A)Hãy xem hình ảnh dưới đây để hiểu cách thực hiện các thay đổi cần thiết trong SUMIF.
Công thức Lookup này điền các ô trống trong phạm vi SUMIF bằng các giá trị từ các ô phía trên.
Đây là một hướng dẫn rất chi tiết về công thức Lookup cụ thể này, đó là nền tảng của công thức SUMIF trên – Công thức mảng để điền các ô trống với các giá trị từ các ô phía trên trong Google Sheets.
Đó là tất cả về cách bao gồm các ô trống kề cạnh trong phạm vi SUMIF trong Google Sheets. Cám ơn bạn đã đọc. Chúc bạn vui vẻ!
Các hướng dẫn nâng cao về SUMIF:
- Sumif Multiple Columns Criteria – It Works in Google Sheets.
- Multiple Criteria Sumif Formula in Google Sheets.
- SUMIF to Sum By Current Work Week in Google Sheets.
- How to Include Multiple Sum Columns in SUMIF in Google Sheets.
- How to Use Dynamic Ranges in SUMIF Formula in Google Sheets.
- SUMIF Excluding Hidden Rows in Google Sheets.
- How to Sumif When Multiple Criteria in the Same Column in Google Sheets.
- How to Sum Every Nth Row in Google Sheets Using SUMIF.
- MMULT Instead of SUMIF in Google Sheets for Array Result.
- How to Do a Case Sensitive SUMIF in Google Sheets.
