Home |
Search |
Today's Posts |
#1
|
|||
|
|||
counting unique strings
I'm having trouble with a formula that will allow me to count the number of
unique strings in a range, however it needs to disregard any blanks in that range. In the range g4:g1000, there are only a dozen or so values, but I'm steadily entering the data. In the MS Knowledgebase (article 268001), it gives this formula. =SUM(IF(FREQUENCY(IF(LEN(G4:G1000)0,MATCH(G4:G100 0,G4:G1000,0),""), IF(LEN(G4:G1000)0,MATCH(G4:G1000,G4:G1000,0),"")) 0,1)) However, all that's coming up is "#VALUE!". Why does this not work? -- Sparky Mark http://www.moglesby.karoo.net ~~~~~~~~~~~~~~~~~~~~~~~~ |
#2
|
|||
|
|||
Think its an array formula which needs to be array-entered,
viz.: press CTRL+SHIFT+ENTER, instead of just pressing ENTER -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Sparky Mark" wrote in message ... I'm having trouble with a formula that will allow me to count the number of unique strings in a range, however it needs to disregard any blanks in that range. In the range g4:g1000, there are only a dozen or so values, but I'm steadily entering the data. In the MS Knowledgebase (article 268001), it gives this formula. =SUM(IF(FREQUENCY(IF(LEN(G4:G1000)0,MATCH(G4:G100 0,G4:G1000,0),""), IF(LEN(G4:G1000)0,MATCH(G4:G1000,G4:G1000,0),"")) 0,1)) However, all that's coming up is "#VALUE!". Why does this not work? -- Sparky Mark http://www.moglesby.karoo.net ~~~~~~~~~~~~~~~~~~~~~~~~ |
#3
|
|||
|
|||
"Max" wrote in message ... Think its an array formula which needs to be array-entered, viz.: press CTRL+SHIFT+ENTER, instead of just pressing ENTER Excellent, nice one, cheers for that. -- Sparky Mark http://www.moglesby.karoo.net ~~~~~~~~~~~~~~~~~~~~~~~~ |
#4
|
|||
|
|||
"Sparky Mark" wrote
Excellent, nice one, cheers for that. You're welcome, Sparky ! Thanks for feedback -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Rainfall Data | Excel Discussion (Misc queries) | |||
Searching for Substrings Within Strings | Excel Discussion (Misc queries) | |||
How can I find strings of wildcards in Excel? | Excel Discussion (Misc queries) | |||
How do I get unique values from 2 columns? | Excel Discussion (Misc queries) | |||
compare unique identifiers in multiple ranges | Charts and Charting in Excel |