COUNTIFS/IF/AND Formula Help

Please post a smaller example file. I have no idea what you're talking about since there are no L's in column E or 1's in column D in the default opened sheet in your file (and you have dozens of sheets)

It's really hard to understand what you need, since you have an enormous spreadsheet and most columns don't have titles to describe what they are. Could you just give a step by step general explanation of what should happen?
So for example start like this:
If D1 is "1" and E1 is "L", check if E2 (next to D2="2") is "W". If so, multiply L1 with... etc

If: D2 = "1" & E2 = "L", check if D3 = "2". If D3 = "2" & E3 = "W" then show value of L3. (EXAMPLE 1&5)

If: D2 = "1" & E2 = "L", check if D3 = "2". If D3 = "2" & E3 = "L", check if D4 = "3". If D4 doesn't = "3" then show value of L3. (EXAMPLE 4)

If: D2 = "1" & E2 = "L", check if D3 = "2". If D3 = "2" & E3 = "L", check if D4 = "3". If D4 does = "3" then show value of L4 multiplied by 2 with L3 subtracted from that value ((L4*2)-L3). (EXAMPLE 2&3)

"" indicates a value is present but it's not important to the formula.

EXAMPLE 1
--D E L (4TH COLUMN)
2 1 L ""
3 2 W 100 100
4 3 "" ""

EXAMPLE 2
--D E L (4TH COLUMN)
2 1 L ""
3 2 L -100
4 3 L -100 -300

EXAMPLE 3
--D E L (4TH COLUMN)
2 1 L ""
3 2 L -100
4 3 W 100 100

EXAMPLE 4
--D E L (4TH COLUMN)
2 1 L ""
3 2 L -100 -100
4 1 "" ""

EXAMPLE 5
--D E L (4TH COLUMN)
2 1 L ""
3 2 W 100 100
4 1 "" ""

I've assumed that in the cases that you didn't mention (D2 is not 1, E2 is not L etc) you want an empty cell. And also that E3 will always be filled with either W or L.

=IF(AND(D2=1,E2="L",D3=2),IF(E3="W",L3,IF(D4=3,L4*2+L3,L3)),"")

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.