Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique elements
rTrying to count unique data in a column that contains mixed numbers & text.
Have a Sum(If(Frequency(Match) formula but I must have copied it wrong. What is the corect formula for this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique elements
See the XL help file article "Count unique values among duplicates" for the
various functions. I'm guessing yours is similar to: =SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2: B10,B2:B10,0))0,1)) Note that all 3 of the formulas listed in the article are array formulas, and thus need to be confirmed using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "EPMMGR06" wrote: rTrying to count unique data in a column that contains mixed numbers & text. Have a Sum(If(Frequency(Match) formula but I must have copied it wrong. What is the corect formula for this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique elements
In article ,
EPMMGR06 wrote: rTrying to count unique data in a column that contains mixed numbers & text. Have a Sum(If(Frequency(Match) formula but I must have copied it wrong. What is the corect formula for this? Try... =SUM(IF(FREQUENCY(IF(A2:A10<"",MATCH("~"&A2:A10,A 2:A10&"",0)),ROW(A2:A10 )-ROW(A2)+1),1)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique elements
Worksheet functions?
Choose your own approach of "waiting for Godot": http://sulprobil.com/html/count_unique.html Regards, Bernd |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique elements
You could try this non-formula approach. Give a heading to the column and
then select the range including the headers to assign it a name, say Range1. Now convert it to a list (Ctrl+L). Now save the file Click on any blank cell and go to Data Import External data New Database query. Select Excel files and click on Next. On the last screen, select the radio button for Edit query in MS Query. Cluck on Finish. Now go to Edit Properties and check the box for unique records. Now go to file and select the last option - Return data to MS Excel. Now click on the properties button and select the box for refresh every 1 minutes. Click on Finish. You will now have all the unique entries. Now that you have the unique records -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "EPMMGR06" wrote in message ... rTrying to count unique data in a column that contains mixed numbers & text. Have a Sum(If(Frequency(Match) formula but I must have copied it wrong. What is the corect formula for this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare and count if data elements on two worksheets are similar | Excel Worksheet Functions | |||
list of unique elements | Excel Worksheet Functions | |||
Attempting to sort unique/only count first record in each unique g | Excel Discussion (Misc queries) | |||
Non-unique elements in an array | Excel Discussion (Misc queries) | |||
Excel: Can I use Fill_Color Attribs to count elements? | Excel Worksheet Functions |