Forestq

Active Member
Joined
May 9, 2010
Messages
482
Hi,

I have function to count how many many times name from column C (sheet M1) appear in range (sheet CSTG, column B):
Code:
=COUNTIFS(CSTG!$B:$B,'M1'!C7)
Column CColumn D - only count
Audi5
BMW7
VW10
Fiat3

<tbody>
</tbody>

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.

Column CColumn DColumn E - diffrent version
Audi32
BWM75
VW104
Fiat32

<tbody>
</tbody>

What should I do?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I found something like below:

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


but its not working.
 
Upvote 0
Control + Shift + Enter


Excel 2010
CDEF
1Column C
2Audi1.21
3BWM2.02
4Audi1.2
5BWM3.0
Sheet26
Cell Formulas
RangeFormula
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.
 
Upvote 0
HI,

I made like:
Code:
=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.
 
Upvote 0
HI,

I made like:
Code:
=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.
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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:
Rich (BB code):

=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))
 
Upvote 0
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" ?
 
Upvote 0
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:
Rich (BB code):

=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))
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top