Thread: Calculate number of unique values in non adjacent cells

Hello

I need to calculate the number of unique cells within a range of approximately 190 non adjacent cells.

The cells are all in the same column but every other row i.e. C14, C16, C18, C20 etc.

The cells contain text of client references compromising both letters and numbers, together with some blank cells that need ignoring.

I have found a way of doing it if all the cells are adjacent in a column but am struggling with non adjacent cells.

In the worksheet I have grouped and named all the cells that need considering 'clients', and I was hoping for a formula (possibly utilising the name) to return the number of unique cells.

In the attached file the coloured cells are the ones that need considering (the blank ones would ordinarily have other unrelated data in) and the number returned in this case would be 5.

The cells form part of a much more extensive spreadsheet so unfortunately rearranging them so they are in adjacent cells is not a possibility.

Any ideas very gratefully received - I just hope it is possible !


Many thanks

MRF

Try:

=COUNT(1/FREQUENCY(IF(MOD(ROW(A1:A11),2)=1,IF(A1:A11<>"",MATCH(A1:A11,A1:A11,0))),ROW(A1:A11)-MIN(ROW(A1:A11))+1))

adjust ranges to suit.

This assumes you are checking every odd row starting at A1, if you want even rows, change the MOD()=1 to MOD()=0

Then Confirm the formula with CTRL+SHIFT+ENTER not just ENTER

Try:

=COUNT(1/FREQUENCY(IF(MOD(ROW(A1:A11),2)=1,IF(A1:A11<>"",MATCH(A1:A11,A1:A11,0))),ROW(A1:A11)-MIN(ROW(A1:A11))+1))

adjust ranges to suit.

This assumes you are checking every odd row starting at A1, if you want even rows, change the MOD()=1 to MOD()=0

Then Confirm the formula with CTRL+SHIFT+ENTER not just ENTER