which count function?
 

Hi,

Cell A2:A100 contains a dropdown list (choice of six Sites)
C2:C100 to I2:I100 contains hours worked at the relevant site (by day)
Each of the rows relates to a different person.
I am trying to count the number of people working at each site each
day

I have tried IF, DCOUNT, COUNT, FREQUENCY etc. with no success.

Has anyone any suggestions, please?


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

which count function?
 

Could you please post some sample data?

--
AP

"y_not" a écrit dans le
message de ...

Hi,

Cell A2:A100 contains a dropdown list (choice of six Sites)
C2:C100 to I2:I100 contains hours worked at the relevant site (by day)
Each of the rows relates to a different person.
I am trying to count the number of people working at each site each
day

I have tried IF, DCOUNT, COUNT, FREQUENCY etc. with no success.

Has anyone any suggestions, please?


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

which count function?
 

A1 B1 C1 D1
London Bill 8
Exeter Fred 8 8
London John 8 6
Norfolk Joe 6 6

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

which count function?
 

=SUMPRODUCT(--($A1:$A4="London"),--(C1:C40)) for Monday.
Copy formula to the right for Tuesday, Wed, etc..


HTH
--
AP

"y_not" a écrit dans le
message de ...

A1 B1 C1 D1
London Bill 8
Exeter Fred 8 8
London John 8 6
Norfolk Joe 6 6

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

which count function?
 

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

which count function?
 

In spite of my "less than perfect" description of what I was trying to
do you have succeeded in making an old man very happy :)

I used the suggestion from Ardus Petus (simply because that was the
first one I came to) but my thanks also to Stefi for your
contribution.

IHope I can return the favour one day ...


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

which count function?
 

You are welcome! Thanks for the feedback!
Stefi


€žy_not€� ezt Ã*rta:


In spite of my "less than perfect" description of what I was trying to
do you have succeeded in making an old man very happy :)

I used the suggestion from Ardus Petus (simply because that was the
first one I came to) but my thanks also to Stefi for your
contribution.

IHope I can return the favour one day ...


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