Uniq values

[Forestq]

Hi,

I have function to count how many many times name from column C (sheet M1) appear in range (sheet CSTG, column B):

=COUNTIFS(CSTG!$B:$B,'M1'!C7)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column C[/TD]
[TD]Column D - only count[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]VW[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Fiat[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Now in column Q (sheet CSTG) I have different values, like: 1, 1.0, 1.2, 2.0, 3.2 etc.

I need to count how many different values is for each name for column C.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E - diffrent version[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]BWM[/TD]
[TD]7[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]VW[/TD]
[TD]10[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Fiat[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

What should I do?

 

[Forestq]

I found something like below:

​=SUMPRODUCT(1/COUNTIF(Q2:Q2000,Q2:Q2000))

but its not working.

 

[vllgv]

Control + Shift + Enter

Excel 2010
	C	D	E	F
1	Column C
2	Audi	1.2		1
3	BWM	2.0		2
4	Audi	1.2
5	BWM	3.0
Sheet26

Cell Formulas
Range	Formula
F2	{=SUM(IF(FREQUENCY(IF($C$2:$C$5<>"", IF($C$2:$C$5=C2,MATCH($D$2:$D$5,$D$2:$D$5,0))), ROW($C$2:$C$5)-ROW($A$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Forestq]

HI,

I made like:

=SUM(IF(FREQUENCY(IF($C$7:$C$168<>"",IF($C$7:$C$168=C2,MATCH(CSTG!$Q$2000:$Q$2000,CSTG!$Q$2000:$Q$2000,0))),ROW($C$168:$C$168)-ROW($C$2)+1),1))

but Im getting O or N/A. My rows with values starts from row 7, and duplicates values normally starts from row 2.

 

[Aladin Akyurek]

HI,

I made like:

=SUM(IF(FREQUENCY(IF($C$7:$C$168<>"",IF($C$7:$C$168=C2,MATCH(CSTG!$Q$2000:$Q$2000,CSTG!$Q$2000:$Q$2000,0))),ROW($C$168:$C$168)-ROW($C$2)+1),1))

but Im getting O or N/A. My rows with values starts from row 7, and duplicates values normally starts from row 2.

Care to state clearly where your data is? You mention a confusing set of columns and sheet names.

 

[Forestq]

sheet CSTG - data source, column B- repeated values like Audi, BWM, column Q - diffrent version

sheet M1 - my template, where I keep in column C uniq values from column B(sheet CSTG), and here, I need to add count of diffrent version

 

[vllgv]

try: =SUM(IF(FREQUENCY(IF(CSTG!$B$2:$B$2000<>"",
IF(CSTG!$B$2:$B$2000=C2,MATCH(CSTG!$Q$2:$Q$2000,CSTG!$Q$2:$Q$2000,0))),
ROW(CSTG!$Q$2:$Q$2000)-ROW($A$2)+1),1))

w Control Shift Enter

 

[Aladin Akyurek]

sheet CSTG - data source, column B- repeated values like Audi, BWM, column Q - diffrent version

sheet M1 - my template, where I keep in column C uniq values from column B(sheet CSTG), and here, I need to add count of diffrent version

In D2 of M1 (sheet M1) control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(CSTG!$Q$2:$Q$100<>"",IF(CSTG!$B$2:$B$100=$C2,
    MATCH(CSTG!$Q$2:$Q$100,CSTG!$Q$2:$Q$100,0))),
    ROW(CSTG!$Q$2:$Q$100)-ROW(CSTG!$Q$2)+1),1))

 

[Forestq]

thanks Aladin, works great!

one more question: how can I add to the function one condition, let say:
- values from column G sheet CSTG should be = "Completed" ?

 

[Aladin Akyurek]

thanks Aladin, works great!

one more question: how can I add to the function one condition, let say:
- values from column G sheet CSTG should be = "Completed" ?

Control+shift+enter and copy down:

=SUM(IF(FREQUENCY(IF(CSTG!$Q$2:$Q$100<>"",IF(CSTG!$B$2:$B$100=$C2,
    IF(CSTG!$G$2:$G$100="Completed",
    MATCH(CSTG!$Q$2:$Q$100,CSTG!$Q$2:$Q$100,0)))),
    ROW(CSTG!$Q$2:$Q$100)-ROW(CSTG!$Q$2)+1),1))