Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question - please help
Hi All,
I need a formula that returns the number of unique times an item appears in a text list. Example List: Pen Pen Pencil Pencil Stamp I would a formula that returns 3 for the above (even though there are multiples I only need to know the number of times something occurs). I know I can do this with filters but this is not how I want to do it. I also tried the Sum IF Frequency formula but couldn't seem to get that to work. Thanks in advance for your help. Belle. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question - please help
On Jan 29, 4:33 pm, Belle wrote:
I need a formula that returns the number of unique times an item appears in a text list. Example List: Pen Pen Pencil Pencil Stamp I would a formula that returns 3 for the above If the data are sorted, as in your example, and if you can ensure that the last row ("Stamp" above) is followed by something else (like an empty cell), the following might work for you: =SUMPRODUCT(--(A1:A5<A2:A6)) ----- original posting ----- On Jan 29, 4:33*pm, Belle wrote: Hi All, I need a formula that returns the number of unique times an item appears in a text list. Example List: Pen Pen Pencil Pencil Stamp I would a formula that returns 3 for the above (even though there are multiples I only need to know the number of times something occurs). I know I can do this with filters but this is not how I want to do it. I also tried the Sum IF Frequency *formula but couldn't seem to get that to work. Thanks in advance for your help. Belle. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question - please help
You could try:
=SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5)) HTH Elkar "Belle" wrote: Hi All, I need a formula that returns the number of unique times an item appears in a text list. Example List: Pen Pen Pencil Pencil Stamp I would a formula that returns 3 for the above (even though there are multiples I only need to know the number of times something occurs). I know I can do this with filters but this is not how I want to do it. I also tried the Sum IF Frequency formula but couldn't seem to get that to work. Thanks in advance for your help. Belle. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question - please help
Try this array* formula on your example:
=SUM(IF(LEN(A1:A10),1/COUNTIF(A1:A10,A1:A10))) Assumes a data range of A1 to A10 - adjust as necessary. *An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual Enter. If you do this correctly then Excel will wrap curly braces around that formula when viewed in the formula bar - do not type these yourself. If you edit/ amend the formula you need to use CSE again. Hope this helps. Pete On Jan 30, 12:33*am, Belle wrote: Hi All, I need a formula that returns the number of unique times an item appears in a text list. Example List: Pen Pen Pencil Pencil Stamp I would a formula that returns 3 for the above (even though there are multiples I only need to know the number of times something occurs). I know I can do this with filters but this is not how I want to do it. I also tried the Sum IF Frequency *formula but couldn't seem to get that to work. Thanks in advance for your help. Belle. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question - please help
Thanks Pete - this seems to have worked! Thanks a lot!
"Pete_UK" wrote: Try this array* formula on your example: =SUM(IF(LEN(A1:A10),1/COUNTIF(A1:A10,A1:A10))) Assumes a data range of A1 to A10 - adjust as necessary. *An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual Enter. If you do this correctly then Excel will wrap curly braces around that formula when viewed in the formula bar - do not type these yourself. If you edit/ amend the formula you need to use CSE again. Hope this helps. Pete On Jan 30, 12:33 am, Belle wrote: Hi All, I need a formula that returns the number of unique times an item appears in a text list. Example List: Pen Pen Pencil Pencil Stamp I would a formula that returns 3 for the above (even though there are multiples I only need to know the number of times something occurs). I know I can do this with filters but this is not how I want to do it. I also tried the Sum IF Frequency formula but couldn't seem to get that to work. Thanks in advance for your help. Belle. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question - please help
On Thu, 29 Jan 2009 16:33:02 -0800, Belle
wrote: Hi All, I need a formula that returns the number of unique times an item appears in a text list. Example List: Pen Pen Pencil Pencil Stamp I would a formula that returns 3 for the above (even though there are multiples I only need to know the number of times something occurs). I know I can do this with filters but this is not how I want to do it. I also tried the Sum IF Frequency formula but couldn't seem to get that to work. Thanks in advance for your help. Belle. If you are counting blanks, try: =SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5&"")) If you want to ignore blanks, then try: =SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question - please help
Hi,
You can try using this array formula (Ctrl+Shift+Enter) =SUM(1/COUNTIF(range,range)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Belle" wrote in message ... Hi All, I need a formula that returns the number of unique times an item appears in a text list. Example List: Pen Pen Pencil Pencil Stamp I would a formula that returns 3 for the above (even though there are multiples I only need to know the number of times something occurs). I know I can do this with filters but this is not how I want to do it. I also tried the Sum IF Frequency formula but couldn't seem to get that to work. Thanks in advance for your help. Belle. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question - please help
You're welcome, but many of the other suggestions are very similar.
Pete On Jan 30, 1:12*am, Belle wrote: Thanks Pete - this seems to have worked! Thanks a lot! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Question - please help
Hi
Try this non array formula =SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7)) This assume that there're no blank Cells in the data table However, if you have a large dataset, Array formula and this Sumproduct formula will slow down Excel Pivot table will be your best solution HTH -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "Belle" wrote: Hi All, I need a formula that returns the number of unique times an item appears in a text list. Example List: Pen Pen Pencil Pencil Stamp I would a formula that returns 3 for the above (even though there are multiples I only need to know the number of times something occurs). I know I can do this with filters but this is not how I want to do it. I also tried the Sum IF Frequency formula but couldn't seem to get that to work. Thanks in advance for your help. Belle. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula question | Excel Worksheet Functions | |||
If Formula Question | Excel Discussion (Misc queries) | |||
what IF - formula question | Excel Worksheet Functions | |||
Formula Question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) |