Hi Tony,

The formula is
=SUMPRODUCT(--(Sheet1 !$A2:$A100=$A1),--NOT(ISBLANK(Sheet1 !C2:C100)))

where

Sheet1 is your data sheet
A1 B1 C1 D1
London Bill 8
Exeter Fred 8 8
London John 8 6
Norfolk Joe 6 6

Sheet2 is a summary sheet:

A B C D
City Monday Tuesday Wednesday ...
London formula-
Exeter |
Norfolk V

Fill the formula to right and down!

Regards,
Stefi

Where A1 = list (using validation)
B1 = Name of employee
C1 = Hours worked on Monday
D1 = Hoours worked on Tuesday etc.

I am needing to count how many people worked in each location each day
e.g. in the example London = 2 (Monday) & 1 (Tuesday)

Does this make any sense?

Thanks for your help

Tony


--
y_not
------------------------------------------------------------------------
y_not's Profile: http://www.excelforum.com/member.php...o&userid=19947
View this thread: http://www.excelforum.com/showthread...hreadid=525138