Formula: count co occurrence of elements of 2 columns in one row.

[Vlad_2019]

Dear community,

I need a formula that would count how many times an element of one column co-occurs with another element in each row. The input is represented in Figure 1. The result (see Figure 2) could look as follows. The formula counts how many times H* co-occurs with n and p. In this case, it would be 1 for n and 2 for p. The same calculation would be performed with L+H*. I think that the countifformula can be applied here however, I do not know how to create a link between the two elements, n and p.

=COUNTIF(F2:F343; "H*"//how to link it with p/n?).

I am glad about every feedback and would be very grateful if you could help me. I work with a rather big data bank and. It is barely possible to count the elements manually and without making a mistake.


Figure 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]pre-nuclear/ nuclear[/TD]
[TD]Symbol[/TD]
[/TR]
[TR]
[TD]p[/TD]
[TD]H*[/TD]
[/TR]
[TR]
[TD]n[/TD]
[TD]L+H*[/TD]
[/TR]
[TR]
[TD]p[/TD]
[TD]H*[/TD]
[/TR]
[TR]
[TD]p[/TD]
[TD]L+H*[/TD]
[/TR]
[TR]
[TD]n[/TD]
[TD]H*[/TD]
[/TR]
</tbody>[/TABLE]

Figure 2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Symbol[/TD]
[TD]n[/TD]
[TD]p[/TD]
[/TR]
[TR]
[TD]H*[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]L+H*[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel.
How about

Excel 2013/2016
	A	B	C	D	E	F
1	pre-nuclear/ nuclear	Symbol		Symbol	n	p
2	p	H*		H*	1	2
3	n	L+H*		L+H*	1	1
4	p	H*
5	p	L+H*
6	n	H*
Data

Cell Formulas
Range	Formula
E2	=COUNTIFS($B$2:$B$6,$D2,$A$2:$A$6,E$1)