help with IF, if it is the answer..

[Lost Will]

Hi all,

I am a bit of a newb so be gentle here...

I have a sheet that has a lit of names vertically and weeks of the month
horizontally.
In my "total" cell I want to displayt he result fo the following:

If in the range of cells there is an X - then add 40, add 40 for each x in
the range
If in the range of cells there is an H - then add 32, add 32 for each H in
the range
If in the range of cells there is a 1-31 then add 8, if shown as 1,2 (or any
combination of 1-31 like 1,5,8,31 then add 32)

So, if Max takes the week of May 30th off, I input X into that weeks cell
and formula adds 40, if Max takes off the 6th, 7th and 9th of May off, I
input 6,7,9 in that weeks cell and the formula adds 24, if Max takes off the
1st and 2nd, I input 1,2 in the cell and the formula adds 16.

Is this even possible???

 

[T. Valko]

Try these:

For "X" :

=COUNTIF(B2:J2,"X")*40

For "H" :

=COUNTIF(B2:J2,"H")*32

For 1-31 :

Assuming that only cells with the "1-31 format" will contain commas.

=(COUNT(B2:J2)+SUMPRODUCT(LEN(B2:J2)-LEN(SUBSTITUTE(B2:J2,",","")))+COUNTIF(B2:I2,"*,*"))*8