Counting unique values based on 2 columns
 

I have been struggling all day to find the correct formula to use when trying to count unique values using different columns. I have a multiple column spreadsheet and I would like to identify the number of unique values (text & alpha) in column D (job #) that has a specific status which is in column F. For example, see below, the working status has 3 unique job#'s. Can someone help please?

Job# Status
100 Initial
201 Working
634 Working
201 Working
978 Completed
978 Working

Counting unique values based on 2 columns
 

hi Tee51,


array formula to validate with ctrl + shift + enter

=COUNT(1/FREQUENCY(IF(D2:D100,MATCH(F2:F100,F2:F100,0)),ROW (INDIRECT("1:"&ROWS(F2:F100)))))

--
isabelle



Le 2012-03-14 15:38, Tee51 a écrit :
I have been struggling all day to find the correct formula to use when
trying to count unique values using different columns. I have a multiple
column spreadsheet and I would like to identify the number of unique
values (text& alpha) in column D (job #) that has a specific status
which is in column F. For example, see below, the working status has 3
unique job#'s. Can someone help please?

Job# Status
100 Initial
201 Working
634 Working
201 Working
978 Completed
978 Working

Counting unique values based on 2 columns
 

correction:

=COUNT(1/FREQUENCY(IF(F2:F100="Working",MATCH(D2:D100,D2:D1 00,0)),ROW(INDIRECT("1:"&ROWS(D2:D100)))))

--
isabelle

Le 2012-03-15 09:05, isabelle a écrit :
hi Tee51,


array formula to validate with ctrl + shift + enter

=COUNT(1/FREQUENCY(IF(D2:D100,MATCH(F2:F100,F2:F100,0)),ROW (INDIRECT("1:"&ROWS(F2:F100)))))

Counting unique values based on 2 columns
 

On 16/03/2012 12:09 AM, isabelle wrote:
correction:

=COUNT(1/FREQUENCY(IF(F2:F100="Working",MATCH(D2:D100,D2:D1 00,0)),ROW(INDIRECT("1:"&ROWS(D2:D100)))))


Just to add to Isabelle's very nice work is you have to Array Enter the
formula with <CTRL-SHIFT-ENTER for it to work...

HTH
Mick.