Formula Help
 

I am creating a spreadsheet for scheduling I have most of the formulas using
=countif what I am trying to do is calculate a cell that lists 2 names for a
split shift i.e.

1st shift: Fred
2nd Shift: Fred/Roman or Fred Roman
3rd shift: Roman

At the end of the week the countif is counting the number of times each name
was found. The problem is having a shift like listed above is not being
calculated in any way for the 2nd shift. What I would like to do is have a
formula that woul see 2nd shift and add .5 to each end total instead of the
traditional 1 per occurance. I dont know if this is possible.

Any help would be appreciated

Kelly

Formula Help
 

Try to give the 0.5 counts and just add to the single counts ...

=COUNTIF($A$1:$A$6,D1) +

(COUNTIF($A$1:$A$6,D1&"*/*")+COUNTIF($A$1:$A$6,"*/*" &D1))*0.5

A1:A6 is list of names on Shifts

Fred
Fred/Roman
Roman
Fred
Roman / Fred
Roman

Names are separated by "/" with
D1 is name of person being counted

Data above gave:

Fred 3
Roman 3

HTH

"madduck" wrote:

I am creating a spreadsheet for scheduling I have most of the formulas using
=countif what I am trying to do is calculate a cell that lists 2 names for a
split shift i.e.

1st shift: Fred
2nd Shift: Fred/Roman or Fred Roman
3rd shift: Roman

At the end of the week the countif is counting the number of times each name
was found. The problem is having a shift like listed above is not being
calculated in any way for the 2nd shift. What I would like to do is have a
formula that woul see 2nd shift and add .5 to each end total instead of the
traditional 1 per occurance. I dont know if this is possible.

Any help would be appreciated

Kelly

Formula Help
 

I tried this formula and it was not giving the correct totals and this spread
sheet has up to 12 different names used in it

thanks anyway

"Toppers" wrote:

Try to give the 0.5 counts and just add to the single counts ...

=COUNTIF($A$1:$A$6,D1) +

(COUNTIF($A$1:$A$6,D1&"*/*")+COUNTIF($A$1:$A$6,"*/*" &D1))*0.5

A1:A6 is list of names on Shifts

Fred
Fred/Roman
Roman
Fred
Roman / Fred
Roman

Names are separated by "/" with
D1 is name of person being counted

Data above gave:

Fred 3
Roman 3

HTH

"madduck" wrote:

I am creating a spreadsheet for scheduling I have most of the formulas using
=countif what I am trying to do is calculate a cell that lists 2 names for a
split shift i.e.

1st shift: Fred
2nd Shift: Fred/Roman or Fred Roman
3rd shift: Roman

At the end of the week the countif is counting the number of times each name
was found. The problem is having a shift like listed above is not being
calculated in any way for the 2nd shift. What I would like to do is have a
formula that woul see 2nd shift and add .5 to each end total instead of the
traditional 1 per occurance. I dont know if this is possible.

Any help would be appreciated

Kelly

Formula Help
 

I re-tested with a series of 10 names and it worked OK (unless I have
misunderstood the problem).

Post sample to toppers at REMOVETHISjohntopley.fsnet.co.uk

"madduck" wrote:

I tried this formula and it was not giving the correct totals and this spread
sheet has up to 12 different names used in it

thanks anyway

"Toppers" wrote:

Try to give the 0.5 counts and just add to the single counts ...

=COUNTIF($A$1:$A$6,D1) +

(COUNTIF($A$1:$A$6,D1&"*/*")+COUNTIF($A$1:$A$6,"*/*" &D1))*0.5

A1:A6 is list of names on Shifts

Fred
Fred/Roman
Roman
Fred
Roman / Fred
Roman

Names are separated by "/" with
D1 is name of person being counted

Data above gave:

Fred 3
Roman 3

HTH

"madduck" wrote:

I am creating a spreadsheet for scheduling I have most of the formulas using
=countif what I am trying to do is calculate a cell that lists 2 names for a
split shift i.e.

1st shift: Fred
2nd Shift: Fred/Roman or Fred Roman
3rd shift: Roman

At the end of the week the countif is counting the number of times each name
was found. The problem is having a shift like listed above is not being
calculated in any way for the 2nd shift. What I would like to do is have a
formula that woul see 2nd shift and add .5 to each end total instead of the
traditional 1 per occurance. I dont know if this is possible.

Any help would be appreciated

Kelly