Help with Absence sheet formula.
 

Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l;
bev; dom" and "late or Early finish"
All of these types of absence are entered in one row for a certain person on
a certain date. (calendar type of row,31 cells ). At the end of that row I
need to calculate and divide them monthly for each day of the week how many
absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or
"early" inputs.
I do have a formula and I thought if there is an simple one that I can use?
Here is a copy of the formula I use for each day in the week, this is for
Monday only:
=SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev"))

thank you

Help with Absence sheet formula.
 

Do the columns represent days of the week? If so, are there column headers
that are the *dates* for the month?

If that's the case then you can use a formula that tests the header row for
the day of the week and test the data row for those specific criteria:

H80:AL80 = *dates* for the month
H81:AL81 = data

To count instances of a/l, dom, sick, and bev for Mondays:

=SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0))))

For the other weekdays, in the WEEKDAY function change =n to:

For Monday: =1
For Tuesday: =2
For Wednesday: =3
For Thursday: =4
For Friday: =5
For Saturday: =6
For Sunday: =7


--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Hi, I manage a monthly absence sheet, we have 4 types of absences "sick;
a/l;
bev; dom" and "late or Early finish"
All of these types of absence are entered in one row for a certain person
on
a certain date. (calendar type of row,31 cells ). At the end of that row I
need to calculate and divide them monthly for each day of the week how
many
absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late"
or
"early" inputs.
I do have a formula and I thought if there is an simple one that I can
use?
Here is a copy of the formula I use for each day in the week, this is for
Monday only:
=SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev"))

thank you

Help with Absence sheet formula.
 

If this helps what I mean the headers are not actual, just for example. This
is how the first row of the list looks.


A B C D E F G H
AM AN ...
1 Mon Tue Wed Thu Fri Sat Sun Mon Tue ....(31 days). Mon Tue Wed
Thu Fri Sat Sun
2 sick late sick dom
2 0 0 0 0 0 1
3 sick sick late sick
1 2 0 0 0 0 0

At AM2 I put this formula that counts only Monday inputs:
=SUM(COUNTIF(A2,"a/l"),COUNTIF(A2,"dom"),COUNTIF(A2,"sick"),COUNTIF(A 2,"bev"),COUNTIF(G2,"sick"),COUNTIF(G2,"bev"),COUN TIF(G2,"dom"),COUNTIF(G2,"a/l"),COUNTIF(P2,"sick"),COUNTIF(P2,"bev"),COUNTIF(P 2,"dom"),COUNTIF(P2,"a/l"),COUNTIF(W2,"bev"),COUNTIF(W2,"dom"),COUNTIF(W2 ,"sick"),COUNTIF(W2,"a/l"),COUNTIF(AD2,"a/l"),COUNTIF(AD2,"dom"),COUNTIF(AD2,"sick"),COUNTIF (AD2,"bev"))

I don't count "Late" and "early" is there a simple formula to be put in AM2?

I don't know how else to explain this matter. Sorry to trouble you.

Regards




"Bojan" wrote:

Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l;
bev; dom" and "late or Early finish"
All of these types of absence are entered in one row for a certain person on
a certain date. (calendar type of row,31 cells ). At the end of that row I
need to calculate and divide them monthly for each day of the week how many
absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or
"early" inputs.
I do have a formula and I thought if there is an simple one that I can use?
Here is a copy of the formula I use for each day in the week, this is for
Monday only:
=SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev"))

thank you

Help with Absence sheet formula.
 

I can't make out the layout you posted. They usually get "shredded" by the
line/word wraps.

If your column headers are *text* entries Mon, Tue, Wed, etc:

=SUMPRODUCT(--(A1:AD1="mon"),--(ISNUMBER(MATCH(A2:AD2,{"a/l","dom","sick","bev"},0))))


--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
If this helps what I mean the headers are not actual, just for example.
This
is how the first row of the list looks.


A B C D E F G H
AM AN ...
1 Mon Tue Wed Thu Fri Sat Sun Mon Tue ....(31 days). Mon Tue
Wed
Thu Fri Sat Sun
2 sick late sick dom
2 0 0 0 0 0 1
3 sick sick late sick
1 2 0 0 0 0 0

At AM2 I put this formula that counts only Monday inputs:
=SUM(COUNTIF(A2,"a/l"),COUNTIF(A2,"dom"),COUNTIF(A2,"sick"),COUNTIF(A 2,"bev"),COUNTIF(G2,"sick"),COUNTIF(G2,"bev"),COUN TIF(G2,"dom"),COUNTIF(G2,"a/l"),COUNTIF(P2,"sick"),COUNTIF(P2,"bev"),COUNTIF(P 2,"dom"),COUNTIF(P2,"a/l"),COUNTIF(W2,"bev"),COUNTIF(W2,"dom"),COUNTIF(W2 ,"sick"),COUNTIF(W2,"a/l"),COUNTIF(AD2,"a/l"),COUNTIF(AD2,"dom"),COUNTIF(AD2,"sick"),COUNTIF (AD2,"bev"))

I don't count "Late" and "early" is there a simple formula to be put in
AM2?

I don't know how else to explain this matter. Sorry to trouble you.

Regards




"Bojan" wrote:

Hi, I manage a monthly absence sheet, we have 4 types of absences "sick;
a/l;
bev; dom" and "late or Early finish"
All of these types of absence are entered in one row for a certain person
on
a certain date. (calendar type of row,31 cells ). At the end of that row
I
need to calculate and divide them monthly for each day of the week how
many
absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late"
or
"early" inputs.
I do have a formula and I thought if there is an simple one that I can
use?
Here is a copy of the formula I use for each day in the week, this is for
Monday only:
=SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev"))

thank you

Help with Absence sheet formula.
 

I tried your formula but I'm lost, Sorry for the trouble, I'll just use the
old formula.

cheers

"T. Valko" wrote:

I can't make out the layout you posted. They usually get "shredded" by the
line/word wraps.

If your column headers are *text* entries Mon, Tue, Wed, etc:

=SUMPRODUCT(--(A1:AD1="mon"),--(ISNUMBER(MATCH(A2:AD2,{"a/l","dom","sick","bev"},0))))


--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
If this helps what I mean the headers are not actual, just for example.
This
is how the first row of the list looks.


A B C D E F G H
AM AN ...
1 Mon Tue Wed Thu Fri Sat Sun Mon Tue ....(31 days). Mon Tue
Wed
Thu Fri Sat Sun
2 sick late sick dom
2 0 0 0 0 0 1
3 sick sick late sick
1 2 0 0 0 0 0

At AM2 I put this formula that counts only Monday inputs:
=SUM(COUNTIF(A2,"a/l"),COUNTIF(A2,"dom"),COUNTIF(A2,"sick"),COUNTIF(A 2,"bev"),COUNTIF(G2,"sick"),COUNTIF(G2,"bev"),COUN TIF(G2,"dom"),COUNTIF(G2,"a/l"),COUNTIF(P2,"sick"),COUNTIF(P2,"bev"),COUNTIF(P 2,"dom"),COUNTIF(P2,"a/l"),COUNTIF(W2,"bev"),COUNTIF(W2,"dom"),COUNTIF(W2 ,"sick"),COUNTIF(W2,"a/l"),COUNTIF(AD2,"a/l"),COUNTIF(AD2,"dom"),COUNTIF(AD2,"sick"),COUNTIF (AD2,"bev"))

I don't count "Late" and "early" is there a simple formula to be put in
AM2?

I don't know how else to explain this matter. Sorry to trouble you.

Regards




"Bojan" wrote:

Hi, I manage a monthly absence sheet, we have 4 types of absences "sick;
a/l;
bev; dom" and "late or Early finish"
All of these types of absence are entered in one row for a certain person
on
a certain date. (calendar type of row,31 cells ). At the end of that row
I
need to calculate and divide them monthly for each day of the week how
many
absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late"
or
"early" inputs.
I do have a formula and I thought if there is an simple one that I can
use?
Here is a copy of the formula I use for each day in the week, this is for
Monday only:
=SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev"))

thank you

Help with Absence sheet formula.
 

Here's a small sample file that demonstrates this:

xBojan.xls 14kb

http://cjoint.com/?jshE0GTLCg

--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
I tried your formula but I'm lost, Sorry for the trouble, I'll just use the
old formula.

cheers

"T. Valko" wrote:

I can't make out the layout you posted. They usually get "shredded" by
the
line/word wraps.

If your column headers are *text* entries Mon, Tue, Wed, etc:

=SUMPRODUCT(--(A1:AD1="mon"),--(ISNUMBER(MATCH(A2:AD2,{"a/l","dom","sick","bev"},0))))


--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
If this helps what I mean the headers are not actual, just for example.
This
is how the first row of the list looks.


A B C D E F G H
AM AN ...
1 Mon Tue Wed Thu Fri Sat Sun Mon Tue ....(31 days). Mon
Tue
Wed
Thu Fri Sat Sun
2 sick late sick dom
2 0 0 0 0 0 1
3 sick sick late sick
1 2 0 0 0 0 0

At AM2 I put this formula that counts only Monday inputs:
=SUM(COUNTIF(A2,"a/l"),COUNTIF(A2,"dom"),COUNTIF(A2,"sick"),COUNTIF(A 2,"bev"),COUNTIF(G2,"sick"),COUNTIF(G2,"bev"),COUN TIF(G2,"dom"),COUNTIF(G2,"a/l"),COUNTIF(P2,"sick"),COUNTIF(P2,"bev"),COUNTIF(P 2,"dom"),COUNTIF(P2,"a/l"),COUNTIF(W2,"bev"),COUNTIF(W2,"dom"),COUNTIF(W2 ,"sick"),COUNTIF(W2,"a/l"),COUNTIF(AD2,"a/l"),COUNTIF(AD2,"dom"),COUNTIF(AD2,"sick"),COUNTIF (AD2,"bev"))

I don't count "Late" and "early" is there a simple formula to be put in
AM2?

I don't know how else to explain this matter. Sorry to trouble you.

Regards




"Bojan" wrote:

Hi, I manage a monthly absence sheet, we have 4 types of absences
"sick;
a/l;
bev; dom" and "late or Early finish"
All of these types of absence are entered in one row for a certain
person
on
a certain date. (calendar type of row,31 cells ). At the end of that
row
I
need to calculate and divide them monthly for each day of the week how
many
absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the
"Late"
or
"early" inputs.
I do have a formula and I thought if there is an simple one that I can
use?
Here is a copy of the formula I use for each day in the week, this is
for
Monday only:
=SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev"))

thank you

Help with Absence sheet formula.
 

Thank you Valko, that's what I needed, how can I learn more about this type
of formula?

Regards

"T. Valko" wrote:

Here's a small sample file that demonstrates this:

xBojan.xls 14kb

http://cjoint.com/?jshE0GTLCg

--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
I tried your formula but I'm lost, Sorry for the trouble, I'll just use the
old formula.

cheers

"T. Valko" wrote:

I can't make out the layout you posted. They usually get "shredded" by
the
line/word wraps.

If your column headers are *text* entries Mon, Tue, Wed, etc:

=SUMPRODUCT(--(A1:AD1="mon"),--(ISNUMBER(MATCH(A2:AD2,{"a/l","dom","sick","bev"},0))))


--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
If this helps what I mean the headers are not actual, just for example.
This
is how the first row of the list looks.


A B C D E F G H
AM AN ...
1 Mon Tue Wed Thu Fri Sat Sun Mon Tue ....(31 days). Mon
Tue
Wed
Thu Fri Sat Sun
2 sick late sick dom
2 0 0 0 0 0 1
3 sick sick late sick
1 2 0 0 0 0 0

At AM2 I put this formula that counts only Monday inputs:
=SUM(COUNTIF(A2,"a/l"),COUNTIF(A2,"dom"),COUNTIF(A2,"sick"),COUNTIF(A 2,"bev"),COUNTIF(G2,"sick"),COUNTIF(G2,"bev"),COUN TIF(G2,"dom"),COUNTIF(G2,"a/l"),COUNTIF(P2,"sick"),COUNTIF(P2,"bev"),COUNTIF(P 2,"dom"),COUNTIF(P2,"a/l"),COUNTIF(W2,"bev"),COUNTIF(W2,"dom"),COUNTIF(W2 ,"sick"),COUNTIF(W2,"a/l"),COUNTIF(AD2,"a/l"),COUNTIF(AD2,"dom"),COUNTIF(AD2,"sick"),COUNTIF (AD2,"bev"))

I don't count "Late" and "early" is there a simple formula to be put in
AM2?

I don't know how else to explain this matter. Sorry to trouble you.

Regards




"Bojan" wrote:

Hi, I manage a monthly absence sheet, we have 4 types of absences
"sick;
a/l;
bev; dom" and "late or Early finish"
All of these types of absence are entered in one row for a certain
person
on
a certain date. (calendar type of row,31 cells ). At the end of that
row
I
need to calculate and divide them monthly for each day of the week how
many
absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the
"Late"
or
"early" inputs.
I do have a formula and I thought if there is an simple one that I can
use?
Here is a copy of the formula I use for each day in the week, this is
for
Monday only:
=SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev"))

thank you

Help with Absence sheet formula.
 

Me again,

Thank you for your help, I have another question:

Is there a way to lock this "H80:AL80" part of the formula that tests the
days/dates so when I copy the formula to the following name in the list thay
can remain to count just that row-just the dates.

Example, Let's say I have 10 people on my absence list and one row with the
days or dates, If I copy the formula downwards, it will go H81:AL81, H82:AL82
etc... and I have to manually adjust each formula to be H80:AL80.

Thank you


"T. Valko" wrote:

Do the columns represent days of the week? If so, are there column headers
that are the *dates* for the month?

If that's the case then you can use a formula that tests the header row for
the day of the week and test the data row for those specific criteria:

H80:AL80 = *dates* for the month
H81:AL81 = data

To count instances of a/l, dom, sick, and bev for Mondays:

=SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0))))

For the other weekdays, in the WEEKDAY function change =n to:

For Monday: =1
For Tuesday: =2
For Wednesday: =3
For Thursday: =4
For Friday: =5
For Saturday: =6
For Sunday: =7


--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Hi, I manage a monthly absence sheet, we have 4 types of absences "sick;
a/l;
bev; dom" and "late or Early finish"
All of these types of absence are entered in one row for a certain person
on
a certain date. (calendar type of row,31 cells ). At the end of that row I
need to calculate and divide them monthly for each day of the week how
many
absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late"
or
"early" inputs.
I do have a formula and I thought if there is an simple one that I can
use?
Here is a copy of the formula I use for each day in the week, this is for
Monday only:
=SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev"))

thank you

Help with Absence sheet formula.
 

Using the formula from the sample file:

=SUMPRODUCT(--(A$1:O$1="mon"),--(ISNUMBER(MATCH(A2:O2,{"a/l","dom","sick","bev"},0))))

As you copy down the reference to A1:O1 will remain constant while the
reference to A2:O2 will increment as desired.

--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Me again,

Thank you for your help, I have another question:

Is there a way to lock this "H80:AL80" part of the formula that tests the
days/dates so when I copy the formula to the following name in the list
thay
can remain to count just that row-just the dates.

Example, Let's say I have 10 people on my absence list and one row with
the
days or dates, If I copy the formula downwards, it will go H81:AL81,
H82:AL82
etc... and I have to manually adjust each formula to be H80:AL80.

Thank you


"T. Valko" wrote:

Do the columns represent days of the week? If so, are there column
headers
that are the *dates* for the month?

If that's the case then you can use a formula that tests the header row
for
the day of the week and test the data row for those specific criteria:

H80:AL80 = *dates* for the month
H81:AL81 = data

To count instances of a/l, dom, sick, and bev for Mondays:

=SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0))))

For the other weekdays, in the WEEKDAY function change =n to:

For Monday: =1
For Tuesday: =2
For Wednesday: =3
For Thursday: =4
For Friday: =5
For Saturday: =6
For Sunday: =7


--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Hi, I manage a monthly absence sheet, we have 4 types of absences
"sick;
a/l;
bev; dom" and "late or Early finish"
All of these types of absence are entered in one row for a certain
person
on
a certain date. (calendar type of row,31 cells ). At the end of that
row I
need to calculate and divide them monthly for each day of the week how
many
absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the
"Late"
or
"early" inputs.
I do have a formula and I thought if there is an simple one that I can
use?
Here is a copy of the formula I use for each day in the week, this is
for
Monday only:
=SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev"))

thank you

Help with Absence sheet formula.
 

Thank you very Much Valko, it works!

Best Regards

"T. Valko" wrote:

Using the formula from the sample file:

=SUMPRODUCT(--(A$1:O$1="mon"),--(ISNUMBER(MATCH(A2:O2,{"a/l","dom","sick","bev"},0))))

As you copy down the reference to A1:O1 will remain constant while the
reference to A2:O2 will increment as desired.

--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Me again,

Thank you for your help, I have another question:

Is there a way to lock this "H80:AL80" part of the formula that tests the
days/dates so when I copy the formula to the following name in the list
thay
can remain to count just that row-just the dates.

Example, Let's say I have 10 people on my absence list and one row with
the
days or dates, If I copy the formula downwards, it will go H81:AL81,
H82:AL82
etc... and I have to manually adjust each formula to be H80:AL80.

Thank you


"T. Valko" wrote:

Do the columns represent days of the week? If so, are there column
headers
that are the *dates* for the month?

If that's the case then you can use a formula that tests the header row
for
the day of the week and test the data row for those specific criteria:

H80:AL80 = *dates* for the month
H81:AL81 = data

To count instances of a/l, dom, sick, and bev for Mondays:

=SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0))))

For the other weekdays, in the WEEKDAY function change =n to:

For Monday: =1
For Tuesday: =2
For Wednesday: =3
For Thursday: =4
For Friday: =5
For Saturday: =6
For Sunday: =7


--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Hi, I manage a monthly absence sheet, we have 4 types of absences
"sick;
a/l;
bev; dom" and "late or Early finish"
All of these types of absence are entered in one row for a certain
person
on
a certain date. (calendar type of row,31 cells ). At the end of that
row I
need to calculate and divide them monthly for each day of the week how
many
absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the
"Late"
or
"early" inputs.
I do have a formula and I thought if there is an simple one that I can
use?
Here is a copy of the formula I use for each day in the week, this is
for
Monday only:
=SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev"))

thank you

Help with Absence sheet formula.
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Thank you very Much Valko, it works!

Best Regards

"T. Valko" wrote:

Using the formula from the sample file:

=SUMPRODUCT(--(A$1:O$1="mon"),--(ISNUMBER(MATCH(A2:O2,{"a/l","dom","sick","bev"},0))))

As you copy down the reference to A1:O1 will remain constant while the
reference to A2:O2 will increment as desired.

--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Me again,

Thank you for your help, I have another question:

Is there a way to lock this "H80:AL80" part of the formula that tests
the
days/dates so when I copy the formula to the following name in the list
thay
can remain to count just that row-just the dates.

Example, Let's say I have 10 people on my absence list and one row with
the
days or dates, If I copy the formula downwards, it will go H81:AL81,
H82:AL82
etc... and I have to manually adjust each formula to be H80:AL80.

Thank you


"T. Valko" wrote:

Do the columns represent days of the week? If so, are there column
headers
that are the *dates* for the month?

If that's the case then you can use a formula that tests the header
row
for
the day of the week and test the data row for those specific criteria:

H80:AL80 = *dates* for the month
H81:AL81 = data

To count instances of a/l, dom, sick, and bev for Mondays:

=SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0))))

For the other weekdays, in the WEEKDAY function change =n to:

For Monday: =1
For Tuesday: =2
For Wednesday: =3
For Thursday: =4
For Friday: =5
For Saturday: =6
For Sunday: =7


--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Hi, I manage a monthly absence sheet, we have 4 types of absences
"sick;
a/l;
bev; dom" and "late or Early finish"
All of these types of absence are entered in one row for a certain
person
on
a certain date. (calendar type of row,31 cells ). At the end of that
row I
need to calculate and divide them monthly for each day of the week
how
many
absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the
"Late"
or
"early" inputs.
I do have a formula and I thought if there is an simple one that I
can
use?
Here is a copy of the formula I use for each day in the week, this
is
for
Monday only:
=SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev"))

thank you

Help with Absence sheet formula.
 

you could shave a few keystrokes

=SUMPRODUCT((A$1:O$1="mon")*(A2:O2={"a/l";"dom";"sick";"bev"}))


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Thank you very Much Valko, it works!

Best Regards

"T. Valko" wrote:

Using the formula from the sample file:

=SUMPRODUCT(--(A$1:O$1="mon"),--(ISNUMBER(MATCH(A2:O2,{"a/l","dom","sick","bev"},0))))

As you copy down the reference to A1:O1 will remain constant while the
reference to A2:O2 will increment as desired.

--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Me again,

Thank you for your help, I have another question:

Is there a way to lock this "H80:AL80" part of the formula that tests
the
days/dates so when I copy the formula to the following name in the list
thay
can remain to count just that row-just the dates.

Example, Let's say I have 10 people on my absence list and one row with
the
days or dates, If I copy the formula downwards, it will go H81:AL81,
H82:AL82
etc... and I have to manually adjust each formula to be H80:AL80.

Thank you


"T. Valko" wrote:

Do the columns represent days of the week? If so, are there column
headers
that are the *dates* for the month?

If that's the case then you can use a formula that tests the header
row
for
the day of the week and test the data row for those specific criteria:

H80:AL80 = *dates* for the month
H81:AL81 = data

To count instances of a/l, dom, sick, and bev for Mondays:

=SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0))))

For the other weekdays, in the WEEKDAY function change =n to:

For Monday: =1
For Tuesday: =2
For Wednesday: =3
For Thursday: =4
For Friday: =5
For Saturday: =6
For Sunday: =7


--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Hi, I manage a monthly absence sheet, we have 4 types of absences
"sick;
a/l;
bev; dom" and "late or Early finish"
All of these types of absence are entered in one row for a certain
person
on
a certain date. (calendar type of row,31 cells ). At the end of that
row I
need to calculate and divide them monthly for each day of the week
how
many
absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the
"Late"
or
"early" inputs.
I do have a formula and I thought if there is an simple one that I
can
use?
Here is a copy of the formula I use for each day in the week, this
is
for
Monday only:
=SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev"))

thank you

Help with Absence sheet formula.
 

It's a trade-off.

That formula is a few keystrokes shorter but the other formula has a very
slight efficiency advantage.

--
Biff
Microsoft Excel MVP


"JMB" wrote in message
...
you could shave a few keystrokes

=SUMPRODUCT((A$1:O$1="mon")*(A2:O2={"a/l";"dom";"sick";"bev"}))


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Thank you very Much Valko, it works!

Best Regards

"T. Valko" wrote:

Using the formula from the sample file:

=SUMPRODUCT(--(A$1:O$1="mon"),--(ISNUMBER(MATCH(A2:O2,{"a/l","dom","sick","bev"},0))))

As you copy down the reference to A1:O1 will remain constant while the
reference to A2:O2 will increment as desired.

--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Me again,

Thank you for your help, I have another question:

Is there a way to lock this "H80:AL80" part of the formula that
tests
the
days/dates so when I copy the formula to the following name in the
list
thay
can remain to count just that row-just the dates.

Example, Let's say I have 10 people on my absence list and one row
with
the
days or dates, If I copy the formula downwards, it will go H81:AL81,
H82:AL82
etc... and I have to manually adjust each formula to be H80:AL80.

Thank you


"T. Valko" wrote:

Do the columns represent days of the week? If so, are there column
headers
that are the *dates* for the month?

If that's the case then you can use a formula that tests the header
row
for
the day of the week and test the data row for those specific
criteria:

H80:AL80 = *dates* for the month
H81:AL81 = data

To count instances of a/l, dom, sick, and bev for Mondays:

=SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0))))

For the other weekdays, in the WEEKDAY function change =n to:

For Monday: =1
For Tuesday: =2
For Wednesday: =3
For Thursday: =4
For Friday: =5
For Saturday: =6
For Sunday: =7


--
Biff
Microsoft Excel MVP


"Bojan" wrote in message
...
Hi, I manage a monthly absence sheet, we have 4 types of absences
"sick;
a/l;
bev; dom" and "late or Early finish"
All of these types of absence are entered in one row for a
certain
person
on
a certain date. (calendar type of row,31 cells ). At the end of
that
row I
need to calculate and divide them monthly for each day of the
week
how
many
absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the
"Late"
or
"early" inputs.
I do have a formula and I thought if there is an simple one that
I
can
use?
Here is a copy of the formula I use for each day in the week,
this
is
for
Monday only:
=SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev"))

thank you