VBA or Formula for count unique under the 1 | X result

[Kishan]

Using Excel 2000

Hi,

I got some results in the column "C" starting from C6 to down the column.

In the column "E" I have 9 unique results I want to count how much time below the result 1 | X each unique results has appeared.


As per my manual count result are shown in the column "F"

*	A	B	C	D	E	F	G
1
2
3					Below The 1 | X	Times
4
5			P1		Following Patterns Appear	Times
6			X | 1		1 | 1	4
7			X | X		1 | X	5
8			X | 1		1 | 2	0
9			2 | 1		X | 1	2
10			1 | X		X | X	1
11			X | 2		X | 2	0
12			X | 1		2 | 1	1
13			1 | X		2 | X	1
14			1 | 1		2 | 2	1
15			1 | X
16			1 | X
17			1 | 1
18			1 | 1
19			1 | 1
20			1 | X
21			2 | 2
22			1 | X
23			2 | 1
24			1 | X
25			X | 1
26			X | 2
27			X | 1
28			2 | 1
29			X | X
30			1 | X
31			X | 2
32			1 | 1
33			1 | 1
34			2 | 1
35			1 | 1
36			1 | X
37			1 | X
38			X | X
39			X | X
40			1 | 1
41			1 | X
42			1 | 1
43			1 | X
44			1 | 1
45			1 | X
46			1 | X
47			2 | X
48			1 | 1
49			1 | 1
50			X | 1
51			1 | X
52			1 | X
53			1 | X
54			X | 1
55			X | X
56			2 | X
57			X | 1
58			1 | X
59			X | 2
60			1 | 1
61			1 | 2
62			1 | X
63			1 | X
64
65

Please I need some formula or better VBA solution for my problem


Thank you in advance


Regards,

Kishan

 

[Fluff]

I don't agree with your results, but try

ZFluff.xlsm
	A	B	C	D	E	F	G
1
2
3					1 | X	Times
4
5			P1		Following Patterns Appear	Times
6			X | 1		1 | 1	4	4
7			X | X		1 | X	5	6
8			X | 1		1 | 2	0	0
9			2 | 1		X | 1	2	2
10			1 | X		X | X	1	1
11			X | 2		X | 2	0	3
12			X | 1		2 | 1	1	1
13			1 | X		2 | X	1	1
14			1 | 1		2 | 2	1	1
15			1 | X
16			1 | X
17			1 | 1
18			1 | 1
19			1 | 1
20			1 | X
21			2 | 2
22			1 | X
23			2 | 1
24			1 | X
25			X | 1
26			X | 2
27			X | 1
28			2 | 1
29			X | X
30			1 | X
31			X | 2
32			1 | 1
33			1 | 1
34			2 | 1
35			1 | 1
36			1 | X
37			1 | X
38			X | X
39			X | X
40			1 | 1
41			1 | X
42			1 | 1
43			1 | X
44			1 | 1
45			1 | X
46			1 | X
47			2 | X
48			1 | 1
49			1 | 1
50			X | 1
51			1 | X
52			1 | X
53			1 | X
54			X | 1
55			X | X
56			2 | X
57			X | 1
58			1 | X
59			X | 2
60			1 | 1
61			1 | 2
62			1 | X
63			1 | X
Model

Cell Formulas
Range	Formula
G6:G14	G6	=COUNTIFS($C$6:$C$63,$E$3,$C$7:$C$64,E6)

 

[Kishan]

Thank you Fluff, for the formula but as I am using Excel 2000 which I did mention in the post#1, so far function COUNTIFS does not work, yes I agree with you my results are not correct. It seems your formula calculations are prefect

Kind Regards
Kishan

 

[Fluff]

Apologies, forgot you were on Xl2000, how about
=SUMPRODUCT(($C$6:$C$63=$E$3)*($C$7:$C$64=E6))

 

[Kishan]

Apologies, forgot you were on Xl2000, how about
=SUMPRODUCT(($C$6:$C$63=$E$3)*($C$7:$C$64=E6))

Fluff, with this new formula you have provided I am getting all the result perfect.

I do appreciate your kind help

Kind Regards
Kishan

 

[Fluff]

You're welcome & thanks for the feedback