# Need To Count Total Number Of Instances Of Values In A Column And Return The Results In Other Cells

I have a situation where I need to count the total number of instances of values in a column and return the results in other cells.

Users are going to be provided this Excel sheet as a template and will choose from a dropdown list in a specific column.

For example in Column A, the 'Clothing' column, the user would choose any one of these values:

``````Bra
Dress
Pants
Shirt
Shoes
Shorts
Socks
T-Shirt
Thermal Underwear
Underwear
``````

And they will have a different number of each item each time.

So ideally I could total up every instance of each term. Then in another column I could return any number above 0, along with the term, like so: 'Bra: 4'. This value would be received later when the file is submitted.

I searched around and tried using the COUNTIF function. I can get this to work generically if I drag it down along side the Clothing column, but the users will submit this themselves, so I need it to do this as automatically as possible.

I made a named range of the terms way out in cell AA in order to make the dropdown, but I couldn't get the function to check the named range, and I'm not sure if I can do that at all.

Can anyone shed some light here? Thanks much and please let me know if more info is needed.

I would use COUNTIF in a case like this. In the example below I completed a COUNTIF using a named range (Test). Hopefully, that provides some help, Brad

• This worked exactly for what I needed. I was trying to put the named range in the second argument of the COUNTIF and it wasn't working, but now I see why it didn't work. Thanks much. – John G Apr 17 at 17:08

You can use Subtotal Function 3 for this. Create another A2 row with filter enabled and put a formula similar to =SUBTOTAL(3,A3:A23) . It will count the rows matching your filter. If you are using Excel 365, then in B1 enter:

``````=FILTER(UNIQUE(A:A),UNIQUE(A:A)<>"")
``````

and in C1 enter:

``````=COUNTIF(A:A,B1)
``````

and copy downward. Use a Pivot Table,

Put the Item in both the Rows and the Values: 