Is there a way to not include data from hidden rows in the following formula?

[HTTR21]

Is there a way to not include data from hidden rows in the following formula? Or a better formula?

=COUNTIF('MMI Tailoring Outline'!E:E,"Mechanical")

and

=SUM(COUNTIFS('MMI Tailoring Outline'!E:E,"Mechanical",'MMI Tailoring Outline'!U:U,"Yes"))

[Root_]

There are several ways to achieve this. The easiest one is to have a (hidden) helper column on sheet 'MMI Tailoring Outline' -- say, column X.

Place the following formula in cell X1 and drag-copy it down as needed: =SUBTOTAL(103,E1)

Now, your formulas that will ignore data in hidden rows become:

=COUNTIFS('MMI Tailoring Outline'!E:E,"Mechanical",'MMI Tailoring Outline'!X:X,1)

=COUNTIFS('MMI Tailoring Outline'!E:E,"Mechanical",'MMI Tailoring Outline'!U:U,"Yes",'MMI Tailoring Outline'!X:X,1)

[FlameRetired]

@ Root_

Check this on a few trials. With this method I've encountered occasional failures to filter out deselected items.

I'm not alone and I've not read where anyone seems to know why.

Perhaps it's an idiosyncrasy of the 'Filter'.

[Root_]

Hi Dave,

I did an extensive testing of the two simple formulas on a dataset with 10,000 rows.
Used filtering, row hiding, and grouping -- not a single glitch.

If you have a workbook where this method occasionally fails, could you please post it?

[FlameRetired]

I'll do my best to find it if it's still on my hard drive.

[FlameRetired]

Root_

I could only find one of the files.

The helper formula is in D18:D26 and it is

=SUBTOTAL(103,A18)

Try filtering out Mary Sue. There will always be a Mary Sue left in the filtered data.

This helper modification does not have that problem.

=--OR(SUBTOTAL(103,A18),SUBTOTAL(103,C18))

[Root_]

Dave,

I played with your workbook and my testing dataset and found the following, which does look like a glitch in the Filter implementation.

Under certain conditions, the Filter does not see the last row with a SUBTOTAL(103,Ref) formula. That’s why Mary Sue stays in the filtered data.

This glitch occurs only when the helper column is filterable. In your workbook, try setting the filter in range A17:C17 only, and Mary Sue can be filtered out.

[FlameRetired]

So it does.

Sharp eyes.

Thanks for looking at this.

Now the remaining question is why the second formula does work filterable.

[HTTR21]

There are several ways to achieve this. The easiest one is to have a (hidden) helper column on sheet 'MMI Tailoring Outline' -- say, column X.

Place the following formula in cell X1 and drag-copy it down as needed: =SUBTOTAL(103,E1)

Now, your formulas that will ignore data in hidden rows become:

=COUNTIFS('MMI Tailoring Outline'!E:E,"Mechanical",'MMI Tailoring Outline'!X:X,1)

=COUNTIFS('MMI Tailoring Outline'!E:E,"Mechanical",'MMI Tailoring Outline'!U:U,"Yes",'MMI Tailoring Outline'!X:X,1)

Thank you for your help.

[Root_]

@ HTTR21: You are welcome.

@ FlameRetired: Upon further digging, I found that filtering works without the last-row glitch if the single-subtotal formula is entered as =SUBTOTAL(103,Ref)+0

[FlameRetired]

That's an interesting find. I've never known SUBTOTAL to require anything like that.

Thanks for passing that one on as well.