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:D2 000))))0))

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

--
__________________________________
HTH

Bob

"Sarah (OGI)" wrote in message
...
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.

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

"Sarah (OGI)" wrote:

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.

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

Tools|Addins
Check analysis toolpak.

Mike

"Mike H" wrote:

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

"Sarah (OGI)" wrote:

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.

"Mike H" wrote:

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

Tools|Addins
Check analysis toolpak.

Mike

"Mike H" wrote:

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

"Sarah (OGI)" wrote:

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.