collecting rows into an array for fasten up array formulas, is it possible?

Hi all,

I have a table with 250 rows and columns A-AD. Each row has different types of information about a "bank paper". The table must be unsorted due to several reasons. I have to summarize/count the papers type by type. For example coloumn AD has the information if the paper's type is "NEW" or "DELETED". Also AC has the information if it is paper "Type 1" or paper "Type 2" etc.

I have to do many counts like this: count how many NEW, Type 1 paper exist in the table. I do this with array formulas since there are many aspects I should "filter", but it is too slow.

My question is: is it possible to collect the correct rows, for example all "NEW" rows into an array somehow? I want this to fasten up the search, since now the array formulas for finding NEW & Type 1 and NEW & Type 2 searches two times for NEW, what would not be necessary if I could collect all NEW rows somehow into someting like an "array-cell".

(I tried to do it with OFFSET, MATCH, INDIRECT and Ctrl-Shift-Enter with no luck, also thought about INDEX but I think these formulas are not for this.)

I have Excel 2003 (and two days ).

Hi and welcome to the board

Have you had a look at Pivot Tables?

Hi and thank you!

To be honest, I am not too familiar with Pivot Tables. However, I tried to use them, but I chosed not to use them becouse it is not guaranteed that the data source's size is constant.

I also cannot use a named range, becouse I have to create a template, and the user will create worksheets from the template by copying them and renaming it corresponding to the current month. (I do not know how can I create a dynamic named range what also renames itself, without VB code, what I was told not to use.)

But since I am not into Pivot Tables, I am unsure if I am missing the easier way here. I also have to create datas for the whole year from the individual worksheets. (This is now done by copying the final data with formulas.)

Thank you for your help, I will give it a try then!

For my personal interest, is there such a thing what I tried to use originally?