Count items from a dropdown list

[ricardobrat]

Hi there.

Im having a bit struggle with Excel at the moment as Im trying to count how many items there are in a dropdown menu. Creating the list in a second sheet and using the list to make a dropdown list in the first sheet does not work as I need a lot of dropdown menus and I would need to create countless lists in the second sheet.

I want to count all the things in the list without the first one as I will be using it as a Title. Picture attached.

If we count the list in the example picture, it should say that I have 4 items.

I would appreciate any help!

 

[mackc557]

Is this a Data Validation drop-down? Are you planning on using the count with a Formula or with VBA? Are you expecting the number of items to be variable?

 

[ricardobrat]

Hi. It is a Data Validation drop-down, yes. Im planning to use it as a list of items which will eventually be removed 1 by 1 manually by me, so yes, it will be variable. Not sure what the second question is but basically, I need the drop down menu to see what current available items I got from this specific list and I want to know exactly how many items are left on the list, that's it.

 

[mackc557]

Book1
	B	C	D	E
2				Title
3	6			One
4				Two
5				Three
6				Four
7				Five
8				Six
Sheet1

Cell Formulas
Range	Formula
B3	B3	=COUNTA(ListRange)-1

Named Ranges
Name	Refers To	Cells
ListRange	=INDIRECT("Sheet1!" & ADDRESS(2,5) & ":" & ADDRESS(COUNTA(Sheet1!$E:$E)+1,5))	B3

Cells with Data Validation
Cell	Allow	Criteria
C3	List	=ListRange

 

[ricardobrat]

Thank you for this. Is it possible to not use a different sheet to store the data? I have a lot of dropdown menus and I will have to create countless sheets or lists in a different sheet that way. Is it possible to count the list directly from the dropdown list?

 

[mackc557]

If you are creating a dropdown list for data validation, you will need a source for the data. It will need to be SOMEWHERE on a sheet; you could have the data in the same sheet as everything else in a far distant column, or you could have all the data in a dedicated sheet for data validation only.

The formula in B3 was available if you wanted the count of how many there are in the list.

The named range is an example of how to create a dynamic range to create a dropdown list with only as many items as there are in the given column.

You said you wanted a count of how many items are in the dropdown; the best way to do that is a COUNTA of the reference list contained in the sheet.