Excel-Buzz

Hi One and all, This article will demonstrate that how can we apply further criterias on Filtered data using formulas and get to the conclusions. So here we go:- So First lets concentrate on Count Part.. The formula I used is.. =SUMPRODUCT(SUBTOTAL(3,OFFSET(B1,ROW(B2:B16)-1,)),--(B2:B16="A"),--(C2:C16>100)) First lets discuss the bold portion only i.e. SUBTOTAL(3,OFFSET(B1,ROW(B2:B16)-1,)) Which extends to SUBTOTAL(3,OFFSET(B1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},)) which further extends to SUBTOTAL(3,{B2;B3;B4;B5;B6;B7;B8;B9;B10;B11;B12;B13;B14;B15,B16})

First of all, I wanna thank Mr. Tony Volko ( An Expert at ExcelForum.com ) to provide this beauty. The Formula I have used is =TRIM(MID(SUBSTITUTE("|"&B$3,"|",REPT(" ",255)),ROWS(C$1:C1)*255,255)) Dragged down Here is the explanation… Lets start from the inside of the formula first.. Ie. =SUBSTITUTE("|"&B$3,"|",REPT(" ",255)) Syntax [SUBSTITUTE(text, old_text, new_text)] which is expanded to '=SUBSTITUTE("|5|3|4|67|10|45|15","|",REPT(" ",255)) It will SUBSTITUTE the "|" with 255 Spaces and will provide the following Text.