Skip to main content

Posts

Showing posts from September, 2014

Summing and Counting Criteria in filtered data (Using SUMPRODUCT and SUBTOTAL Functions)

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})

Converting Delimited Text in to Rows.

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.