Jacek has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel Advanced course
Function in excel
Is it funcktion which Can count the number of objects (arguments) are in the range?
e.g.
x
x
x
y
y
y
z
z
=3 (x,y,x)
RE: function in excel
Hi Jacek
Thank you for your question regarding the counting of unique objects in a list.
The best way to achieve this is to:
- Firstly make sure the range has a column header
- On the Data menu, point to Filter & then select Advanced filter
- In the Advanced filter dialogue box click 'Copy to another location'
- In the List Range box, delete anything that may already be in there & then select the your data range (including the column header) so your data range cell ref is now in the List Range box.
- In the Copy To box, delete anything that may be in there and then select a cell from where you would like the unique values to appear
- Tick the Unique Records Only checkbox - OK
- Now just count the rows that have appeared using the function ROWS
e.g if the unique objects appear in cell range B2:B10, the formula would look like =ROWS (B2:B10)
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post.
Regards,
Nafeesa
Microsoft Office Specialist Trainer
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
RE: function in excel
thanks a lot. So There isn't any simple funcjtion.
RE: function in excel
Hi Jacek
The easiest way to identify unique values in a range of data is to use the Advanced Filter tool, there are functions available but far more complex to use than the filtering method.
If you prefer rather than using the ROWS function to count the number of rows of unique values, you could use the COUNT (specifically used for numeric data) or the COUNTA (specifically used for non-numeric data i.e x, y, z) function which would also count the number of unique values.
e.g. =COUNTA(B2:B10)
Kind Regards
Nafeesa
Microsoft Office Specialist Trainer
