Sumproduct, extract numbers from text

[scorps10]

Hi,

I receive an automatic dump of data on a weekly basis that looks like the below Table A. Basically, the first column is by category and the remaining columns are by week. How would I summarize the hours (found within the text) into Table B, which would summarize it by category (A,B,C) and Type (Submitted, Approval, Invoice, etc)? Any help would be highly appreciated.


Table A: Table B:
[TABLE="width: 901"]
<colgroup><col><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Category[/TD]
[TD]08-Jun-18[/TD]
[TD]15-Jun-18[/TD]
[TD][/TD]
[TD][/TD]
[TD]Submitted [/TD]
[TD]Approval[/TD]
[TD] Invoice[/TD]
[TD] Client [/TD]
[TD]Document[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Invoice (HRS : 383[/TD]
[TD]Invoice (HRS : 147.5[/TD]
[TD][/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Document (HRS : 36[/TD]
[TD]Document (HRS : 39[/TD]
[TD][/TD]
[TD]B[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Submitted (HRS : 53[/TD]
[TD]Submitted (HRS : 41[/TD]
[TD][/TD]
[TD]C[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Submitted (HRS : 9[/TD]
[TD]Submitted (HRS : 6.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Submitted (HRS : 5[/TD]
[TD]Client (HRS : 54[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Submitted (HRS : 19[/TD]
[TD]Submitted (HRS : 18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Submitted (HRS : 3[/TD]
[TD]Submitted (HRS : 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Submitted (HRS : 2[/TD]
[TD]Submitted (HRS : 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Invoice (HRS : 778.5[/TD]
[TD]Invoice (HRS : 446.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Invoice (HRS : 747[/TD]
[TD]Invoice (HRS : 842[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Invoice (HRS : 936.5[/TD]
[TD]Approval (HRS : 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Invoice (HRS : 982.5[/TD]
[TD]Invoice (HRS : 819.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Invoice (HRS : 918[/TD]
[TD]Invoice (HRS : 1122[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Invoice (HRS : 58.5[/TD]
[TD]Invoice (HRS : 292[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Client (HRS : 528.5[/TD]
[TD]Client (HRS : 429.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Approval (HRS : 4[/TD]
[TD]Approval (HRS : 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[kvngrn]

hi,

i have a way to achieve what you want.

You need to add 2 columns D and E
In D write de formula =A2&TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",50)),1,50)) // this will create a key Category and type (Ex ADocument)
In Column E : =TRIM(MID(B2,FIND(":",B2)+1,LEN(B2)-FIND(":",B2)))+0 // this will give you the number of our - I use +0 at the end to change it from text to number

Then in your table B :
=SUMIF($D:$D,$F2&G$1,$E:$E)

You can drag and drop to the right and down

 

[scorps10]

Thanks Kvngrn

 

[Peter_SSs]

My interpretation was that you wanted the whole table summarised, not just one column?

Formula in F2 is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Category	8-Jun-18	15-Jun-18			Submitted	Approval	Invoice	Client	Document
2	A	Invoice (HRS : 383	Invoice (HRS : 147.5		A	0	8	8473.5	958	75
3	A	Document (HRS : 36	Document (HRS : 39		B	94	12	0	0	0
4	B	Submitted (HRS : 53	Submitted (HRS : 41		C	71.5	0	0	54	0
5	C	Submitted (HRS : 9	Submitted (HRS : 6.5
6	C	Submitted (HRS : 5	Client (HRS : 54
7	C	Submitted (HRS : 19	Submitted (HRS : 18
8	C	Submitted (HRS : 3	Submitted (HRS : 2
9	C	Submitted (HRS : 2	Submitted (HRS : 7
10	A	Invoice (HRS : 778.5	Invoice (HRS : 446.5
11	A	Invoice (HRS : 747	Invoice (HRS : 842
12	A	Invoice (HRS : 936.5	Approval (HRS : 8
13	A	Invoice (HRS : 982.5	Invoice (HRS : 819.5
14	A	Invoice (HRS : 918	Invoice (HRS : 1122
15	A	Invoice (HRS : 58.5	Invoice (HRS : 292
16	A	Client (HRS : 528.5	Client (HRS : 429.5
17	B	Approval (HRS : 4	Approval (HRS : 8
Sumproduct

 

[scorps10]

Peter, that works perfectly. Thanks!

 

[Peter_SSs]

Peter, that works perfectly. Thanks!

You're welcome. Thanks for the follow-up.

 

[Dossfm0q]

Thank Sir