Filter + Sumproduct + Countif + Wildcard!

[kaiser14]

Hi All,

I am currently using the following formula designed by Aladin Akyurek to find matches for a certain date when I am using a filter:

SUMPRODUCT(SUBTOTAL(3,OFFSET('Approved TSPs'!K1:'Approved TSPs'!K9999,ROW('Approved TSPs'!K1:'Approved TSPs'!K9999)-MIN(ROW('Approved TSPs'!K1:'Approved TSPs'!K9999)),,1))*( 'Approved TSPs'!K1:'Approved TSPs'!K9999TSPs'!K9999="01/01/0001"))

it works great, however I also need to find occurences of "part dates" such as 2009 by itself, I have tried using the following formula:

SUMPRODUCT(SUBTOTAL(3,OFFSET('Approved TSPs'!K1:'Approved TSPs'!K9999,ROW('Approved TSPs'!K1:'Approved TSPs'!K9999)-MIN(ROW('Approved TSPs'!K1:'Approved TSPs'!K9999)),,1))*( 'Approved TSPs'!K1:'Approved TSPs'!K9999TSPs'!K9999="*2009"))

The hope is I can use the filter and then use a wildcard search to track down the occurences of 2009,2010 in a massive list of dates (in the format of 01/01/2009 , 01/05/2010 etc)

Thanks in advance for all your help!

 

[Aladin Akyurek]

Hi All,

I am currently using the following formula designed by Aladin Akyurek to find matches for a certain date when I am using a filter:

SUMPRODUCT(SUBTOTAL(3,OFFSET('Approved TSPs'!K1:'Approved TSPs'!K9999,ROW('Approved TSPs'!K1:'Approved TSPs'!K9999)-MIN(ROW('Approved TSPs'!K1:'Approved TSPs'!K9999)),,1))*( 'Approved TSPs'!K1:'Approved TSPs'!K9999TSPs'!K9999="01/01/0001"))

it works great, however I also need to find occurences of "part dates" such as 2009 by itself, I have tried using the following formula:

SUMPRODUCT(SUBTOTAL(3,OFFSET('Approved TSPs'!K1:'Approved TSPs'!K9999,ROW('Approved TSPs'!K1:'Approved TSPs'!K9999)-MIN(ROW('Approved TSPs'!K1:'Approved TSPs'!K9999)),,1))*( 'Approved TSPs'!K1:'Approved TSPs'!K9999TSPs'!K9999="*2009"))

The hope is I can use the filter and then use a wildcard search to track down the occurences of 2009,2010 in a massive list of dates (in the format of 01/01/2009 , 01/05/2010 etc)

Thanks in advance for all your help!

The formula you posted looks as if it's garbled...

Did you mean something like:

=SUMPRODUCT(
   SUBTOTAL(3,OFFSET('Approved TSPs'!K1,
     ROW('Approved TSPs'!K1:K9999)-ROW('Approved TSPs'!K1),,1)),
   --(YEAR('Approved TSPs'!K1:K9999)=2009))

Note that using a coercer like -- instead of * allows us to invoke SumProduct according to its native syntax.