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]

cool,
can I replace 3 IF, one COUNTIF function?
I'm asking, because I have 5 or 7 condition.

regards

 

[Aladin Akyurek]

cool,
can I replace 3 IF, one COUNTIF function?
I'm asking, because I have 5 or 7 condition.

regards

No, we don't want COUNTIF(S)...

If most conditions are associated with an equality test, we can resort to concatenation in order to avoid the inevitable performance problems for each condition would augment the costs.

What follows illustrates what concatenation involves.

1. Activate CSTG.

2. In Z2 enter and copy down (the concatenation range)...

=$B2&"|"&$G2&"|"&IF($Q2="","",$Q2)

3. Define Ivec in Formulas | Name Manager as referring to:

=ROW(CSTG!$Z$2:$Z$100)-ROW(CSTG!$Z$2)+1

4. Now invoke... Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH($C2&"*"&"Completed",CSTG!$G$2:$G$100)),
    MATCH(CSTG!$Z$2:$Z$100,CSTG!$Z$2:$Z$100,0)),Ivec),1))

5. Verify that [4] behaves as intended.

6. Add other condition ranges to the concatenation range, which involve an equality test.

 

[Forestq]

I see your point, thx! I know now how to change, modify list of uniq values.
Perfect!

thanks Aladin!

 

[Aladin Akyurek]

I see your point, thx! I know now how to change, modify list of uniq values.
Perfect!

thanks Aladin!

You are welcome.