How to use Sumif with ArrayFormula in filtered data in Google Sheets? This post answers this question in detail.

When you use the Sumif function with the ArrayFormula function in Google Sheets, you will get multiple row results.

A simple example explains this.

The above data range/array is A1:C10. The below criteria are in E1:E4.

The following Sumif with ArrayFormula in cell F2 would return the result shown under the “Formula” column in table # 2 above.

=ArrayFormula(sumif(A2:A10,E2:E4,C2:C10))

This way we can get the sum of the “Amount” column based on multiple conditions in multiple rows.

This Sumif with ArrayFormula usage in Google Sheets has already been featured in a few of my previous tutorials and here are them.

• How to Sumif When Multiple Criteria in the Same Column in Google Sheets.
• Multiple Criteria Sumif Formula in Google Sheets.

Not necessary to read the above two posts to understand this tutorial but maybe worth reading.

Sumif with ArrayFormula in Filtered Data

Now I am going to filter the table # 1. For that, select the data (A1:C10) then click Data > Create a filter.

Click the down-arrow that appears in cell B1 and un-check “Feb” and click the “OK” button.

Filter Has No Effect by Default?

After filtering out the data for the month of “Feb” (which is in column 2) as above, check whether any changes are there in the result in the above formula.

You won’t see any changes as Filter won’t affect formula results except the formulas that involve the Subtotal function. So the Sumif with ArrayFormula in filtered data has no effect on the result in Google Sheets.

To exclude the values in rows that are not visible from the Sumif total, there is an easy method which I have detailed here – SUMIF Excluding Hidden Rows in Google Sheets. But won’t work with the ArrayFormula as per our requirement above.

In this post, I am going to provide you a solution to only include visible rows in Sumif Array Formula use in Google Sheets. You may please follow the step by step instructions below.

Sumif ArrayFormula that Includes Visible Rows in Filtered Data (How-to)

In the below example I have a Sumif ArrayFormula in cell F2. When I filter column B for the months “Jan” and “Mar”, (filters out “Feb”) the formula adjusts the total accordingly.

I mean the Sumif ArrayFormula in cell F2 only includes visible rows. The values in the filtered out rows are not in the Sumif ArrayFormula total.

Even though we are using an ArrayFormula, a helper column is also required. Actually I have used column D as the helper column and hidden its values by changing the font color to white.

Please follow the “Steps” below for getting the helper column formula, F2 formula, and their explanations.

Steps

1. Take any column that comes in your data range. It can be a numeric column, number column, date column, or any column with values. But make sure that the column shouldn’t contain blank cells in the range. So I’m taking/considering column B here.
2. In cell D2 (helper column), insert the Subtotal formula =subtotal(103,B2) and drag-down to copy-paste it up to the last row in the range. That means the formulas should be in the range D2:D10 as per my data.
3. Insert the below Sumif ArrayFormula in cell F2 and voila!

=ArrayFormula(sumif(A2:A10&D2:D10,E2:E4&1,C2:C10))

Formula Explanation

Subtotal

The above Sumif formula heavily depends on the helper column D to exclude filtered out rows or you can say only include visible rows in the criteria based sum.

Regarding the helper column formulas, i.e. the Subtotal Count formulas in column D, it returns 1 in visible rows and 0 in filtered out rows.

You may replace 103 (function number in Subtotal) with 3 if you just want to include or exclude filtered rows. The function number 103 includes or excludes hidden rows by any means.

Sumif

We should make the Sumif formula to only sum values in the visible rows. That means if D1:D10 is greater than 0. How it’s possible?

Actually the Sumif function is only to use with single column criteria. For multiple columns, Sumifs is there. But the latter function won’t return an Array Result with the ArrayFormula function.

Related: Sumifs Array Formula Expanding Issue and Alternative Formulas.

The workaround here is to combine the additional column (helper column) in the Sumif range and adjust the criteria range accordingly. That’s what I’ve done in my Sumif with ArrayFormula in filtered data in Google Sheets.

Syntax:

SUMIF(range, criteria, sum_range)

Arguments as per the Formula:

range – A2:A10&D2:D10
criteria – E2:E4&1
sum_range – C2:C10

As per the example, rows 5, 6, and 7 are filtered out. So the values in A2:A10&D2:D10 (Sumif range) would be as follows.

The criteria E2:E4&1 are;

I hope that tells everything and you could understand how to use Sumif with ArrayFormula in filtered data in Google Sheets.

Thanks for the stay, enjoy!