Array Formula

[D310633]

Hi,

I have this table below...

I'd like to ask assistance what array formula I can use to add the values for those cells that has "L :"? Thank you in advance!
2/12 2/13 2/14 2/15 2/16 P P L : 13 P L : 3 <tbody> </tbody>

<tbody>
</tbody><colgroup><col span="5"></colgroup>

 

[jtakw]

Re: Help Needed: Array Formula

Hi,

Are you saying you want to add the numbers for cells with L, in your sample, 13+3?

 

[Dave Patton]

Re: Help Needed: Array Formula

Excel 2010
	A	B	C	D	E
1
2	P	P	L : 13	P	L : 3
3
4					16
5
2b

Cell Formulas
Range	Formula
E4	=SUMPRODUCT(SUBSTITUTE(REPLACE(A2:E2,1,1,0)," : ",0,1)+0)

 

[D310633]

Re: Help Needed: Array Formula

Hi Dave,

Thank you very much!
I tried the formula and it works....

I have a follow up question, does the length of the table matter?
The actual table has column from D to Z, when I expanded the column using the formula you created, it provides an error value.

 

[D310633]

Re: Help Needed: Array Formula

Hi jtakw,

Yes, That's what my goal is.

The actual table has the following headers:
Column A1: ID
Column B1: Name
Column C1: Manager's Name
Column D1 to Z1: Date

The data on the table shows "L : 13" where "L" stands for "Late" and the number value is the duration in minutes.
Based on the given data, I would like to get the total Late duration.

Thank you very much!

 

[pgc01]

Re: Help Needed: Array Formula

Hi Dave,

Thank you very much!
I tried the formula and it works....

I have a follow up question, does the length of the table matter?
The actual table has column from D to Z, when I expanded the column using the formula you created, it provides an error value.

Hi

Can you post an example where you get an error using Dave's formula?

 

[D310633]

Re: Help Needed: Array Formula

Hi pgc01,

Thank you for your response.
I tried to study what the problem was when i expanded the range from the original formula provided by Dave.

It was because there were other data from the range that has more than 6 characters and without numbers.

So I tweaked the formula a little and now it works the way I wanted it to.

This has been fixed.

Thank you everyone for your help!!!

 

[markmzz]

Re: Help Needed: Array Formula

Hi!

Try this array formula (use Ctrl+Shift+Enter to enter the formula):

=SUM(--IFERROR(REPLACE(D2:Z2,1,SEARCH(" : ",D2:Z2)+2,""),0))

Markmzz