Subtract The Top Visible 2nd and 3rd row from a filtered list?

[gvillepa]

Hi all,

I'm trying to work a formula that always subtracts the 3rd visible row and cell data from the 2nd visible row and cell data to return the result from a filtered list.

Every time i filter it returns 3 rows of data (a header, and 2018 data and 2017 data). But when you filter different data paramteres for 2017 and 2018, it could be row 5 and 73 showing or 99 and 276, or whatever, but they are always going to be in the 2nd and 3rd row. I am thinking mod function, but that's as far as my brain takes me. Resorting to folks smarter than myself.

Thanks!

 

[pgc01]

Hi

With the header cell A1 and the values in A2:A100, try:

=SUM(N(OFFSET($A$2,SMALL(IF(SUBTOTAL(2,OFFSET($A$2,ROW($A$2:$A$100)-ROW($A$2),))=1,ROW($A$2:$A$100)-ROW($A$2)),{1,2}),))*{1,-1})
This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.

 

[gvillepa]

Much obliged pgc01. It works and works well. Now i just have to follow the formula to understand how you constructed it!! I could not have thought of that myself. Thanks again.

 

[pgc01]

You're welcome.
If you have problems post back.