some help with an IF formual or something like it

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

 

[vezerid]

The formula to find the numeric value for a single cell is the
following:

=IF(A2="X",40,IF(A2="h",32,IF(A2<>"",IF(ISNUMBER(FIND(",",A2)),8*(LEN
(A2)-LEN(SUBSTITUTE(A2,",",""))+1),8))))

Now, if you want to find a row total you use this in array form inside
SUM and commit with Shift+Ctrl+Enter:

=SUM(IF(A2:BA2="X",40,IF(A2:BA2="h",32,IF(A2:BA2<>"",IF(ISNUMBER(FIND
(",",A2:BA2)),8*(LEN(A2:BA2)-LEN(SUBSTITUTE(A2:BA2,",",""))+1),8)))))

If you want a column total, the same for columns

=SUM(IF(A2:A101="X",40,IF(A2:A101="h",32,IF(A2:A101<>"",IF(ISNUMBER
(FIND(",",A2:A101)),8*(LEN(A2:A101)-LEN(SUBSTITUTE(A2:A101,",",""))+1),
8)))))

HTH
Kostis Vezerides

 

[Rick Rothstein]

Adjust the ranges as necessary...

=40*COUNTIF(B2:AJ2,"X")+32*COUNTIF(B2:AJ2,"H")+8*SUMPRODUCT(ISNUMBER(FIND(",",B2:AJ2))+LEN(B2:AJ2)-LEN(SUBSTITUTE(B2:AJ2,",","")))

This formula can be copied down.

 

[Lost Will]

I can visualize it now inside the SUM however I am still getting a message
that the formula contains an error.
I have made the cell references match...still a little confused but thanks
fro the help..

what is wrong with this now?

=SUM(IF(D4:BC4="X",40,IF(D4:BC4="h",32,IF(D4:BC4<>"",IF(ISNUMBER(FIND
(",",D4:BC4)),8*(LEN(D4:BC4)-LEN(SUBSTITUTE(D4:BC4,",",""))+1),8)))))

 

[Lost Will]

Thanks so much that is perfect.
I am learning and this has expanded my knowledge a little.
The help is appreciated.

Will//