Count the change from one letter to another

[vlmedia]

I have 2 columns with letters from A-G and I would like to count how many As change to Bs, As to Cs, Bs to Ds, Ds to Es, etc... Basically have a summary of the changes for each letter (changes from current to new).

Thank you in advance for your help.

 

[jtakw]

Hi,

One way:

Book3.xlsx
	A	B	C
2	A	A	7
3	A	B
4	C	D
5	D	C
6	D	E
7	E	F
8	F	G
9	F	G
10
11
12
Sheet858

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(A2:A20<>B2:B20))

 

[jtakw]

Think I misunderstood your question, may be this:

Book3.xlsx
	A	B	C	D	E
1	Current	New		List	# Changes
2	A	A		A	1
3	A	B		B	0
4	C	D		C	1
5	D	C		D	2
6	D	E		E	1
7	E	F		F	2
8	F	G		G	0
9	F	G
10
11
Sheet858

Cell Formulas
Range	Formula
E2:E8	E2	=SUMPRODUCT((A$2:A$20=D2)*(A$2:A$20<>B$2:B$20))

 

[vlmedia]

This is the result I would like to obtain, calculate the number of As that changed to a B, As to a Cs, Bs to As, Bs to Cs, etc.

Book1
	A	B	C	D	E	F
1	Current	New
2	A	B		A	A	0
3	A	B			B	2
4	C	D			C	0
5	D	C			D	0
6	D	E			E	0
7	E	F			F	0
8	F	G			G	0
9	F	G		B	A	0
10					B	0
11					C	0
12					D	0
13					E	0
14					F	0
15					G	0
16				C	A	0
17					B	0
18					C	0
19					D	1
20					E	0
21					F	0
22					G	0
Sheet1

 

[vlmedia]

This is the result I would like to obtain, calculate the number of As that changed to a B, As to a Cs, Bs to As, Bs to Cs, etc.

Book1
	A	B	C	D	E	F
1	Current	New
2	A	B		A	A	0
3	A	B			B	2
4	C	D			C	0
5	D	C			D	0
6	D	E			E	0
7	E	F			F	0
8	F	G			G	0
9	F	G		B	A	0
10					B	0
11					C	0
12					D	0
13					E	0
14					F	0
15					G	0
16				C	A	0
17					B	0
18					C	0
19					D	1
20					E	0
21					F	0
22					G	0
Sheet1

I think I figured it out, it would be a simple countifs.

 

[jtakw]

Now that you show what your expected results are, then yes, a SUMIFS will do it, how are you setting it up?

I'd recommend filling the Blanks in column D:

Book3.xlsx
	A	B	C	D	E	F
1	Current	New
2	A	B		A	A	0
3	A	B		A	B	2
4	C	D		A	C	0
5	D	C		A	D	0
6	D	E		A	E	0
7	E	F		A	F	0
8	F	G		A	G	0
9	F	G		B	A	0
10				B	B	0
11				B	C	0
12				B	D	0
13				B	E	0
14				B	F	0
15				B	G	0
16				C	A	0
17				C	B	0
18				C	C	0
19				C	D	1
20				C	E	0
21				C	F	0
22				C	G	0
Sheet859

Cell Formulas
Range	Formula
F2:F22	F2	=COUNTIFS(A$2:A$20,D2,B$2:B$20,E2)

 

[vlmedia]

Yes, I set it up just like you. Thank you for your help!

 

[jtakw]

You're very welcome, thanks for the feedback.