which count function?

[y_not]

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?

[Ardus Petus]

Could you please post some sample data?

--
AP

"y_not" <[email protected]> a écrit dans le
message de news:[email protected]...
>
> 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
>

[y_not]

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

[Ardus Petus]

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


HTH
--
AP

"y_not" <[email protected]> a écrit dans le
message de news:[email protected]...
>
> 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
>

[Stefi]

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

[y_not]

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

[Stefi]

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