some help with an IF formual or something like it
 

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

some help with an IF formual or something like it
 

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(F IND(",",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:A10 1<"",IF(ISNUMBER
(FIND(",",A2:A101)),8*(LEN(A2:A101)-LEN(SUBSTITUTE(A2:A101,",",""))+1),
8)))))

HTH
Kostis Vezerides

On Nov 18, 7:05*pm, Lost Will
wrote:
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???

some help with an IF formual or something like it
 

Adjust the ranges as necessary...

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

This formula can be copied down.

--
Rick (MVP - Excel)


"Lost Will" wrote in message
...
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???

some help with an IF formual or something like it
 

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)))))



"vezerid" wrote:

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(F IND(",",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:A10 1<"",IF(ISNUMBER
(FIND(",",A2:A101)),8*(LEN(A2:A101)-LEN(SUBSTITUTE(A2:A101,",",""))+1),
8)))))

HTH
Kostis Vezerides

On Nov 18, 7:05 pm, Lost Will
wrote:
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???

some help with an IF formual or something like it
 

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

Will//

"Rick Rothstein" wrote:

Adjust the ranges as necessary...

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

This formula can be copied down.

--
Rick (MVP - Excel)


"Lost Will" wrote in message
...
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???