Differentiating overlapping strings using SUMIFS?

[aehinma]

Hi! I am working on a tracking sheet where I want to sum up the minutes worked by each employee on each given day.

My problem is that we have a new employee whose initials overlap with an existing employee's; ie ABC and BC. I can't figure out how to differentiate between the two within the SUMIFS statement. The operator names are pulled from manually entered spreadsheets so sets of initials could be listed in any order and preceded by " ", ",", or nothing.

I've tried subtracting the difference from BC's totals, but this doesn't account for when they work together on something (minutes should count for both). I'm playing with conditional statements example: =SUMIFS(C:C,B:B,AND("*"&F17&"*","<>"*"&F15"*""),A:A,$G$10) but having trouble within the SUMIFS statement since all conditions must be true.

My idea for a manual forced solution was to find & replace all instances of "ABC" with "AC" but for some reason this gives a result of 0 minutes for both operators, why is that?

I'm sure there's a better way to accomplish this task overall so I'm open to suggestions, just interested in learning how to solve this case as well

 

[shift-del]

Hello

The best solution is to split the data in multiple rows.
Instead of
01/10/2024 - BC, ABC - 200
Use:
01/10/2024 - BC - 200
01/10/2024 - ABC - 200

 

[JamesCanale]

It's a bit cleaner if you don't have any spaces in column B.

MrExcelPlayground20 (version 1).xlsb
	A	B	C	D	E	F	G
1	8-Jan	AK	25
2	8-Jan	PB	45
3	8-Jan	IB	20
4	9-Jan	HB	70
5	9-Jan	IB,ABC	90
6	9-Jan	HB, ABC,AK	180
7	9-Jan	PB	160
8	9-Jan	IB	160				1/10/2024
9	10-Jan	AK, ABC, BC	100			AK	400
10	10-Jan	BC	200			HB	415
11	10-Jan	ABC	100			IB	400
12	10-Jan	IB,BC	400			ABC	700
13	10-Jan	BC,ABC	200			PB	415
14	10-Jan	PB,HB	415			BC	900
15	10-Jan	AK,ABC	300			JX	0
Sheet23

Cell Formulas
Range	Formula
G9:G15	G9	=IFERROR(SUM(FILTER($C$1:$C$15,($A$1:$A$15=$G$8)*NOT(ISERROR(((SEARCH(","&F9,","&SUBSTITUTE($B$1:$B$15," ","")))))))),0)