how to make a formulaL: count if a cell contains text and countif specific information from a table

Question

I'm creating a schedule for work. HUGE project!I have a table "Levels" containing the following:LAST&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FIRST&nbsp; &nbsp; &nbsp; &nbsp; LEVELSmith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; John&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2Jean&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Barbara&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2Doe&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Jane&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3Summers&nbsp; &nbsp;Steve&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;I have a range of cells that look like the following:&nbsp;&nbsp;&nbsp;NAMELEVEL&nbsp;SMTWRFSJohn2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;T7T7T7Barbara2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;T7T7P15Jane3&nbsp;&nbsp;&nbsp;&nbsp;T7&nbsp;D7T7Steve1&nbsp;&nbsp;D7&nbsp;&nbsp;T7T7D7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;level 1&nbsp;&nbsp;0101111Level 2&nbsp;&nbsp;0001222Level 3&nbsp;&nbsp;0000011&nbsp;I would like to create a formula (where the bolded numbers are) that counts the number of levels&nbsp;looked up from the table "Levels".&nbsp;&nbsp;Does this make any sense? is this even possible? Please let me know if I can clarify anything. Pretty desperate, I don't want to hand count each cell. I have 65 employees I would need to do this for!

detlef_lewin · Answer

Hello Dylan&nbsp;I get opposite results in the Wednesday column.Select D7:J7, copy the formula into the formula bar and press CTRL-SHIFT-ENTER.{=MMULT({1,1,1,1},("Level "&amp;$B$2:$B$5=A12)*($D$2:$J$5&lt;&gt;""))}&nbsp;

dylan bastien · Answer

hmm. didn't seem to work.

Forum Discussion

how to make a formulaL: count if a cell contains text and countif specific information from a table

Share

Resources


NAME	LEVEL	S	M	T	W	R	F	S
John	2					T7	T7	T7
Barbara	2					T7	T7	P15
Jane	3				T7		D7	T7
Steve	1		D7			T7	T7	D7

level 1		0	1	0	1	1	1	1
Level 2		0	0	0	1	2	2	2
Level 3		0	0	0	0	0	1	1