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:

Thermal 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).

enter image description here

Hopefully, that provides some help, Brad

| improve this answer | |
  • 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.

Here is an example:

| improve this answer | |

If you are using Excel 365, then in B1 enter:


and in C1 enter:


and copy downward.

enter image description here

| improve this answer | |

Use a Pivot Table,

Put the Item in both the Rows and the Values:

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.