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.

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

"Ratboy" wrote:

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.

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


"Ratboy" wrote in message
...
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.

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" wrote:

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.

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

"T. Valko" wrote:

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


"Ratboy" wrote in message
...
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.


.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ratboy" wrote in message
...
Thank you so much, i now done it! Whoop Whoop.

"T. Valko" wrote:

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


"Ratboy" wrote in message
...
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.


.