HOW TIMES A NAME IS USED IN DROP DOWN LIST

[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.

 

[dlw]

=countif(j1.j9999,"name")
replace name with the name you want

 

[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)

 

[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

 

[Ratboy]

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

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





.

 

[T. Valko]

You're welcome. Thanks for the feedback!