Trust me, I have a very useful Sumif formula to share with you! If you frequently use the SUMIF function in Google Sheets, you’re going to love this tutorial and formula. Let’s learn how to include adjacent blank cells in the SUMIF range in Google Sheets.
I bet you’ve never seen a SUMIF formula like this before! I’m excited to share it with you, so keep reading.
As you may know, the SUMIF function in Google Sheets takes a range, criterion (criteria with ArrayFormula), and a sum_range as arguments. This allows us to conditionally sum across a range.
But what happens when the range contains blank cells but the sum_range has values? Do those values get included in the conditional sum?
For example, the formula =sumif(A2:A9,"",C2:C9) would return 7000.00 based on the sample data. It sums the values in column C where column A contains blanks. However, that’s not what we want.
We want the blank cells in column A (range) to take the value from the non-blank cells above in the SUMIF formula. We don’t want to use a blank as a criterion.
Include All the Values in sum_range Even if the range Contains Blank Cells in SUMIF
In the following example, cells A3, A5, A6, and A9 are blank, but corresponding cells C3, C5, C6, and C9 contain values.
If we use the normal SUMIF formula =sumif(A2:A9,"Kim Robinson",C2:C9), it would only return the value 4500. This is because the formula only finds the criterion “Kim Robinson” in cell A2, so it returns the value from cell C2.
But what if we want the formula to also consider cell A3 as “Kim Robinson” and return the total of cells C2 and C3, which is 9000? We can achieve this with multiple criteria in SUMIF.
In this tutorial, I’ll show you my SUMIF array formula in cell F3, which takes multiple criteria in E3:E6. The formula considers the blank cells in the range A2:A9 to have values from the non-blank cells above. It then sums the sum_range C2:C9 accordingly.
Before we proceed, let me show you one more image. In this example, the range and criterion refer to the same data range (A2:A9). I want the result in a total column (column D, cell D2), so I don’t want to specify the criteria separately.
If you compare both screenshots, you can see the difference. In the first example, I specified the SUMIF criteria separately, but in the second example, I didn’t.
My formula for including adjacent blank cells in the SUMIF range will be the same for both examples. You only need to change the criteria reference in the formula.
Formula to Include Adjacent Blank Cells in Sumif Range in Google Sheets
Instead of diving straight into the formula explanation, let me share the formulas first.
Formula #1 used in example 1 in cell F3 (Screenshot #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),))
Formula #2 used in example 2 in cell D2 (Screenshot #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),))
The differences between these formulas are minimal. I only changed the criteria range from E3:E to A2:A in the second example. Also, to limit the formula output to non-blank cells in the criteria range, I used the LEN function.
I adjusted the LEN function according to the criteria range. In the first formula, it’s len(E3:E), and in the second formula, it’s len(A2:A).
Now, let’s focus on the most important part of this tutorial: explaining the second formula (Screenshot #2).
SUMIF Formula #2 Explanation
I’ll easily help you understand the second formula. First, enter this SUMIF array formula in cell D2:
=ArrayFormula(if(len(A2:A),sumif(A2:A,A2:A,C2:C),))
The above SUMIF formula skips the highlighted cells in the total since the corresponding cells in column A contain blanks.
To include values corresponding to the blank cells in the total in SUMIF, replace the range A2:A with a virtual range. Simply replace A2:A in the formula above with the following formula:
lookup(row(A2:A),row(A2:A)/if(A2:A<>"",TRUE,FALSE),A2:A)
Refer to the image below to understand how to make the necessary changes in SUMIF.
This Lookup formula fills the blank cells in the SUMIF range with values from the cells above.
If you’d like a more detailed tutorial on this Lookup formula, which is the backbone of the above SUMIF formula, check out this tutorial on Crawlan.com titled “Array Formula to Fill Blank Cells With the Values Above in Google Sheets.”
That’s all about how to include adjacent blank cells in the SUMIF Range in Google Sheets. Thanks for reading. Enjoy!
SUMIF Advanced Tutorials:
- 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.
