Truncate and count names

[mwvirk]

First scenario is...

I have mix names in:
Cell B1 Abc+Def+Xyz and a Date in A1
Cell B2 Def+Ghi and a Date in A2
Cell B3 Abc+Xyz and a Date in A3
Cell B4 Def+Ghi and a Date in A4

In C1 I want to count how many times Abc is present from B1:B4

In C2 want to count how many times Def is present from B1:B4

In C3 I want to count how many times Ghi is present from B1:B4

In C4 I want to count how many times Xyz is present from B1:B4


Second scenario is...

I have mix names in:

Cell B1 Abc+Def+Xyz and a Date in A1
Cell B2 Def+Ghi and a Date in A2
Cell B3 Abc+Xyz and a Date in A3
Cell B4 Def+Ghi and a Date in A4

In C1 I want to count how many times Abc is present from B1:B4 on next date

In C2 want to count how many times Def is present from B1:B4 on next date

In C3 I want to count how many times Ghi is present from B1:B4 on next date

In C4 I want to count how many times Xyz is present from B1:B4 on next date


(Please note that first scenario is for day shift from 4 pm to 12 am (16:00 to 24:00)

(but in second scenario the duty for the night shift start after 00:00 hrs so date 1 will be counted as date 2)

 

[StephenCrump]

Can you please provide some sample data for the two scenarios, and the results you expect to see, using XL2BB - Excel Range to BBCode

Otherwise it's not at clear what you're asking, or what you mean by "next date", "date 1" and "date 2".

 

[mwvirk]

Can you please provide some sample data for the two scenarios, and the results you expect to see, using XL2BB - Excel Range to BBCode

Otherwise it's not at clear what you're asking, or what you mean by "next date", "date 1" and "date 2".

thank you.

Let me explain the first step...
I am receiving data from another system where staff names are showing like staff1+staff2 so I need to count each staff attendance
I am checking how many times a person is working in which shift
if the value in C is W then it's a normal working day and if it's P then it will be considered a public holiday

I will count how many times a person works the morning shift. if in C value is W then it will be in 'Morning' but if a value in C is P or it's Fri or Sat then it will be considered as 'Weekend Morning'
The same will apply for 'Afternoon' and Weekend Afternoon' and for 'Night' and 'Weekend Night' but for the Night Shift, duty starts after midnight 00:00hrs so calculation/attendance and payable amount will be as of the next day. i.e. if someone worked on Thursday night shift then it will be considered day Friday and it will fall in 'Weekend Night'

Note: I will calculate but no amount will be paid if staff is on duty in the morning during work days (Sun - Thu) but if it's Fri or Sat or a Public holiday, he will be in "Weekend Morning"

I would appreciate it if you could help resolve it. let me know if my message is clear now. Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				Morning	Afternoon	Night
2	Sat	01-Feb-2025	W	Abc+Def	Ghi+Jkl	Mno+Pqr			Shift Days
3	Sun	02-Feb-2025	W	Abc+Def	Ghi+Jkl	Mno+Pqr			Morning	Weekend Morning	Afternoon	Weekend Afternoon	Night	Weekend Night
4	Mon	03-Feb-2025	W	Stu+Vwx	Xyz	Abcd		Abc	1	1	1	1	0	0
5	Tue	04-Feb-2025	W	Stu+Vwx	Xyz	Abcd		Def	1	1	1	1	0	3
6	Wed	05-Feb-2025	W	Mno+Pqr	Abc+Def	Ghi+Jkl		Ghi	0	0	1	1	1	1
7	Thu	06-Feb-2025	P	Mno+Pqr	Abc+Def	Ghi+Jkl		Jkl	0	0	1	1	1	1
8								Mno	1	1	0	0	1	1
9								Pqr	1	1	0	0	1	1
10								Stu	2	0	0	0	0	0
11								Vwx	2	0	0	0	0	0
12								Xyz	0	0	2	0	0	0
13								Abcd	0	0	0	0	2	0
14
15									Shift Amount Calculation
16									Weekend Morning	Afternoon	Weekend Afternoon	Night	Weekend Night	Total Of Amount
17								Abc	4	12	14	0	0	30
18								Def	4	12	14	0	48	78
19								Ghi	0	12	14	14	16	56
20								Jkl	0	12	14	14	16	56
21								Mno	4	0	0	14	16	34
22								Pqr	4	0	0	14	16	34
23								Stu	0	0	0	0	0	0
24								Vwx	0	0	0	0	0	0
25								Xyz	0	24	0	0	0	24
26								Abcd	0	0	0	28	0	28
Sheet1

Cell Formulas
Range	Formula
A2:A7	A2	=TEXT(B2,"DDD")
I17:I26	I17	=J4*8*0.5
J17:J26	J17	=K4*8*1.5
K17:L26	K17	=L4*8*1.75
M17:M26	M17	=N4*8*2
N17:N26	N17	=SUM(I17:M17)

 

[StephenCrump]

Thanks, that's much clearer.

How about:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1				Morning	Afternoon	Night
2	Sat	1 Feb 2025	W	Abc+Def	Ghi+Jkl	Mno+Pqr			Shift Days
3	Sun	2 Feb 2025	W	Abc+Def	Ghi+Jkl	Mno+Pqr			Morning	Weekend Morning	Afternoon	Weekend Afternoon	Night	Weekend Night
4	Mon	3 Feb 2025	W	Stu+Vwx	Xyz	Abcd		Abc	1	1	1	1	0	0
5	Tue	4 Feb 2025	W	Stu+Vwx	Xyz	Abcd		Def	1	1	1	1	0	0
6	Wed	5 Feb 2025	W	Mno+Pqr	Abc+Def	Ghi+Jkl		Ghi	0	0	1	1	1	1
7	Thu	6 Feb 2025	P	Mno+Pqr	Abc+Def	Ghi+Jkl		Jkl	0	0	1	1	1	1
8								Mno	1	1	0	0	2	0
9								Pqr	1	1	0	0	2	0
10								Stu	2	0	0	0	0	0
11								Vwx	2	0	0	0	0	0
12								Xyz	0	0	2	0	0	0
13								Abcd	0	0	0	0	2	0
14
15								ShiftHours		8
16								Loading		0.50	1.50	1.75	1.75	2.00
17
18										Shift Amount Calculation
19										Weekend Morning	Afternoon	Weekend Afternoon	Night	Weekend Night	Total Of Amount
20								Abc		4	12	14	0	0	30
21								Def		4	12	14	0	0	30
22								Ghi		0	12	14	14	16	56
23								Jkl		0	12	14	14	16	56
24								Mno		4	0	0	28	0	32
25								Pqr		4	0	0	28	0	32
26								Stu		0	0	0	0	0	0
27								Vwx		0	0	0	0	0	0
28								Xyz		0	24	0	0	0	24
29								Abcd		0	0	0	28	0	28
30
Sheet1

Cell Formulas
Range	Formula
I4:I13	I4	=SUMPRODUCT(--ISNUMBER(FIND("+"&$H4&"+","+"&D$2:D$7&"+")),--($C$2:$C$7="W"),--(WEEKDAY($B$2:$B$7)<6))
J4:J13	J4	=SUMPRODUCT(--ISNUMBER(FIND("+"&$H4&"+","+"&D$2:D$7&"+")))-I4
K4:K13	K4	=SUMPRODUCT(--ISNUMBER(FIND("+"&$H4&"+","+"&E$2:E$7&"+")),--($C$2:$C$7="W"),--(WEEKDAY($B$2:$B$7)<6))
L4:L13	L4	=SUMPRODUCT(--ISNUMBER(FIND("+"&$H4&"+","+"&E$2:E$7&"+")))-K4
M4:M13	M4	=SUMPRODUCT(--ISNUMBER(FIND("+"&$H4&"+","+"&F$2:F$7&"+")),--($C$2:$C$7="W"),--(WEEKDAY($B$2:$B$7,16)<6))
N4:N13	N4	=SUMPRODUCT(--ISNUMBER(FIND("+"&$H4&"+","+"&F$2:F$7&"+")))-M4
A2:A7	A2	=TEXT(B2,"DDD")
J20:N29	J20	=J4*$J$15*J$16
O20:O29	O20	=SUM(J20:N20)

I have assumed that:

- Names will be in exactly this format: Abc+Def+Mno, and not, for example: Abc + Def &Mno. In practice, I'd expect data entry variations to occur.
- Column C can contain only W or P - and hence that if the entry is not W it must be P.

Are your loadings correct, e.g. for an afternoon shift, is the loading 1.50, or the pay rate 1.50 and hence the loading 0.50?