Excel 2007 : Simplify multiple VLOOKUPs and COUNTIFs possible?

[dip11]

Hi,

I am using several multi-VLOOKUPs and COUNTIFS which I think might turn into a problem eventually.

I was wondering whether there is a way to simplify (mostly speed up) what I am doing.
You probably won't need the attached workbook as the referenced other workbooks are not included (you'll have to work with non-updates). Should be useful for visualizing what I'm looking at anyhow.

Formula 1 (repeated a lot down the column).
What it does is check 4 different sources for whether a value is 1 for the criteria.
Criteria are O1 (all sources), A2 (for 1 source) and B2 (for 3 sources).

=SUM(COUNTIFS(WEH.xlsx!$BH:$BH,">"&$P$1-12,WEH.xlsx!$P:$P,1,WEH.xlsx!$BI:$BI,LEFT($B2,4)&"*"),COUNTIFS(DSU.xlsx!$BH:$BH,">"&$P$1-12,WEH.xlsx!$P:$P,1,WEH.xlsx!$BI:$BI,LEFT($B2,4)&"*"),COUNTIFS([ICrec30_04.xlsx]SIC!$BM:$BM,">"&$P$1-12,[ICrec30_04.xlsx]SIC!$P:$P,1,[ICrec30_04.xlsx]SIC!$BP:$BP,LEFT($B2,4)&"*"),COUNTIFS(Sheet1!$B:$B,$A2,Sheet1!$AJ:$AJ,">"&$P$1-12,Sheet1!$T:$T,1))

Formula 2 (repeated 5 times for differing criteria)
Checks the Counter sheet (formula 1 above) for the right person and if so, divides 2 different columns (one is an actual count, the other is the total count figure)

=VLOOKUP($A$2,Counter!A:H,3,FALSE)/VLOOKUP($A$2,Counter!A:H,8,FALSE)

Note the 4 different worksheets in the book and what information is present on each worksheet and whether some could be used in some fashion I hadn't thought of for simplification?

Thamks in advance.