sum specific values in a cell using criteria

[AriannaVV]

Hi everyone. I need help to built a formula to sum specific values in a cell, if this is possible. Let me explain this better.

In a cell, let's say A1, I have the following narratives:
01.08.2023 / 2 issues caused by bad weather, 02.08.2023 / 2 issues caused by drivers' fault, 03.08.2023 / 8 issues caused by bad weather, 04.08.2023 / 4 issues caused by drivers' fault. Is it possible to sum in different cells the issues caused by bad weather and the issues cause by driver's fault? I'm using excel 365. Thank you so much for any help provided.

 

[lrobbo314]

How about this?

Excel
	A	B	C	D	E	F
1	01.08.2023 / 2 issues caused by bad weather, 02.08.2023 / 2 issues caused by drivers' fault, 03.08.2023 / 8 issues caused by bad weather, 04.08.2023 / 4 issues caused by drivers' fault.
2
3	01.08.2023	2	caused by bad weather			6
4	02.08.2023	2	caused by drivers' fault
5	03.08.2023	8	caused by bad weather
6	04.08.2023	4	caused by drivers' fault.
Sheet2

Cell Formulas
Range	Formula
A3:C6	A3	=TEXTSPLIT(A1,{" / "," issues "},", ")
F3	F3	=SUM(IFERROR(FIND("driver",C3:C6)>0,0)*B3:B6)
Dynamic array formulas.

 

[lrobbo314]

Or in 1 cell.

Excel
	A	B	C
1	01.08.2023 / 2 issues caused by bad weather, 02.08.2023 / 2 issues caused by drivers' fault, 03.08.2023 / 8 issues caused by bad weather, 04.08.2023 / 4 issues caused by drivers' fault.		6
Sheet2

Cell Formulas
Range	Formula
C1	C1	=LET(ts,TEXTSPLIT(A1,{" / "," issues "},", "),SUM(IFERROR(FIND("driver",INDEX(ts,,3))>0,0)*INDEX(ts,,2)))

 

[AriannaVV]

Or in 1 cell.

Excel
	A	B	C
1	01.08.2023 / 2 issues caused by bad weather, 02.08.2023 / 2 issues caused by drivers' fault, 03.08.2023 / 8 issues caused by bad weather, 04.08.2023 / 4 issues caused by drivers' fault.		6
Sheet2

Cell Formulas
Range	Formula
C1	C1	=LET(ts,TEXTSPLIT(A1,{" / "," issues "},", "),SUM(IFERROR(FIND("driver",INDEX(ts,,3))>0,0)*INDEX(ts,,2)))

Thank you for your reply. The second solution seems to work ok, but i have a problem. The result of the formula is 0 because it can't sum the numbers. They appear as text. Any ideas how to overcome this?

 

[Peter_SSs]

I thought that you might be after something like this? (Punctuation may possibly cause further issues)

23 08 29.xlsm
	A	B	C
1		bad weather	drivers' fault
2	01.08.2023 / 2 issues caused by bad weather, 02.08.2023 / 2 issues caused by drivers' fault, 03.08.2023 / 8 issues caused by bad weather, 04.08.2023 / 4 issues caused by drivers' fault.	10	6
3	01.08.2023 / 0 issues caused by bad weather, 02.08.2023 / 2 issues caused by drivers' fault, 03.08.2023 / 0 issues caused by bad weather, 04.08.2023 / 4 issues caused by drivers' fault, 05.08.2023 / 204 issues caused by drivers' fault.	0	210
Sums

Cell Formulas
Range	Formula
B2:C3	B2	=LET(ts,TRIM(TEXTSPLIT($A2,{"/",",","."})),f,FILTER(ts,RIGHT(ts,LEN(B$1))=B$1),SUM(--LEFT(f,FIND(" ",f))))

 

[AriannaVV]

I thought that you might be after something like this? (Punctuation may possibly cause further issues)

23 08 29.xlsm
	A	B	C
1		bad weather	drivers' fault
2	01.08.2023 / 2 issues caused by bad weather, 02.08.2023 / 2 issues caused by drivers' fault, 03.08.2023 / 8 issues caused by bad weather, 04.08.2023 / 4 issues caused by drivers' fault.	10	6
3	01.08.2023 / 0 issues caused by bad weather, 02.08.2023 / 2 issues caused by drivers' fault, 03.08.2023 / 0 issues caused by bad weather, 04.08.2023 / 4 issues caused by drivers' fault, 05.08.2023 / 204 issues caused by drivers' fault.	0	210
Sums

Cell Formulas
Range	Formula
B2:C3	B2	=LET(ts,TRIM(TEXTSPLIT($A2,{"/",",","."})),f,FILTER(ts,RIGHT(ts,LEN(B$1))=B$1),SUM(--LEFT(f,FIND(" ",f))))

Τhis is exaclty what I was looking for. Thank you very much. Works great. Ideed I have some issues with the punctuation (it's not same in all cases) but I solve it by making some changes in the text. Thanks again!!!

 

[Peter_SSs]

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