Excel 2003 formula

[anna82marie]

Hi guys,

I'm having issues getting my head around a formula and wondered if you could help me before I throw my PC out of a window! I do not like excel 2003 at all!
I'm working across 2 spreadsheets, for arguments sake we'll call them S1 and S2. The result i need from a formula is as follows:

I need the sum of S1 column L divided by S2 B6 IF the following criteria are met;
S1 column B = S2 A6 and S1 column E >=S2 B1 and S1 column E <=S2 B2

I'm fairly sure that this shouldn't be as complicated as I'm trying to make it. Please help!!

 

[RoryA]

Assuming the formula is in S2:
=SUMPRODUCT((S1!$B$1:$B$1000=$A$6)*(S1!$E$1:$E$1000>=$B$1)*(S1!$E$1:$E$1000<=$B$2),S1!$L$1:$L$1000)/$B$6