Counting formula by multiple columns

[asyamonique]

COLUMN-M	COLUMN-N		COLUMN-P	COLUMN-R
1	1		1	6
1	2		2	6
1	3		3	5
1	4		4	3
1	5
1	6
2	1
2	2
2	3
2	4
2	5
2	6
3	1
3	2
3	3
3	6
3	2
4	4
4	5
4	1

<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

Hello,
What is the formula for to count Column-P- datas base on Column-N- ?
Thanks

 

[Eric W]

Based on your example:

=COUNTIF(M:M,P2)

But are you looking for something more, like you want a unique count? In that case the "3" value would be 4, since "2" is duplicated.

 

[asyamonique]

Hello Eric,
Thanks for reply, but my question is a bit different..
So column M has datas from 1 to 4 (multiple) and those datas I've placed on column P after sort the duplicates(actually I have huge of datas that is sample only)
I need the formula on column R which will show me amount of column N datas base on column M
Hope its clear..

 

[Eric W]

I'm afraid I still don't see the difference. My formula replicates your results exactly. See below:

	M	N	O	P	Q	R	S	T
1	COLUMN-M	COLUMN-N		COLUMN-P		COLUMN-R
2	1	1		1		6	6
3	1	2		2		6	6
4	1	3		3		5	4
5	1	4		4		3	3
6	1	5
7	1	6
8	2	1
9	2	2
10	2	3
11	2	4
12	2	5
13	2	6
14	3	1
15	3	2
16	3	3
17	3	6
18	3	2
19	4	4
20	4	5
21	4	1

<tbody>
</tbody>

Sheet11​

Worksheet Formulas

Cell	Formula
R2	=COUNTIF(M:M,P2)

<tbody>
</tbody>

Array Formulas Cell Formula S2 {=SUM(SIGN(FREQUENCY(IF($M$2:$M$21=P2,$N$2:$N$21),$N$2:$N$21)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Or if you want the formula that creates the unique count, that's in S2.

 

[asyamonique]

Thank you