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

 

[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 da
e.g. in the example London = 2 (Monday) & 1 (Tuesday)

Does this make any sense?

Thanks for your help

Ton

 

[Ardus Petus]

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


HTH

 

[Guest]

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

 

[y_not]

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

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

IHope I can return the favour one day ..

 

[Guest]

You are welcome! Thanks for the feedback!
Stefi


„y_not†ezt írta: