Count unique values in 1 column where cells in another show non-bl

  • Thread starter Thread starter Sarah (OGI)
  • Start date Start date
S

Sarah (OGI)

I have a spreadsheet whereby I need to be able to count how many unique items
are in column D (D5 onwards), where each corresponding value in column O is a
non-blank.

Is there an easy way to do this and if so, how?

Many thanks in advance.
 
=SUM(--(FREQUENCY(IF((D5:D2000<>"")*(O5:O2000<>""),MATCH(D5:D2000,D5:D2000,0)),ROW(INDIRECT("1:"&ROWS(D5:D2000))))>0))

this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter
 
Sarah,

Try this

=MROUND(SUM(IF(LEN(D5:D20),IF(O5:O20<>"",1/COUNTIF(D5:D20,D5:D20)))),1)

This is an array formula and musr be entered by pressing CTRL+Shift+Enter
and NOT just enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't simply type these yourself. Change D20, O20
to the range you want but you then must re=enter as an array.

Mike
 
I should have mentioned if this returns a #NAME error then activate the
analysis toolpak

Tools|Addins
Check analysis toolpak.

Mike
 

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 4
Counting Unique Pairs of Cells 6
counting unique values of text 4
Counting unique values in column 3
Counting unique values in multiple columns 4
Need Array Formula for Counting Blank & Non Blank Cells (Multiple Criteria) 1
counting blank cells within two columns 2
Conditional count of unique values 5

Back
Top