Count unique values based on criteria in different column

Nelson78

Active Member
Joined
Sep 11, 2017
Messages
449
Hello.

I've in column D a lot of values with duplicates.

I've to count how many unique values in column D in relation to the value in column K same row.

For example: I need to know how many unique values in column D when column K = ABC12345

Thank's.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,211
Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(K2:K10="ABC12345",IF(LEN(D2:D10)>0,MATCH(D2:D10,D2:D10,0))),ROW(D2:D10)-ROW(D2)+1)>0,1))

Adjust the ranges accordingly.

Hope this helps!
 

Nelson78

Active Member
Joined
Sep 11, 2017
Messages
449
Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(K2:K10="ABC12345",IF(LEN(D2:D10)>0,MATCH(D2:D10,D2:D10,0))),ROW(D2:D10)-ROW(D2)+1)>0,1))

Adjust the ranges accordingly.

Hope this helps!
I'm going to try.

Anyway, perhaps in VBA is easier.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,633
Messages
5,488,022
Members
407,617
Latest member
Samanthad2007

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top