Summarize Range Without Blanks, ""

[Alriemer]

Good evening,

I am trying to summarize a range which includes blank cells, cells with text and cells with the formula result: "". I want to return only the cells with text in an adascent column.

I am working with an array formula developed by Chip Pearson - see http://www.cpearson.com/excel/noblanks.htm

Here is the formula:

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(Movers)-(COUNTBLANK(Movers)),"",INDIRECT(ADDRESS(SMALL(
(IF(Movers<>"",ROW(Movers),ROW()+ROWS(Movers))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(Movers),4)))

This is array-entered in a range the same size as the source range. In my case, the data is found in Movers and the output in NoBlanksRange; the rest #N/A.

This formula is fooled, and I only get back the first result - in both the first cell of NoBlanksRange and the last. I tried to modify it as follows:

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(Movers)-(COUNTIF(Movers,"")+COUNTBLANK(Movers)),"",INDIRECT(ADDRESS(SMALL(
(IF(Movers<>"",ROW(Movers),ROW()+ROWS(Movers))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(Movers),4)))

but then it only returned "" values. Any suggestions?

Much obliged,
Alriemer

 

[Aladin Akyurek]

I'd suggest using advanced filter for this type of problems. If you insist on a formula-based approach, see my contrib in...

http://216.92.17.166/board/viewtopic.php?topic=24204&forum=2

 

[Alriemer]

Aladin,

Unfortunately the formula approach is necessary here; the data has to be passed on to a nice printable output page. Your prior post is helpful.

THanks,
Alriemer

 

[shaylynch]

have you tried using a pivot table?