Filter Sheet 1, formula on filtered cells on Sheet B

[StephanV]

I've got a database on Sheet 1, containing several projects, with dates, actions and errors.

Sheet 1:
A1:A5000 = Project Name
B1:B5000 = Start date
C1:C5000 = End date
D1:D5000 = No. actions
E1:E5000 = Error on action

From these values I would like to calculate averages, based on project, dates etc on sheet 2.

Is it possible to calculate these averages based on the filtered / visible cells in sheet 1?

So for example, I filter sheet 1 on project name, and then calculate the error percentage on the visible cells (Ex/Dx)

Is this possible?

 

[StephanV]

I tried SUBTOTAL(1,E1:E5000;D1:D5000) but this didnt do the trick

 

[mancemonster]

Try...

=SUBTOTAL(9,E1:E5000)/SUBTOTAL(9,D1:D5000)

 

[StephanV]

Another try:

Sheet 2: A1: subtotal(9;'Sheet 1'!E1:E5000)
Sheet 2: A2: subtotal(9;'Sheet 1'!D1:D5000)
Sheet 2: A3: =A1/A2

Can someone with more excel knowledge confirm that this is the average of my filtered data on sheet 1? My first check says it is, but I cant take the risk of error :P

 

[StephanV]

Try...

=SUBTOTAL(9,E1:E5000)/SUBTOTAL(9,D1:D5000)

Ha!

Thats more efficient