Counting a list of wild cards from a range of cells

[BeccaB131]

Good Evening All

Was wondering if anybody had a solution.

I use a labour diary: Where Emp 1 to 9 are typed as First name and initial ie Emp 1 = Becca R, Emp 2 = John D etc but have made anonymous for this request. So we enter everyone on site (b1-j14) and use a count if table to ensure no one is missed using the (b17-i25)- we also add others in that are not listed in B17 to B25 - is there a way to count all the wild cards from B17-B25 in each row (2-14) i tried: in cell A2'=countif(C2:I2,"*"&$B$17:$B$25&"*") and =countif(C2:I2,"*"&$B$17&"*":"*"&$B$25&"*") neither of which seems to work - wanted it to = 12 as excludes the 'Other 1' as not in the wild card list B17-25

Many thanks in advance if anyone does have a solution

	A	B	C	D	E	F	G	H	I
1​			Day1	Day2	Day3	Day4	Day5	Day6	Day7
2​		LOCATION1	Emp 1, Emp 2, Emp 3, Other 1				Emp 1, Emp 2, Emp 3, Other 1	Emp 1, Emp 2, Emp 3, Other 1	Emp 1, Emp 2, Emp 3, Other 1
3​		LOCATION2	Emp 4	Emp 4	Emp 4	Emp 4	Emp 4	Emp 4	Emp 4
4​		LOCATION3		Emp 1, Emp 2, Emp 3, Other 1
5​		LOCATION4	Emp 5, Emp 7, Emp 8	Emp 5, Emp 7, Emp 9	Emp 5, Emp 7,
6​		LOCATION5		Other 1, Other1	Emp 1, Emp 2, Emp 3, Other 1
7​		LOCATION6
8​		LOCATION7				Emp 5, Emp 7	Emp 5, Emp 8	Emp 5, Emp 9	Emp 5,
9​		LOCATION8
10​		LOCATION9	Other 2, Other 5	Other 2, Other 6	Other 2, Other 7	Other 2, Other 8	Other 2, Other 9	Other 2, Other 10	Other 2, Other 11
11​		LOCATION10		Emp 6, Other 3, Other 4	Emp 6, Other 3, Other 4	Emp 1, Emp 2, Emp 3, Other 1,Emp 6, Other 3, Other 4	Emp 6, Other 3, Other 4	Emp 6, Other 3, Other 4	Emp 6, Other 3, Other 4
12​		LOCATION11	Emp 6, Other 3, Other 4
13​		LOCATION12
14​		LOCATION13
15​
16​
17​		Emp 1	1​	1​	1​	1​	1​	1​	1​
18​		Emp 2	1​	1​	1​	1​	1​	1​	1​
19​		Emp 3	1​	1​	1​	1​	1​	1​	1​
20​		Emp 4	1​	1​	1​	1​	1​	1​	1​
21​		Emp 5	1​	1​	1​	1​	1​	1​	1​
22​		Emp 6	1​	1​	1​	1​	1​	1​	1​
23​		Emp 7	1​	1​	1​	1​	0​	0​	0​
24​		Emp 8	1​	0​	0​	0​	1​	0​	0​
25​		Emp 9	0​	1​	0​	0​	0​	1​	0​
26​

Same table showing formulas

	A	B	C	D	E	F	G	H	I
1			Day1	Day2	Day3	Day4	Day5	Day6	Day7
2		LOCATION1	Emp 1, Emp 2, Emp 3, Other 1				Emp 1, Emp 2, Emp 3, Other 1	Emp 1, Emp 2, Emp 3, Other 1	Emp 1, Emp 2, Emp 3, Other 1
3		LOCATION2	Emp 4	Emp 4	Emp 4	Emp 4	Emp 4	Emp 4	Emp 4
4		LOCATION3		Emp 1, Emp 2, Emp 3, Other 1
5		LOCATION4	Emp 5, Emp 7, Emp 8	Emp 5, Emp 7, Emp 9	Emp 5, Emp 7,
6		LOCATION5			Emp 1, Emp 2, Emp 3, Other 1
7		LOCATION6
8		LOCATION7				Emp 5, Emp 7	Emp 5, Emp 8	Emp 5, Emp 9	Emp 5,
9		LOCATION8
10		LOCATION9	Other 2, Other 5	Other 2, Other 6	Other 2, Other 7	Other 2, Other 8	Other 2, Other 9	Other 2, Other 10	Other 2, Other 11
11		LOCATION10		Emp 6, Other 3, Other 4	Emp 6, Other 3, Other 4	Emp 1, Emp 2, Emp 3, Other 1,Emp 6, Other 3, Other 4	Emp 6, Other 3, Other 4	Emp 6, Other 3, Other 4	Emp 6, Other 3, Other 4
12		LOCATION11	Emp 6, Other 3, Other 4
13		LOCATION12
14		LOCATION13
15
16
17		Emp 1	=COUNTIF(D$3:D$15,"*"&$C18&"*")	=COUNTIF(E$3:E$15,"*"&$C18&"*")	=COUNTIF(F$3:F$15,"*"&$C18&"*")	=COUNTIF(G$3:G$15,"*"&$C18&"*")	=COUNTIF(H$3:H$15,"*"&$C18&"*")	=COUNTIF(I$3:I$15,"*"&$C18&"*")	=COUNTIF(J$3:J$15,"*"&$C18&"*")
18		Emp 2	=COUNTIF(D$3:D$15,"*"&$C19&"*")	=COUNTIF(E$3:E$15,"*"&$C19&"*")	=COUNTIF(F$3:F$15,"*"&$C19&"*")	=COUNTIF(G$3:G$15,"*"&$C19&"*")	=COUNTIF(H$3:H$15,"*"&$C19&"*")	=COUNTIF(I$3:I$15,"*"&$C19&"*")	=COUNTIF(J$3:J$15,"*"&$C19&"*")
19		Emp 3	=COUNTIF(D$3:D$15,"*"&$C20&"*")	=COUNTIF(E$3:E$15,"*"&$C20&"*")	=COUNTIF(F$3:F$15,"*"&$C20&"*")	=COUNTIF(G$3:G$15,"*"&$C20&"*")	=COUNTIF(H$3:H$15,"*"&$C20&"*")	=COUNTIF(I$3:I$15,"*"&$C20&"*")	=COUNTIF(J$3:J$15,"*"&$C20&"*")
20		Emp 4	=COUNTIF(D$3:D$15,"*"&$C21&"*")	=COUNTIF(E$3:E$15,"*"&$C21&"*")	=COUNTIF(F$3:F$15,"*"&$C21&"*")	=COUNTIF(G$3:G$15,"*"&$C21&"*")	=COUNTIF(H$3:H$15,"*"&$C21&"*")	=COUNTIF(I$3:I$15,"*"&$C21&"*")	=COUNTIF(J$3:J$15,"*"&$C21&"*")
21		Emp 5	=COUNTIF(D$3:D$15,"*"&$C22&"*")	=COUNTIF(E$3:E$15,"*"&$C22&"*")	=COUNTIF(F$3:F$15,"*"&$C22&"*")	=COUNTIF(G$3:G$15,"*"&$C22&"*")	=COUNTIF(H$3:H$15,"*"&$C22&"*")	=COUNTIF(I$3:I$15,"*"&$C22&"*")	=COUNTIF(J$3:J$15,"*"&$C22&"*")
22		Emp 6	=COUNTIF(D$3:D$15,"*"&$C23&"*")	=COUNTIF(E$3:E$15,"*"&$C23&"*")	=COUNTIF(F$3:F$15,"*"&$C23&"*")	=COUNTIF(G$3:G$15,"*"&$C23&"*")	=COUNTIF(H$3:H$15,"*"&$C23&"*")	=COUNTIF(I$3:I$15,"*"&$C23&"*")	=COUNTIF(J$3:J$15,"*"&$C23&"*")
23		Emp 7	=COUNTIF(D$3:D$15,"*"&$C24&"*")	=COUNTIF(E$3:E$15,"*"&$C24&"*")	=COUNTIF(F$3:F$15,"*"&$C24&"*")	=COUNTIF(G$3:G$15,"*"&$C24&"*")	=COUNTIF(H$3:H$15,"*"&$C24&"*")	=COUNTIF(I$3:I$15,"*"&$C24&"*")	=COUNTIF(J$3:J$15,"*"&$C24&"*")
24		Emp 8	=COUNTIF(D$3:D$15,"*"&$C25&"*")	=COUNTIF(E$3:E$15,"*"&$C25&"*")	=COUNTIF(F$3:F$15,"*"&$C25&"*")	=COUNTIF(G$3:G$15,"*"&$C25&"*")	=COUNTIF(H$3:H$15,"*"&$C25&"*")	=COUNTIF(I$3:I$15,"*"&$C25&"*")	=COUNTIF(J$3:J$15,"*"&$C25&"*")
25		Emp 9	=COUNTIF(D$3:D$15,"*"&$C26&"*")	=COUNTIF(E$3:E$15,"*"&$C26&"*")	=COUNTIF(F$3:F$15,"*"&$C26&"*")	=COUNTIF(G$3:G$15,"*"&$C26&"*")	=COUNTIF(H$3:H$15,"*"&$C26&"*")	=COUNTIF(I$3:I$15,"*"&$C26&"*")	=COUNTIF(J$3:J$15,"*"&$C26&"*")
26

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Location"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Other Columns", {{"Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Value", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Value", "Attribute"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Value", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Count"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Value", "Day1", "Day2", "Day3", "Day4", "Day5", "Day6", "Day7"})
in
    #"Reordered Columns"

Book4
	B	C	D	E	F	G	H	I	J	K
2	Location	Day1	Day2	Day3	Day4	Day5	Day6	Day7
3	LOCATION1	Emp 1, Emp 2, Emp 3, Other 1				Emp 1, Emp 2, Emp 3, Other 1	Emp 1, Emp 2, Emp 3, Other 1	Emp 1, Emp 2, Emp 3, Other 1
4	LOCATION2	Emp 4	Emp 4	Emp 4	Emp 4	Emp 4	Emp 4	Emp 4
5	LOCATION3		Emp 1, Emp 2, Emp 3, Other 1
6	LOCATION4	Emp 5, Emp 7, Emp 8	Emp 5, Emp 7, Emp 9	Emp 5, Emp 7,
7	LOCATION5		Other 1, Other1	Emp 1, Emp 2, Emp 3, Other 1
8	LOCATION6
9	LOCATION7				Emp 5, Emp 7	Emp 5, Emp 8	Emp 5, Emp 9	Emp 5,
10	LOCATION8
11	LOCATION9	Other 2, Other 5	Other 2, Other 6	Other 2, Other 7	Other 2, Other 8	Other 2, Other 9	Other 2, Other 10	Other 2, Other 11
12	LOCATION10		Emp 6, Other 3, Other 4	Emp 6, Other 3, Other 4	Emp 1, Emp 2, Emp 3, Other 1,Emp 6, Other 3, Other 4	Emp 6, Other 3, Other 4	Emp 6, Other 3, Other 4	Emp 6, Other 3, Other 4
13	LOCATION11	Emp 6, Other 3, Other 4
14
15	Value	Day1	Day2	Day3	Day4	Day5	Day6	Day7
16				1				1
17	Emp 1	1	1	1	1	1	1	1
18	Emp 2	1	1	1	1	1	1	1
19	Emp 3	1	1	1	1	1	1	1
20	Emp 4	1	1	1	1	1	1	1
21	Emp 5	1	1	1	1	1	1	1
22	Emp 6	1	1	1	1	1	1	1
23	Emp 7	1	1	1	1
24	Emp 8	1				1
25	Emp 9		1				1
26	Other 1	1	2	1	1	1	1	1
27	Other 10						1
28	Other 11							1
29	Other 2	1	1	1	1	1	1	1
30	Other 3	1	1	1	1	1	1	1
31	Other 4	1	1	1	1	1	1	1
32	Other 5	1
33	Other 6		1
34	Other 7			1
35	Other 8				1
36	Other 9					1
37	Other1		1
Sheet1

Note that Other1<>Other 1

 

[BeccaB131]

Thanks - was wanting to exclude all the 'other' and count only the wild cards listed - as a example emp = an employee and other = a subcontractor company and not an individual - I want to count the total number of employees on location 1 (which would be 12) then location 2 (which would be 7) then location 3, 4 to 11 - thanks

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also in what way did the formula you posted not work?