sum cells based on content

[Kutrenia]

I'm working on a timesheet, trying to get it to add the various types of leave based on what kind of leave is taken. For the most part, I have this working using the following formula:

{=SUM(IF(ISNUMBER(FIND(R$1,C2:P2)),VALUE(LEFT(C2:P2,FIND(R$1,C2:P2)-1)),0))}
It is only when there is more than one type of leave in the cell that I run into issues.

Sample:
Dates are shown on Row 1 beginning in Column C
Leave taken starts on Row 2 thru 19

3/8/2015 3/9/2015 3/10/2015 3/11/2015 .... 3/21/2015 AL S CRW CRT ADM
2AL / 2CRT, 3AL / 2S ...I should get 5AL, 2S, 2CRT under the appropriate headings without the text 5,2,2
but S just show "# Value!"

R1 = AL
S1 = S
T1 = CRW
U1 = CRT
V1 = ADM

Any assistance would be greatly appreciated!

 

[cyrilbrd]

Hi,

It might help members to give you appropriate feedback if you would consider posting a small sample of data with expected result. 6 columns max and 6 rows are sufficient.

 

[Kutrenia]

3/3/2015	3/4/2015	3/5/2015	AL	S	CRT
1.25 CRT		2 CRT, 2 S		#Value!	1.25
8 S	8 S	8 S		24
3 AL	1 S	5 AL	8	1

<TBODY>
</TBODY>

In this sample: The first row under the heading S (Sick Leave) should show 2, and CRT (Credits Taken) should show 3.25.

 

[cyrilbrd]

Thanks.
I do not have a solution however I do have a suggestion.
Problem solving often starts with how the data is presented.
In your case I do not know if it is generated by an external program or copied from the web or hard coded.
If hard coded please do consider the alternative:

Excel 2013
	A	B	C	D	E	F	G	H	I	J
1	date	type	qty			Sum of qty	Type
2	3/3/2015	CRT	1.25			Dates	CRT	S	AL	Grand Total
3	3/5/2015	CRT	2			3/3/2015	1.25	8	3	12.25
4	3/5/2015	S	2			3/4/2015		9		9
5	3/3/2015	S	8			3/5/2015	2	10	5	17
6	3/4/2015	S	8			Grand Total	3.25	27	8	38.25
7	3/5/2015	S	8
8	3/3/2015	AL	3
9	3/4/2015	S	1
10	3/5/2015	AL	5
Kutrenia

Few Columns and multiples Rows is better than the opposite.
Data could then be easily extracted with a simple Pivot Table (her is F1).

For your perusal.