Without using any helper column, we can use the Subtotal function with conditions in Excel and Google Sheets in a filtered dataset.

Here is a new Excel vs. Google Sheets formula comparison.

It is not a function comparison between Excel and Google Sheets but a formula comparison.

In this particular formula comparison that involves a combination formula, I see the Excel formula is better than the formula in Google Sheets.

Sheets require the help of Lambda to spill results, whereas Excel doesn’t need it.

We cannot exclude filtered out or hidden rows in conditional functions like Countif, Sumif, Minifs, Maxifs, and Averageif in Google Sheets and Excel.

The only function that works on filtered data is Subtotal. It works equally well on data that is hidden. So we must make use of it in all possible ways.

Introduction to Subtotal Function in Excel and Google Sheets

The Subtotal is a versatile function that subtotals a range with specific functions.

That specific function list includes the below functions and represents them in Subtotal with function numbers.

Function Code/Number in Subtotal in Excel and Google Sheets are the same.

Function # Excludes Filtered Out Rows	Function # Excludes All Hidden Rows
AVERAGE 1	AVERAGE 101
COUNT 2	COUNT 102
COUNTA 3	COUNTA 103
MAX 4	MAX 104
MIN 5	MIN 105
PRODUCT 6	PRODUCT 106
STDEV 7	STDEV 107
STDEVP 8	STDEVP 108
SUM 9	SUM 109
VAR 10	VAR 110
VARP 11	VARP 111

In the below examples, I am using some of the function numbers from the above list.

Comparison to the Use of Subtotal Function With Conditions in Excel and Google Sheets

First, let me share with you how to use Subtotal to count, sum, min, max, and average in Excel and as well as in Google Sheets.

Then you can learn how to use conditions in the Subtotal function in Excel and Google Sheets.

The function Subtotal works similarly in Excel and Google Sheets. The below formula examples work in both the said two popular Spreadsheet applications.

Screen Capture # 1 from Excel:

I have only included a few aggregate functions in the above Subtotal formulas. The reason I am detailing the use of conditions only in those functions.

Just filter the data or hide rows to see the formula results vary as the Subtotal formulas present above only aggregate the values in the visible rows.

Criteria in Subtotal in Excel and Google Sheets – Helper Column Approach

Conditions/Criteria in Subtotal reminds me of the possibility of a new SubtotalIf function.

But unfortunately, at present, there is no such function in Excel or Google Sheets. Let me come to the point.

How to prepare a helper column for Subtotal If or you can say to use conditions in Subtotal?

Screen Capture # 2 from Excel:

In my above example, column D is my helper column.

In that column, apply the below Subtotal formula in cell D2 and drag it down to fill until cell D15.

=SUBTOTAL(109,C2)

Note:- Use =SUBTOTAL(103,C2) if column C is text type, not numeric.

To test the purpose of the above helper column, in cell F2 key in the following formula.

=D5

It will return the value 200, which is the value in cell D5, right?

Now select row # 5, right-click, and from the shortcut menu, select Hide to hide the row.

You can see that the formula in cell F2 now returns 0!

That means when you hide any row, the value in column D in that row will be 0.

It will help you conditionally aggregate values in column C.

I’ll explain how. Before that, you must see how I am aggregating columns without the function Subtotal and excluding hidden/filter rows in Excel and Google Sheets.

Yes, once you have a helper column as above, you can use aggregate functions in Google Sheets and Excel without the Subtotal and aggregate function numbers.

Below is the replacement of the Subtotal formulas provided in the first screenshot, except for counta.

Further, below each of them, you can see one additional formula as an example of the Subtotal function with conditions in Excel and Google Sheets.

Averageif Visible Rows in Excel and Google Sheets

Formula:

=AVERAGEIF(D2:D15,">0",C2:C15)

This formula would return the average value of the numbers in the range C2:C15 but for only the visible rows.

Take a look at the first screenshot. There you can see the Subtotal formula with function # 101. It’s equivalent to that.

Now let us see how to include conditions in Subtotal. See the below Subtotal If Formula equivalent that contains criterion/condition.

=AVERAGEIFS(C2:C15,D2:D15,">0",A2:A15,"Ryan")

The above two and the following formulas work in Excel and Google Sheets. Both formulas calculate averages in different ways.

While the first formula finds the average of the visible rows, the second formula further checks whether the given criterion matches column A.

Countif Visible Rows in Excel and Google Sheets

Formula:

=COUNTIF(D2:D15,">0")

The Subtotal If Formula (Equivalent) with Conditions:

=COUNTIFS(D2:D15,">0",A2:A15,"Ryan")

This formula counts the name “Ryan” but only the visible names.

Please refer to my Google Sheets Functions Guide if you have any queries regarding any of the functions mentioned in this tutorial.

Minifs Visible Rows in Excel and Google Sheets

Formula:

=MINIFS(C2:C15,D2:D15,">0")

The Subtotal If Formula (Equivalent) with Conditions:

=MINIFS(C2:C15,D2:D15,">0",A2:A15,"Ryan")

This formula is an example of the use of Minifs in Google Sheets and Excel, excluding hidden or filtered-out rows.

It returns the min of visible rows for the names in column A, i.e., “Ryan.”

Maxifs Visible Rows in Excel and Google Sheets

Formula:

=MAXIFS(C2:C15,D2:D15,">0")

It follows the same Minifs approach in visible rows.

=MAXIFS(C2:C15,D2:D15,">0",A2:A15,"Ryan")

Sumif Visible Rows in Excel and Google Sheets

Formula:

=SUMIF(D2:D15,">0",C2:C15)

The Subtotal Formula Alternative with Conditions:

=SUMIFS(C2:C15,D2:D15,">0",A2:A15,"Ryan")

Sumif and Sumifs are two popular worksheet functions similar to Vlookup.

Sumif/Sumifs, by default, can’t conditionally sum visible rows. 

The above helper column approach helps to achieve that in Excel and Google Sheets.

Conditions in Subtotal in Excel Without Helper Column (Non-Lambda Approaches)

The below formula would only work in Excel. Even if you find it works in Google Sheets without any visible formula parse error, that output may not be correct.

We can use the Subtotal function with conditions/criteria in Excel without any helper column.

In Excel, we can generate the above helper column with the help of offset rows.

You can understand one thing if you scroll up and see the formulas in the helper column.

In each cell, there is a subtotal formula. The same we can generate virtually using the below one-line piece of code.

=SUBTOTAL(109,OFFSET(C2,ROW(C2:C15)-ROW(C2),0)

Can you explain the logic?

Yes! It’s just like entering the below formula in helper column cell D2 and then dragging it down.

=SUBTOTAL(109,OFFSET(C2,0,0))

Screen Capture # 3 from Excel:

In Excel, we can turn this offset formula into an Array Formula by using Sumproduct or the Ctrl+Shift+Enter legacy array method depending on the version of Excel you use.

The Ctrl+Shift+Enter may not be required in Excel 365.

So the helper column is not required in Excel. That means the Subtotal function with conditions in Excel doesn’t require a helper column.

Formula Examples to Criteria Usage in Subtotal in Excel Without Helper Column

To sum visible rows with conditions in another column, or you can say Sumif Visible Rows with Criteria in Excel, you can use my below formula.

Formula:

=SUMPRODUCT((A2:A15="Marshall")*(SUBTOTAL(109,OFFSET(C2,ROW(C2:C15)-ROW(C2),0))))

It doesn’t use any helper column.

The following formula counts visible rows with conditions in another column.

The Formula to Countif Visible Rows with Criteria in Excel:

=SUMPRODUCT((A2:A15="Marshall")*(SUBTOTAL(103,OFFSET(C2,ROW(C2:C15)-ROW(C2),0))))

You can use the following Excel formula to find the max value in a column in the visible rows based on conditions in another column.

It must be entered as an Array Formula using Ctrl+Shift+Enter since there is no Sumproduct with it.

The Formula to Maxifs Visible Rows with Condition in Excel:

=MAX(IF(A2:A15="Marshall",SUBTOTAL(109,OFFSET(C2,ROW(C2:C15)-ROW(C2),0)),""))

The below formula must also be entered as Array Formula using Ctrl+Shift+Enter.

Screen Capture # 4 from Excel:

Minifs Visible Rows with Criteria in Excel:

=MIN(IF(A2:A15="Marshall",SUBTOTAL(109,OFFSET(C2,ROW(C2:C15)-ROW(C2),0)),""))

Averageif Visible Rows with Criteria in Excel:

You must enter this formula as an array formula.

It finds the average of visible rows without a helper column. Also, it checks the condition in another column.

=AVERAGE(IF(A2:A15="Marshall",IF(SUBTOTAL(109,OFFSET(C2,ROW(C2:C15)-ROW(C2),0,1))>0,C2:C15)))

In the last five formulas, treat the first two as regular formulas.

The last three formulas must be entered as array formulas.

There are only slight differences in the formulas. The logic of offset rows in subtotal is the same in all five.

New Changes Both in Excel and Google Sheets (Non-Helper Lambda Approaches)

A lot of water has flown under the bridge. Now it’s easy to use Subtotal with conditions in Excel and Google Sheets without a helper column.

All thanks go to the Lambda helper functions. Here is an example.

You May Like: SUMIF Excluding Hidden Rows in Google Sheets [Without Helper Column].

The same approach will work in Excel also.

That’s all about how to use the Subtotal function with conditions in Excel and Google Sheets. I hope you have enjoyed your stay!