Counting unique values based on 2 columns

T

Tee51

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

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

isabelle

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

isabelle

correction:

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

Vacuum Sealed

correction:

=COUNT(1/FREQUENCY(IF(F2:F100="Working",MATCH(D2:D100,D2:D100,0)),ROW(INDIRECT("1:"&ROWS(D2:D100)))))
Click to expand...
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting unique values in multiple columns 4
Create a list of unique entries based on several columns 1
count unique values if 2 variables 3
Excel Pivot Table - Counting Unique Values within one column of da 1
Find unique values using 2 variables 2
counting unique items(values or text) 11
Count Unique Values in 1 Column based on Date Range in another Column 14
unique values among duplicates without considering specific words 1

Top