vlookup on filtered list with sum

[Achelois]

I have a workbook containing two sheets.

Sheet 1 - is a table with Team, and cost
The list has an autofilter in place
In cell F20 I have a total for the cost field
In cell F21 I need to put in a formula which will do a number of things

1) The formula needs to refer to the table above, look at the filtered column (a) and see the value

2) Refer to sheet 2, look up value found in column a on sheet 1 and then take the value from this table

3) Return to sheet 1 using the value and subtract it from F20.

I have managed all of the above.

The only problem I now have is that when the list is unfiltered, I want the F20 formula (steps 1-3 above) to not perform the vlookup etc but to sum the cost column on sheet 1

The formula I have is

=VLOOKUP(INDEX(A2:A15,MATCH(1,(SUBTOTAL(3,OFFSET(A2:A15,ROW(A2:A15)-MIN(ROW(A2:A15)),0,1)))*(A2:A15<>""),0)),'Team Totals'!A1:C7,3,FALSE)-F20

Basically, if column A is unfiltered then DONT do the above formula but sum the Cost column on sheet 1, otherwise - DO the formula above?!

Any help gratefully appreciated

A

 

[Achelois]

Anyone or should I have posted this elsewhere please?

 

[Achelois]

Can anyone help me please - desperate for help now!