Complex Array Formula

I have been searching for a formula that can add two different types of data in the same range and haven't found anything. Please help.

Given data:

Mon Tues Wed Thur Fri
AL 6 SL 2
SL 2 AL 6 AL 8 AL 8

The result for these two weeks are AL = 28 and SL = 4.

What formula will work to arrive at these desired results?

thanks,
Sam

Your example data isnt clear, its impossible to work out whats in Mondays column, is it just AL or is it AL AND 6?
Use a comma as a separator so the data is legible.

Hello,

First, I suggest you change the name of this thread so others will have an easier time looking for solutions for their problems similar to yours, or try to help you.
Second, you can try this Array formula

Please Login or Register  to view this content.

To find the sum of all that has AL before it.
Here is a sample file (Next time please provide one before hand).

Because it is an Array formula, you need to hold Ctrl-Shift then hit Enter (You can see the formula warped inside a { })
You can check the attached file.

I also fixed something in the formula, so now it will calculate by row. Check the attached file, I just changed it

Try these array formulas**:

For the sum of AL:

=SUM(IF(LEFT(C3:L3,2)="AL",--MID(C3:L3,4,3)))

For the sum of SL:

=SUM(IF(LEFT(C3:L3,2)="SL",--MID(C3:L3,4,3)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down as needed.

You should start your own thread.