HOW TIMES A NAME IS USED IN DROP DOWN LIST

  • Thread starter Thread starter Ratboy
  • Start date Start date
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.
 
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)
 
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
 
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





.
Click to expand...
 
Back
Top