My manager has asked me to enter an excel formula which calculates the total
number of leave types taken per staff member. The data is derived from a
table whereby the employees names are listed down one column, and the number
of days in a month across the top row. In each cell after the persons name,
there is a drop down menu which you can choose; ST, SL, AL, DL - all relate
to different types of leave. Im required to enter a formula in an adjacent
table which calculates the total number of any particular leave taken, the
table has columns dedicated to a particular type of leave "ST or SL", each
row is strategically placed so that the persons name and the result of the
number of leave they take during the 30 or 31 day period is totalled in each
column according to the particular type of leave.

Is there any formula in excel i can use that first checks, say the first day
to see if the person has take annual leave (AL), and if they have, to than
total the number of AL taken throughout the 31 days (or 31 cells - row)???
HELP!!!!.

I was initially looking at an IF-then formula; =IF(A2:A32=AV9, then total
the number of AV9 successes across this range,"0")
**A2:A32 represents the 31 days (or cells) which can have any number of the
various leave type depending on what type of leave that particular employee
takes.

Could you please send an small example, thanks
"feausij" wrote:

My manager has asked me to enter an excel formula which calculates the total
number of leave types taken per staff member. The data is derived from a
table whereby the employees names are listed down one column, and the number
of days in a month across the top row. In each cell after the persons name,
there is a drop down menu which you can choose; ST, SL, AL, DL - all relate
to different types of leave. Im required to enter a formula in an adjacent
table which calculates the total number of any particular leave taken, the
table has columns dedicated to a particular type of leave "ST or SL", each
row is strategically placed so that the persons name and the result of the
number of leave they take during the 30 or 31 day period is totalled in each
column according to the particular type of leave.

Is there any formula in excel i can use that first checks, say the first day
to see if the person has take annual leave (AL), and if they have, to than
total the number of AL taken throughout the 31 days (or 31 cells - row)???
HELP!!!!.

I was initially looking at an IF-then formula; =IF(A2:A32=AV9, then total
the number of AV9 successes across this range,"0")
**A2:A32 represents the 31 days (or cells) which can have any number of the
various leave type depending on what type of leave that particular employee
takes.

Use CountIF

=Countif(A2:A32,"AL")

If this post helps click Yes
---------------
Jacob Skaria


"feausij" wrote:

My manager has asked me to enter an excel formula which calculates the total
number of leave types taken per staff member. The data is derived from a
table whereby the employees names are listed down one column, and the number
of days in a month across the top row. In each cell after the persons name,
there is a drop down menu which you can choose; ST, SL, AL, DL - all relate
to different types of leave. Im required to enter a formula in an adjacent
table which calculates the total number of any particular leave taken, the
table has columns dedicated to a particular type of leave "ST or SL", each
row is strategically placed so that the persons name and the result of the
number of leave they take during the 30 or 31 day period is totalled in each
column according to the particular type of leave.

Is there any formula in excel i can use that first checks, say the first day
to see if the person has take annual leave (AL), and if they have, to than
total the number of AL taken throughout the 31 days (or 31 cells - row)???
HELP!!!!.

I was initially looking at an IF-then formula; =IF(A2:A32=AV9, then total
the number of AV9 successes across this range,"0")
**A2:A32 represents the 31 days (or cells) which can have any number of the
various leave type depending on what type of leave that particular employee
takes.

Hey guys thanks for the tips. The table looks like this:
TABLE 1 TABLE 2
1 2 3 4 SL AL DL BL
James AL 0 0 SL 1 1 0 0 <-- (formula
required for these cells)
John 0 SL SL SL 3 0 0 0
Jason AL SL SL 0 2 1 0 0
Emma 0 0 BL BL 0 0 0 2

I need to enter a formula in Table 2 that gives the results (above), and
tallied for any given number of a particular type of leave, e.g. John had 3
sick leave days within 4 days, which corresponds to the result in Table 2
under the SL column. (SL=sick leave, AL=annual leave, etc).
PLS HELP....


"Jacob Skaria" wrote:

Use CountIF

=Countif(A2:A32,"AL")

If this post helps click Yes
---------------
Jacob Skaria


"feausij" wrote:

My manager has asked me to enter an excel formula which calculates the total
number of leave types taken per staff member. The data is derived from a
table whereby the employees names are listed down one column, and the number
of days in a month across the top row. In each cell after the persons name,
there is a drop down menu which you can choose; ST, SL, AL, DL - all relate
to different types of leave. Im required to enter a formula in an adjacent
table which calculates the total number of any particular leave taken, the
table has columns dedicated to a particular type of leave "ST or SL", each
row is strategically placed so that the persons name and the result of the
number of leave they take during the 30 or 31 day period is totalled in each
column according to the particular type of leave.

Is there any formula in excel i can use that first checks, say the first day
to see if the person has take annual leave (AL), and if they have, to than
total the number of AL taken throughout the 31 days (or 31 cells - row)???
HELP!!!!.

I was initially looking at an IF-then formula; =IF(A2:A32=AV9, then total
the number of AV9 successes across this range,"0")
**A2:A32 represents the 31 days (or cells) which can have any number of the
various leave type depending on what type of leave that particular employee
takes.

Assuming table 2 headers (SL,AL,DL,BL) in cells G1,H1,I1,J1

A2 = James

Enter the below formula in G2.
=COUNTIF($B2:$E2,G$1)

Copy that to the right upto J2. Then copy the formulas down..
--
If this post helps click Yes
---------------
Jacob Skaria


"feausij" wrote:

Hey guys thanks for the tips. The table looks like this:
TABLE 1 TABLE 2
1 2 3 4 SL AL DL BL
James AL 0 0 SL 1 1 0 0 <-- (formula
required for these cells)
John 0 SL SL SL 3 0 0 0
Jason AL SL SL 0 2 1 0 0
Emma 0 0 BL BL 0 0 0 2

I need to enter a formula in Table 2 that gives the results (above), and
tallied for any given number of a particular type of leave, e.g. John had 3
sick leave days within 4 days, which corresponds to the result in Table 2
under the SL column. (SL=sick leave, AL=annual leave, etc).
PLS HELP....


"Jacob Skaria" wrote:

Use CountIF

=Countif(A2:A32,"AL")

If this post helps click Yes
---------------
Jacob Skaria


"feausij" wrote:

My manager has asked me to enter an excel formula which calculates the total
number of leave types taken per staff member. The data is derived from a
table whereby the employees names are listed down one column, and the number
of days in a month across the top row. In each cell after the persons name,
there is a drop down menu which you can choose; ST, SL, AL, DL - all relate
to different types of leave. Im required to enter a formula in an adjacent
table which calculates the total number of any particular leave taken, the
table has columns dedicated to a particular type of leave "ST or SL", each
row is strategically placed so that the persons name and the result of the
number of leave they take during the 30 or 31 day period is totalled in each
column according to the particular type of leave.

Is there any formula in excel i can use that first checks, say the first day
to see if the person has take annual leave (AL), and if they have, to than
total the number of AL taken throughout the 31 days (or 31 cells - row)???
HELP!!!!.

I was initially looking at an IF-then formula; =IF(A2:A32=AV9, then total
the number of AV9 successes across this range,"0")
**A2:A32 represents the 31 days (or cells) which can have any number of the
various leave type depending on what type of leave that particular employee
takes.

Hi,
I assume that table 2 starts in column AG and SL is in AG1 change it to fit
your needs

=COUNTIF($a2:$AF2,AG$1)



"feausij" wrote:

Hey guys thanks for the tips. The table looks like this:
TABLE 1 TABLE 2
1 2 3 4 SL AL DL BL
James AL 0 0 SL 1 1 0 0 <-- (formula
required for these cells)
John 0 SL SL SL 3 0 0 0
Jason AL SL SL 0 2 1 0 0
Emma 0 0 BL BL 0 0 0 2

I need to enter a formula in Table 2 that gives the results (above), and
tallied for any given number of a particular type of leave, e.g. John had 3
sick leave days within 4 days, which corresponds to the result in Table 2
under the SL column. (SL=sick leave, AL=annual leave, etc).
PLS HELP....


"Jacob Skaria" wrote:

Use CountIF

=Countif(A2:A32,"AL")

If this post helps click Yes
---------------
Jacob Skaria


"feausij" wrote:

My manager has asked me to enter an excel formula which calculates the total
number of leave types taken per staff member. The data is derived from a
table whereby the employees names are listed down one column, and the number
of days in a month across the top row. In each cell after the persons name,
there is a drop down menu which you can choose; ST, SL, AL, DL - all relate
to different types of leave. Im required to enter a formula in an adjacent
table which calculates the total number of any particular leave taken, the
table has columns dedicated to a particular type of leave "ST or SL", each
row is strategically placed so that the persons name and the result of the
number of leave they take during the 30 or 31 day period is totalled in each
column according to the particular type of leave.

Is there any formula in excel i can use that first checks, say the first day
to see if the person has take annual leave (AL), and if they have, to than
total the number of AL taken throughout the 31 days (or 31 cells - row)???
HELP!!!!.

I was initially looking at an IF-then formula; =IF(A2:A32=AV9, then total
the number of AV9 successes across this range,"0")
**A2:A32 represents the 31 days (or cells) which can have any number of the
various leave type depending on what type of leave that particular employee
takes.

Thankyou guys (Eduardo and Jacob), you guys are the best, thankyou,
thankyou...It works beatifully...woohooo!!!!

"Eduardo" wrote:

Hi,
I assume that table 2 starts in column AG and SL is in AG1 change it to fit
your needs

=COUNTIF($a2:$AF2,AG$1)



"feausij" wrote:

Hey guys thanks for the tips. The table looks like this:
TABLE 1 TABLE 2
1 2 3 4 SL AL DL BL
James AL 0 0 SL 1 1 0 0 <-- (formula
required for these cells)
John 0 SL SL SL 3 0 0 0
Jason AL SL SL 0 2 1 0 0
Emma 0 0 BL BL 0 0 0 2

I need to enter a formula in Table 2 that gives the results (above), and
tallied for any given number of a particular type of leave, e.g. John had 3
sick leave days within 4 days, which corresponds to the result in Table 2
under the SL column. (SL=sick leave, AL=annual leave, etc).
PLS HELP....


"Jacob Skaria" wrote:

Use CountIF

=Countif(A2:A32,"AL")

If this post helps click Yes
---------------
Jacob Skaria


"feausij" wrote:

My manager has asked me to enter an excel formula which calculates the total
number of leave types taken per staff member. The data is derived from a
table whereby the employees names are listed down one column, and the number
of days in a month across the top row. In each cell after the persons name,
there is a drop down menu which you can choose; ST, SL, AL, DL - all relate
to different types of leave. Im required to enter a formula in an adjacent
table which calculates the total number of any particular leave taken, the
table has columns dedicated to a particular type of leave "ST or SL", each
row is strategically placed so that the persons name and the result of the
number of leave they take during the 30 or 31 day period is totalled in each
column according to the particular type of leave.

Is there any formula in excel i can use that first checks, say the first day
to see if the person has take annual leave (AL), and if they have, to than
total the number of AL taken throughout the 31 days (or 31 cells - row)???
HELP!!!!.

I was initially looking at an IF-then formula; =IF(A2:A32=AV9, then total
the number of AV9 successes across this range,"0")
**A2:A32 represents the 31 days (or cells) which can have any number of the
various leave type depending on what type of leave that particular employee
takes.

your welcome

"feausij" wrote:

Thankyou guys (Eduardo and Jacob), you guys are the best, thankyou,
thankyou...It works beatifully...woohooo!!!!

"Eduardo" wrote:

Hi,
I assume that table 2 starts in column AG and SL is in AG1 change it to fit
your needs

=COUNTIF($a2:$AF2,AG$1)



"feausij" wrote:

Hey guys thanks for the tips. The table looks like this:
TABLE 1 TABLE 2
1 2 3 4 SL AL DL BL
James AL 0 0 SL 1 1 0 0 <-- (formula
required for these cells)
John 0 SL SL SL 3 0 0 0
Jason AL SL SL 0 2 1 0 0
Emma 0 0 BL BL 0 0 0 2

I need to enter a formula in Table 2 that gives the results (above), and
tallied for any given number of a particular type of leave, e.g. John had 3
sick leave days within 4 days, which corresponds to the result in Table 2
under the SL column. (SL=sick leave, AL=annual leave, etc).
PLS HELP....


"Jacob Skaria" wrote:

Use CountIF

=Countif(A2:A32,"AL")

If this post helps click Yes
---------------
Jacob Skaria


"feausij" wrote:

My manager has asked me to enter an excel formula which calculates the total
number of leave types taken per staff member. The data is derived from a
table whereby the employees names are listed down one column, and the number
of days in a month across the top row. In each cell after the persons name,
there is a drop down menu which you can choose; ST, SL, AL, DL - all relate
to different types of leave. Im required to enter a formula in an adjacent
table which calculates the total number of any particular leave taken, the
table has columns dedicated to a particular type of leave "ST or SL", each
row is strategically placed so that the persons name and the result of the
number of leave they take during the 30 or 31 day period is totalled in each
column according to the particular type of leave.

Is there any formula in excel i can use that first checks, say the first day
to see if the person has take annual leave (AL), and if they have, to than
total the number of AL taken throughout the 31 days (or 31 cells - row)???
HELP!!!!.

I was initially looking at an IF-then formula; =IF(A2:A32=AV9, then total
the number of AV9 successes across this range,"0")
**A2:A32 represents the 31 days (or cells) which can have any number of the
various leave type depending on what type of leave that particular employee
takes.