Trust me I am going to share with you a very useful Sumif formula! If you are frequently using the SUMIF function in Google Sheets you are going to love this tutorial and the formula. See how to include adjacent blank cells in SUMIF range in Google Sheets.

I am sure you may not have seen such a SUMIF formula before! I am excited to share this formula with you. Please read on.

As you may know, the SUMIF function in Google Docs Sheets takes a range, criterion (criteria with ArrayFormula) and a sum_range as arguments.

SUMIF(range, criterion, [sum_range])

This helps us to conditional (criterion) sum (sum_range) across a range (range).

Sometimes the range may contain blank cells but the sum_range may have values. If so, what happens to such values in the conditional sum?

=sumif(A2:A9,"",C2:C9)

This formula is based on the sample data below (screenshot # 1). It would return 7000.00 as the criterion is blank "".

It’s the total of the values in column C where column A contains blanks. But I don’t want this.

I want the blank cells in column A (range) to take value from the non-blank cells above in SUMIF. I 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

Screenshot # 1:

Here in this example, as you can see cell A3, A5, A6, and A9 are blank but corresponding cells C3, C5, C6, and C9 contain values.

The below normal SUMIF formula would return the value 4500. Because the formula only finds the criterion “Kim Robinson” in cell A2. So it returns the value from cell C2.

=sumif(A2:A9,"Kim Robinson",C2:C9)

I want the formula to read the cell A3 too as “Kim Robinson” and return the total of the cell C2 and C3 which is 9000. The same I want with multiple criteria in SUMIF.

My SUMIF array formula in cell F3 takes the multiple criteria in E3:E6 (please see the image above). The formula considers the blank cells in the range A2:A9 have values from the non-blank cells above. It sums the sum_range C2:C9 accordingly. You will get that formula in this tutorial.

Before proceeding, I am going to show you one more image. In that, I am not going to specify the criteria separately. In the earlier example (screenshot # 1) the criteria range is E3:E6.

Here the range and criterion referring to the same data range that is A2:A9. In this, I want the result in a total column (column D, cell D2). So I don’t like to specify the criteria separately.

Screenshot # 2:

If you compare both the screenshot 1 and 2 you can understand the difference. In the first example, I have specified the SUMIF criteria separately but in the second example, it’s not.

My formula that includes adjacent blank cells in SUMIF range will be the same for both the 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 going to the formula explanation, here I am going to 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),))

As I have said the differences in these formulas are minimal. I have changed the criteria range E3:E to A2:A in the second example. Also to make the formula output to limit to the non-blank cells in the criteria range, I have used the LEN function.

I have adjusted the LEN as per the criteria range. I mean, it’s len(E3:E) in the first formula and len(A2:A) in the second formula.

Now I am taking your attention to the most important part of this tutorial that is explaining the above formula. For that, I am going the take the second formula (please refer the screenshot # 2).

SUMIF Formula # 2 Explanation

I can easily make you understand the formula 2 above. First of all, 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.

So to include values corresponding to the blank cells in the total in SUMIF, replace the range A2:A with a virtual range. I mean you simply replace A2:A in the formula above with the formula below.

lookup(row(A2:A),row(A2:A)/if(A2:A<>"",TRUE,FALSE),A2:A)

See 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 the values from the cells above.

Here is a very detailed tutorial on this particular Lookup formula, which is the backbone of the above SUMIF formula – Array Formula to Fill Blank Cells With the Values Above in Google Sheets.

That’s all about how to include adjacent blank cells in SUMIF Range in Google Sheets. Thanks for the stay. Enjoy!

SUMIF Advanced Tutorials:

1. Sumif Multiple Columns Criteria – It Works in Google Sheets.
2. Multiple Criteria Sumif Formula in Google Sheets.
3. SUMIF to Sum By Current Work Week in Google Sheets.
4. How to Include Multiple Sum Columns in SUMIF in Google Sheets.
5. How to Use Dynamic Ranges in SUMIF Formula in Google Sheets.
6. SUMIF Excluding Hidden Rows in Google Sheets.
7. How to Sumif When Multiple Criteria in the Same Column in Google Sheets.
8. How to Sum Every Nth Row in Google Sheets Using SUMIF.
9. MMULT Instead of SUMIF in Google Sheets for Array Result.
10. How to Do a Case Sensitive SUMIF in Google Sheets.