Excel help2

[kir]

The formula I need is regarding a staff rota of 33 people for 24 hour
in Excel and goes as:
b2b2
A Column B C D E
F G
Mon Tues Wed Thu
Fri Sat
6 Joe Bloggs E L E E
O O
7 Madonna EL N N O O
L
8 Charlie E L L N
N E
9 Martin L N L E
O O

The formula given from this board to count total E in B50, total L i
B51 , etc of

=sumproduct(--(b6:b8)="E")) works only if there is one lette
E in the column,

but will not include the E if there is EL. Needs something that wil
count the E in EL as well as single E.

E= Early shift
L= Late shift
N= night shift
EL = Early to Late
O= Off

Thanks

 

[Norman Harker]

Hi Kir!

Use:
=COUNTIF(B6:B8,"*E*")

Trouble is that SUMPRODUCT doesn't support wildcards. In this case
I've given the option of L being before or after E (or not there at
all).

 

[Bob Phillips]

Maybe not, but it can be simulated

=SUMPRODUCT(--(NOT(ISERROR(FIND("E",B6:B8)))))

<vbg>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi Bob
or even shorter:
=SUMPRODUCT(--(ISNUMBER(FIND("E",B6:B8))))

 

[Norman Harker]

Hi Frank and Bob!

In some cases OK! But my solution was 1 function call for the price of
3 or 4 function calls. Not significant in terms of speed unless used
many times but it is easier to understand.

However, we don't always have the luxury of being able to use COUNTIF
especially if there are other criteria.

And you seem to have had a couple of days off Frank. Did you get an
excuse note?

 

[Bob Phillips]

Hi Norm,

However, we don't always have the luxury of being able to use COUNTIF
especially if there are other criteria.
Exactly!

And you seem to have had a couple of days off Frank. Did you get an
excuse note?

Don't knock it, it gives us a chance to get a look in!

 

[Frank Kabel]

Hi Bob, Norman

Don't knock it, it gives us a chance to get a look in!

lol
too much work to do during the week :-(
Frank

 

[Bob Phillips]

about time too!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Norman Harker]

Hi!

That's not as good as the, "The dog ate my modem." that you gave last
time.