Using Countif and Subtotal based on date older than 2 years ago

**simmy981** · 07-09-2014, 04:16 AM

Hi, Im trying to find a formula that combines countif and subtotal as i have a filter used.

Currently have the formula: =COUNTIF(B:B,"<"&TODAY()-730)
but would like to not include the filtered data.

Any suggestions would be greatly appreciated.
Thanks.

**benishiryo** · 07-09-2014, 08:23 AM

welcome to the forum. try something like:
=SUMPRODUCT((B3:B10<TODAY()-730)*SUBTOTAL(3,OFFSET(B3,ROW(B3:B10)-ROW(B3),)))

it's explained in the file of my link below*Tips & Tutorials I Compiled, sheet 10
also, when using SUMPRODUCT or other array formulas, do not reference the whole column as it will affect performance

**simmy981** · 07-10-2014, 04:29 AM

Thank you for this.
I noticed the 'offset' part in other threads with similar problems but I never knew how to adapt it as i didnt really understand it.

The only reason i was using entire column as it is linked to a macro which copies rows from another sheet therefore the list is ever changing.

Nevertheless thanks for the help