Counting ultiple constants in cell

I have an attendance tracking spreadsheet in which I need to track the number
of hours as well as the type of absence, i.e. due to illness, military leave,
FMLA, etc., rather than just the number of days the employee is gone.

I have the spreadsheet set up with by the employees name in the rows and
then have set up columns for each day of the year and columns for the reason
of the absence. Each time there is a military absence, the manager will put
an "M" under the date and excel will then automatically count the number of
times "M" is under that persons name throughout the year. This formula is
working great! However, as mentioned above, I need it to count hours, not
days. How can I set up the formula to allow me to enter in a number of hours
as well as "M" in a single cell and have the total automatically calculated
for me?

Currently looks like the following:

Days of Absence
Employee name: Military "M" FMLA "F" 010106 010206
John Doe 1 1 M F


I want it to look like:

Hours of Absence
Employee Name: Military "M" FMLA "F" 010106 010206
John Doe 8 4 8M 4F

Is this possible without adding additional columns for the hours?

I got it to work using the following array formula:

=SUM(IF(ISNUMBER(A13:F13),A13:F13,IF(LEN(A13:F13)>0,VALUE(LEFT(A13:F13,LEN(A13:F13)-1)),0)))

My test data was in cells A13:F13, and I entered the "mixed" cells like your
example: 8M 4F

With an array formula, instead of {Enter} you have to press
{Ctrl}{Shift}{Enter} together. If you do it correctly, the formula will
appear in the Formula Bar with {curly brackets} around it (Excel adds the
curly brackets, not you). Also, you can't use a whole column or row for an
input range.

Hope this helps,

Hutch

"Westy" wrote:

> I have an attendance tracking spreadsheet in which I need to track the number
> of hours as well as the type of absence, i.e. due to illness, military leave,
> FMLA, etc., rather than just the number of days the employee is gone.
>
> I have the spreadsheet set up with by the employees name in the rows and
> then have set up columns for each day of the year and columns for the reason
> of the absence. Each time there is a military absence, the manager will put
> an "M" under the date and excel will then automatically count the number of
> times "M" is under that persons name throughout the year. This formula is
> working great! However, as mentioned above, I need it to count hours, not
> days. How can I set up the formula to allow me to enter in a number of hours
> as well as "M" in a single cell and have the total automatically calculated
> for me?
>
> Currently looks like the following:
>
> Days of Absence
> Employee name: Military "M" FMLA "F" 010106 010206
> John Doe 1 1 M F
>
>
> I want it to look like:
>
> Hours of Absence
> Employee Name: Military "M" FMLA "F" 010106 010206
> John Doe 8 4 8M 4F
>
> Is this possible without adding additional columns for the hours?
>
>
>
>

It partially worked. I was able to get it to the calculation, however I still
need the formula to determine which column it should go based on whether I
used "M" or "F".

So, if an employee has 4 dates of 8M (4 days with 8 hours of Military leave)
and then later in the quarter, has 2 dates with 4F (2 days with 4 hours of
leave for FMLA), I need the 32 hours of Military leave to appear in the "M"
column and the 8 hours of FMLA leave to appear in the "F" column. The formula
below, will add all of the hours together and give me 40 in whichever cell I
enter the formula.

Is there a way to have it do what I need it to do???

"Tom Hutchins" wrote:

> I got it to work using the following array formula:
>
> =SUM(IF(ISNUMBER(A13:F13),A13:F13,IF(LEN(A13:F13)>0,VALUE(LEFT(A13:F13,LEN(A13:F13)-1)),0)))
>
> My test data was in cells A13:F13, and I entered the "mixed" cells like your
> example: 8M 4F
>
> With an array formula, instead of {Enter} you have to press
> {Ctrl}{Shift}{Enter} together. If you do it correctly, the formula will
> appear in the Formula Bar with {curly brackets} around it (Excel adds the
> curly brackets, not you). Also, you can't use a whole column or row for an
> input range.
>
> Hope this helps,
>
> Hutch
>
> "Westy" wrote:
>
> > I have an attendance tracking spreadsheet in which I need to track the number
> > of hours as well as the type of absence, i.e. due to illness, military leave,
> > FMLA, etc., rather than just the number of days the employee is gone.
> >
> > I have the spreadsheet set up with by the employees name in the rows and
> > then have set up columns for each day of the year and columns for the reason
> > of the absence. Each time there is a military absence, the manager will put
> > an "M" under the date and excel will then automatically count the number of
> > times "M" is under that persons name throughout the year. This formula is
> > working great! However, as mentioned above, I need it to count hours, not
> > days. How can I set up the formula to allow me to enter in a number of hours
> > as well as "M" in a single cell and have the total automatically calculated
> > for me?
> >
> > Currently looks like the following:
> >
> > Days of Absence
> > Employee name: Military "M" FMLA "F" 010106 010206
> > John Doe 1 1 M F
> >
> >
> > I want it to look like:
> >
> > Hours of Absence
> > Employee Name: Military "M" FMLA "F" 010106 010206
> > John Doe 8 4 8M 4F
> >
> > Is this possible without adding additional columns for the hours?
> >
> >
> >
> >

I guess I misunderstood your request. You already have a formula to sum all
hours, whether purely numeric or including M or F. If you want to sum only
the hours with the M suffix, enter an array formula like the following in the
cell where you want the total:

=SUM(IF(RIGHT(A13:F13,1)="M",VALUE(LEFT(A13:F13,LEN(A13:F13)-1)),0))

In the cell where you want the sum of only the F-suffix hours, enter an
array formula like this:

=SUM(IF(RIGHT(A13:F13,1)="F",VALUE(LEFT(A13:F13,LEN(A13:F13)-1)),0))

Regards,

Hutch

"Westy" wrote:

> It partially worked. I was able to get it to the calculation, however I still
> need the formula to determine which column it should go based on whether I
> used "M" or "F".
>
> So, if an employee has 4 dates of 8M (4 days with 8 hours of Military leave)
> and then later in the quarter, has 2 dates with 4F (2 days with 4 hours of
> leave for FMLA), I need the 32 hours of Military leave to appear in the "M"
> column and the 8 hours of FMLA leave to appear in the "F" column. The formula
> below, will add all of the hours together and give me 40 in whichever cell I
> enter the formula.
>
> Is there a way to have it do what I need it to do???
>
> "Tom Hutchins" wrote:
>
> > I got it to work using the following array formula:
> >
> > =SUM(IF(ISNUMBER(A13:F13),A13:F13,IF(LEN(A13:F13)>0,VALUE(LEFT(A13:F13,LEN(A13:F13)-1)),0)))
> >
> > My test data was in cells A13:F13, and I entered the "mixed" cells like your
> > example: 8M 4F
> >
> > With an array formula, instead of {Enter} you have to press
> > {Ctrl}{Shift}{Enter} together. If you do it correctly, the formula will
> > appear in the Formula Bar with {curly brackets} around it (Excel adds the
> > curly brackets, not you). Also, you can't use a whole column or row for an
> > input range.
> >
> > Hope this helps,
> >
> > Hutch
> >
> > "Westy" wrote:
> >
> > > I have an attendance tracking spreadsheet in which I need to track the number
> > > of hours as well as the type of absence, i.e. due to illness, military leave,
> > > FMLA, etc., rather than just the number of days the employee is gone.
> > >
> > > I have the spreadsheet set up with by the employees name in the rows and
> > > then have set up columns for each day of the year and columns for the reason
> > > of the absence. Each time there is a military absence, the manager will put
> > > an "M" under the date and excel will then automatically count the number of
> > > times "M" is under that persons name throughout the year. This formula is
> > > working great! However, as mentioned above, I need it to count hours, not
> > > days. How can I set up the formula to allow me to enter in a number of hours
> > > as well as "M" in a single cell and have the total automatically calculated
> > > for me?
> > >
> > > Currently looks like the following:
> > >
> > > Days of Absence
> > > Employee name: Military "M" FMLA "F" 010106 010206
> > > John Doe 1 1 M F
> > >
> > >
> > > I want it to look like:
> > >
> > > Hours of Absence
> > > Employee Name: Military "M" FMLA "F" 010106 010206
> > > John Doe 8 4 8M 4F
> > >
> > > Is this possible without adding additional columns for the hours?
> > >
> > >
> > >
> > >

You are a genius!!!

It works great!...once I used the ctrl, shift, enter trick after entering
the formula.

Thank you so very much!!!

"Tom Hutchins" wrote:

> I guess I misunderstood your request. You already have a formula to sum all
> hours, whether purely numeric or including M or F. If you want to sum only
> the hours with the M suffix, enter an array formula like the following in the
> cell where you want the total:
>
> =SUM(IF(RIGHT(A13:F13,1)="M",VALUE(LEFT(A13:F13,LEN(A13:F13)-1)),0))
>
> In the cell where you want the sum of only the F-suffix hours, enter an
> array formula like this:
>
> =SUM(IF(RIGHT(A13:F13,1)="F",VALUE(LEFT(A13:F13,LEN(A13:F13)-1)),0))
>
> Regards,
>
> Hutch
>
> "Westy" wrote:
>
> > It partially worked. I was able to get it to the calculation, however I still
> > need the formula to determine which column it should go based on whether I
> > used "M" or "F".
> >
> > So, if an employee has 4 dates of 8M (4 days with 8 hours of Military leave)
> > and then later in the quarter, has 2 dates with 4F (2 days with 4 hours of
> > leave for FMLA), I need the 32 hours of Military leave to appear in the "M"
> > column and the 8 hours of FMLA leave to appear in the "F" column. The formula
> > below, will add all of the hours together and give me 40 in whichever cell I
> > enter the formula.
> >
> > Is there a way to have it do what I need it to do???
> >
> > "Tom Hutchins" wrote:
> >
> > > I got it to work using the following array formula:
> > >
> > > =SUM(IF(ISNUMBER(A13:F13),A13:F13,IF(LEN(A13:F13)>0,VALUE(LEFT(A13:F13,LEN(A13:F13)-1)),0)))
> > >
> > > My test data was in cells A13:F13, and I entered the "mixed" cells like your
> > > example: 8M 4F
> > >
> > > With an array formula, instead of {Enter} you have to press
> > > {Ctrl}{Shift}{Enter} together. If you do it correctly, the formula will
> > > appear in the Formula Bar with {curly brackets} around it (Excel adds the
> > > curly brackets, not you). Also, you can't use a whole column or row for an
> > > input range.
> > >
> > > Hope this helps,
> > >
> > > Hutch
> > >
> > > "Westy" wrote:
> > >
> > > > I have an attendance tracking spreadsheet in which I need to track the number
> > > > of hours as well as the type of absence, i.e. due to illness, military leave,
> > > > FMLA, etc., rather than just the number of days the employee is gone.
> > > >
> > > > I have the spreadsheet set up with by the employees name in the rows and
> > > > then have set up columns for each day of the year and columns for the reason
> > > > of the absence. Each time there is a military absence, the manager will put
> > > > an "M" under the date and excel will then automatically count the number of
> > > > times "M" is under that persons name throughout the year. This formula is
> > > > working great! However, as mentioned above, I need it to count hours, not
> > > > days. How can I set up the formula to allow me to enter in a number of hours
> > > > as well as "M" in a single cell and have the total automatically calculated
> > > > for me?
> > > >
> > > > Currently looks like the following:
> > > >
> > > > Days of Absence
> > > > Employee name: Military "M" FMLA "F" 010106 010206
> > > > John Doe 1 1 M F
> > > >
> > > >
> > > > I want it to look like:
> > > >
> > > > Hours of Absence
> > > > Employee Name: Military "M" FMLA "F" 010106 010206
> > > > John Doe 8 4 8M 4F
> > > >
> > > > Is this possible without adding additional columns for the hours?
> > > >
> > > >
> > > >
> > > >