HOW TIMES A NAME IS USED IN DROP DOWN LIST

R

Ratboy

i would like to know how excel can calculate the number of times a name is
used in the worksheet from a drop down list i have validated.

I have a drop down list of named workers, every few lines down in column D.
Everytime the same name is selcted i want it to give me the the total number
in a key i have created at the side of the main worksheet, this will be
column J.

please help me.
 
T

T. Valko

Tell us the *exact* locations of the drop down lists.

If there aren't too many you can do something like this...

Drop downs in D1, D5, D9

=(D1="Joe")+(D5="Joe")+(D9="Joe")

Or, use a cell to hold Joe :

F1 = Joe

=(D1=F1)+(D5=F1)+(D9=F1)
 
B

Billy Liddel

Say you have data validation in the range B2:B12, the range is named data, I
find it is more helpful to use names for ranges.

Enter this formula in C2 and copy down:

=IF(OR(B2="",COUNTIF($B$2:B2,B2)<COUNTIF(Data,B2)),"",COUNTIF(Data,B2))

This returns a blank cell if therre is now entry in column B or if the entry
is not the last in the list. The total for each item is returned in the last
entry of the item in the range.

Note the expanding range in the second OR criteria COUNTIF($B$2:B2,B2) this
is compared with the total for the whole list.

HTH
Peter Atherton
 
R

Ratboy

Thank you so much, i now done it! Whoop Whoop.

T. Valko said:
Tell us the *exact* locations of the drop down lists.

If there aren't too many you can do something like this...

Drop downs in D1, D5, D9

=(D1="Joe")+(D5="Joe")+(D9="Joe")

Or, use a cell to hold Joe :

F1 = Joe

=(D1=F1)+(D5=F1)+(D9=F1)

--
Biff
Microsoft Excel MVP





.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top