multiple function is it even possible

[Kris M]

I'm working on a schedule that needs to count N or D and assign it a value of
12

the chart i'm working with looks something like this

sun mon tues wed thurs fri sat
mary vac vac vac vac vac va d va-n
tyler off off n n d d off
john d d d d off off d
joe n n off off n n n


what function can i use to count how many times a person works "n" or "va d"
or "va-n" or "d" and assign a value of 12 for each of those characters but a
value of ZERO for off or vac?

 

[Mike H]

How about this in I2 and drag down

=(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N"))*12

You didn't say what was to happen for - va d or va-n

Mike

 

[Kris M]

actually i need "va-d" and "va-n" to also have a value of 12 -- "vac" and
"off" obviously have a zero value.

 

[Kris M]

I GOT IT TO WORK THANKS SO MUCH -- I JUST EXPANDED ON WHAT YOU SHOWED ME BUT
NOW ANOTHER FUNCTION QUESTION PLEASE?

if we work a standard 48 hour work week and work overtime by 24 hours, what
kind of function can you toss my way to show that?

what I mean is in a table i need the first column to read the total hours
worked (example is 12, 72,60,72) in the second column i need to show the
hours that went OVER the standard hours of 48 (doing the math the hard way --
0,24,12,24)

i just confused myself so no biggie if i lost you somewhere.

 

[Mike H]

Hi,

It's only 'obvious' to you what you want not us. Try this

=(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N")+COUNTIF(B2:H2,"VA
D")+COUNTIF(B2:H2,"VA-N"))*12

Mind the line wrap it's all on one line. Note that in your codes va d &
va-n the latter has a hyphen and this is replicated in the formula.

Mike

 

[Mike H]

Hi,

If I understand correctly you want to calculate basic hours up to 48 and
overtime hours for each person.

Basic hours in I2 and drag down

=MIN(48,(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N")+COUNTIF(B2:H2,"VA
D")+COUNTIF(B2:H2,"VA-N"))*12)

Overtime hours in J2 and drag down

=MAX(0,((COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N")+COUNTIF(B2:H2,"VA
D")+COUNTIF(B2:H2,"VA-N"))*12)-48)

Mike

 

[Teethless mama]

Simplify version:

=SUM(COUNTIF(B2:H2,{"va d","va-n","d","n"}))*12

 

[Shane Devenshire]

Hi,

Here is a shorter version:

=SUM(COUNTIF(B2:H2,{"Va??";"d";"n"})*12)

If you already have the above calculation in cell I2, then Regular hours in
J2 would be
=MIN(I2,48)
and Overtime hours in cell K2 would be
=MAX(I2-48,0)

If this helps please click the Yes button

Cheers,
Shane Devenshire