Indenifying Text in a formula.

[pjd]

Hi

This is a bit complicated (well, for me anyway) so here goes...

In columns C5:H5 I have Mon - Fri
In column B I have a list of names.
In the rows below C5:H5 i input the distance travelled eg 730, 789, 730 in the appropriate day.

However, sometimes there is a/l (annaul leave), s/l (sickleave) etc.

What I need to do is, in K5, I need a formula that says something like; look at C5:H5 and if in any cell there is s/l, then times(*) the number of S/l's found, by 8.
eg there is s/l in mon and wed, therefore that would equal 16 (2 instances X 8)

Is this possible??

 

[Biff]

Hi pjd!

Here's a couple of examples:

=SUMPRODUCT(--(C5:H5="S/L"))*8

=Countif(C5:H5,"S/L")*8

For more versatility, replace "S/L" with a cell reference:
A1

Biff

-----Original Message-----
Hi

This is a bit complicated (well, for me anyway) so here goes...

In columns C5:H5 I have Mon - Fri
In column B I have a list of names.
In the rows below C5:H5 i input the distance travelled eg

730, 789, 730 in the appropriate day.

However, sometimes there is a/l (annaul leave), s/l (sickleave) etc.

What I need to do is, in K5, I need a formula that says

something like; look at C5:H5 and if in any cell there is
s/l, then times(*) the number of S/l's found, by 8.