Count if query

[Sliepner]

I am wondering if someone can help assemble a formula. I have a rota and wish to count the amount of people on night shift. Easy enough to separate and total them with a "countif" however, when people mobilise, instead of the usual "NS" for nightshift or "1" for Day shift, we now have the addition of "M*" (1,2,3 or 4), again not really an issue, can use "Left" or "M*", but I need to only count those who will be on nightshift, and that information will be in the next column. e.g. per snip on 14th July, we have 3 people mobilising, 1 on days, 2 on nights. How can I build a formula to count the n/s who mobilise. Any help appreciated.

 

[Toadstool]

Hi Sliepner,

I'm not following as there are 4 two character entries for 14 July. Does the number indicate some kind of shift? Where should the count go and is it by Mobilize type or just a single count.

Could you show a worked example with results so we can test.

Is it something like this?

Sliepner.xlsx
	E	F	G	H	I	J	K	L	M	N	O
1		12-Jul	13-Jul	14-Jul	15-Jul	16-Jul	17-Jul	18-Jul	19-Jul	20-Jul	21-Jul
2		M2	1	1	1	1	1
3		1	1	1	1	D2
4						M2	1
5				M3	1	1	1
6
7		NS	D1
8		EL	EL	D2
9				M2	NS	NS	NS
10				M2	NS	NS	NS
11
12	Mobilized NS	0	0	2	0	0	0	0	0	0	0
Shifts

Cell Formulas
Range	Formula
G1:O1	G1	=F1+1
F12:O12	F12	=COUNTIFS(F$2:F$10,"M*",G$2:G$10,"NS")

 

[Sliepner]

Thanks for the response, what I ended up doing was:
=COUNTIF(H2:H11,"NS")+COUNTIFS(H2:H11,"m*",J2:j11,"NS")
In effect the entries represent as follows: M* what time they mobilise, 1 = Dayshift, NS = Nightshift, D* what time they demob. So I needed to count those who were on nightshift, including those who had mobilised on the day. I had forgotten about countifs.