Excel 2003, Read out an Array

[AndersRasmussen]

Hi Guys,

Due to maintenance reasons I'd prefer not to use VBA for Excel to solve my problem.

My Scenario is as follows.

I've got a data set with:
Employee Name; Employee Company; Employee Division; Employee Position

Each of the collums has been named accordingly.

***********************
A B C D E
1 Name Company Division Position Salary
2 Anders AND AND1 MD 1000
3 Brian AND AND1 Clerck 500
4 Chris AND AND2 MD 1000
5 Debbie BRI BRI1 MD 1000
6 Eric AND AND1 Clerck 500

I've got a sum array formula.

={sum(if((Company=AND)*(Division=AND1)*(Position=Clerck);Salary;false))}
Which returns the Salary of Brian and Eric. Total 1000

I would like to display all Employess who matches a set of criterias on:

Company, Division and Position

What I need to do is to return the Names of Brian and Eric instead. So that I've got a result which looks some thing like this.

Clercks from Company AND, Division AND1
Brian
Eric

The question is then.
Can I read out All the Employess who matches the set Criterias, with out any blank lines?

Is there a way to define a matrice as the resultset of an array?

I'll be looking forward to hearing from you and hope that some one can assist me.

Brgds, Anders Rasmussen

 

[SkipVought]

Hi,

Turn on your AutoFilter and set the criteria for each column.

You can use the SUBTOTAL function. You can return a number of different aggregation types, including the SUM. The FIRST argument controls if the aggregation is for ALL cells in the range or ALL VISIBLE cells in the range. I'm sure that the latter will return the SUM yoju desire.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}