Formula for counting differing Excel rows 4

[beanxx]

I'm sure there's a simple answer but does anyone the formula that should be used for counting differing rows. As an example if Row 1 = Blue, Row 2 = Yellow, Row 3 = Blue, Row 4 = Red and Row 5 = Blue. I would like a count value of 3 to be returned which represents the number of different values. COUNTA would be ideal for this except it counts all the rows seperately and would therefore return a value of 5.

 

[KenWright]

Can you clarify your request. Are you talking about the actual colour of the rows being different, or do you mean you have a range of data eg A1:A100 with different values in them, and you would like to count the number of unique values, or you have a range such as A1:H100 and you would like to count all the rows that are unique, or something else even?

Regards
Ken...........

 

[xlbo]

I'm guessing you want to count instances of colours (based on count=3 - for Blue)
1st thing is that this is not a good idea - it can be done but only via a User Defined Function (ie using VBA). There are NO standard functions to do this and ergo, it is bad spreadsheet design to use colours alone to identify data. Hard data should ALWAYS be used and then colours used merely to guide the eye.
Something like:
Function HowMany(rng as range, colInd as integer)
for each c in rng
if c.interior.colorindex = colInd then
tempCnt = tempCnt + 1
else
end if
next
HowMany = tempCnt
end function

where rng is the roung you want to count in and colInd is the COLORINDEX of the colour you want to count

To get all the color indexes, run this on a blank sheet:
sub listColours()
For i = 1 to 56
Range("A" & i).Value = "Color Index : " & i
Range("A" & i).interior.colorindex = i
next i
end sub

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[beanxx]

Sorry perhaps colours was a bad example. It is unique values that I would like to count and not colours...and thanks for the reponse so far.

 

[GlennUK]

Hi beanxx,

you could put an array formula in like this ...

=SUM(1/COUNTIF(A1:A100,A1:A100))

entered using Ctrl-Shift-Enter instead of Enter. Adjust the ranges to match your requirements.


Glenn.

 

[beanxx]

Glenn,

Thanks but I'm not sure how the COUNTIF function would be used in this circumstance. Basically the data I am querying has in excess of 1000 rows and I want to count just to coutnall rows where the entry is unique. The actual source data follows an identification number format such as DTF03327, DTF05876, etc. So if for example DTF03327 appears in numeorus rows, it is only counted once. I suppose am looking for a COUNTA function which counts unique row values only?

 

[GlennUK]

Hi Beanxx,

sounds like you didn't actually try my method, 'cos it does exactly what you want.

I'll explain ... suppose you have entries like this
DTF1
DTF2
DTF3
DTF1
DTF1
DTF2
my formula actually creates a list like this using COUNTIF
3
2
1
3
3
2
which when inversed ( placed under 1/ ) gives a list like this
1/3
1/2
1/1
1/3
1/3
1/2
which when added together give 3, i.e. 3 unique entries.

Try it and see.
Glenn.

 

[xlbo]

Works for me....Glenn - very nice formula - only just twigged how nice it really is and for the record, - tried to give you a star but the mark this......link is taking forever - have a virtual star instead ;-)

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[KenWright]

The =SUM(1/COUNTIF(A1:A100,A1:A100)) array works fine if all the data is numeric, but if you have a mixture of text and numeric and you want just unique numeric data you can use the following:-

=SUM(N(FREQUENCY(A1:A10,A1:A10)>0)) Array Entered

Regards
Ken..............

 

[GlennUK]

Thanks Geoff,

virtual star appreciated, especially coming from a TipMaster of the week.

Cheers, Glenn.

 

[xlbo]

Much quicker today - have an actual star as well as the virtual one (I like that formula a lot ;-) )

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[beanxx]

Glenn,

Sorry to take a while to respond but I've been off line. Glenn very clever I see how the formula works now and this has resolved my dilema so thanks very much. Oh and I have awarded you with a well deserved star.

Thanks again,

Justin

 

[KenWright]

For those that don't want to array enter them, you can use SUMPRODUCT:-

=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))

=SUMPRODUCT(N(FREQUENCY(A1:A10,A1:A10)>0))

Regards
Ken..............