I want to tally a column but dependent on the text in the next column

[Tristram_ZX81]

Hello. I'm using Office 365. I want to type a formula into the cells in the table below (columns M-S) that would tally how many occurrences there are of the D, M and N letters in the I column for each day of the week. So Monday should read

Mon
D 0
M 4
M 1

Tuesday should read

Tue
D 3
M 1
N 2

And so on. Please can someone advise?

 

[etaf]

can you change the table to use the same full day name - ????

if so then a simple countifs()
otherwise a sumproduct - see below

Book5
	H	I	J	K	L	M	N	O	P
3
4
5		m	Monday			Monday	Tuesday	Wednesday	Thursday
6		m	Monday		D	0	3	1	0
7		n	Monday		M	4	1	2	0
8		m	Monday		N	1	2	3	0
9		m	Monday
10		m	Tuesday
11		n	Tuesday
12		n	Tuesday
13		d	Tuesday
14		d	Tuesday
15		d	Tuesday
16		d	wednesday
17		n	wednesday
18		n	wednesday
19		n	wednesday
20		m	wednesday
21		m	wednesday
Sheet1

Cell Formulas
Range	Formula
M6:P8	M6	=COUNTIFS($J$5:$J$21,M$5,$I$5:$I$21,$L6)

Book5
	H	I	J	K	L	M	N	O	P	Q
3
4
5		m	Monday			Mon	Tue	Wed	Thu
6		m	Monday		D	0	3	1	0
7		n	Monday		M	4	1	2	0
8		m	Monday		N	1	2	3	0
9		m	Monday
10		m	Tuesday
11		n	Tuesday
12		n	Tuesday
13		d	Tuesday
14		d	Tuesday
15		d	Tuesday
16		d	wednesday
17		n	wednesday
18		n	wednesday
19		n	wednesday
20		m	wednesday
21		m	wednesday
22
Sheet1

Cell Formulas
Range	Formula
M6:P8	M6	=SUMPRODUCT( (LEFT($J$5:$J$21,3)=M$5)*($I$5:$I$21=$L6) )

 

[Tristram_ZX81]

Thanks Etaf. For some reason I can't get that formula to work in my document, as per the screen grab below:

 

[Fluff]

Try using the countifs formula that etaf posted.

 

[Tristram_ZX81]

Missed that one. Work snow. Thanks Etaf, thanks Fluff.

 

[etaf]

you are welcome