Count for present & absent days
 

I use Excel 2000
I have sheet muster for my clients of January, 2005 like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4

1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays, Tuesdays etc. present in
that month. How can I do this?
--
Knowldege is Power

This should get you started. To count the total # of P's
for John for January (assuming your data below is just
for January), try:

=SUMPRODUCT((3:3="P")*(1:1="Sun"))

where John is listed on row 3 and the Days are listed in
row 1.

HTH
Jason
Atlanta, GA

-----Original Message-----
I use Excel 2000
I have sheet muster for my clients of January, 2005
like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4

1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays,
Tuesdays etc. present in
that month. How can I do this?
--
Knowldege is Power
.

Thanks Jason,It's working.
I also want to convert actual numbers (a1=46556.4646, a2=787979.79798 etc.)
into lacs (i.e. a1=0.465564646, a2=7.87979.79798 etc.) at one go and also one
by one. How can I do this? I also want Excel to prompt me for convesion in
Lacs or Crores or Thousands? Pl. guide.

Ajit
"Jason Morin" wrote:

This should get you started. To count the total # of P's
for John for January (assuming your data below is just
for January), try:

=SUMPRODUCT((3:3="P")*(1:1="Sun"))

where John is listed on row 3 and the Days are listed in
row 1.

HTH
Jason
Atlanta, GA

-----Original Message-----
I use Excel 2000
I have sheet muster for my clients of January, 2005
like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4

1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays,
Tuesdays etc. present in
that month. How can I do this?
--
Knowldege is Power
.

You divide cells one by one with a formula in another
cell:

=A1/100000

or to do all of them at once, put 100000 in an open cell,
copy it, select your numbers in column A, and go to Edit
Paste Special Divide.

HTH
Jason
Atlanta, Ga

-----Original Message-----
Thanks Jason,It's working.
I also want to convert actual numbers (a1=46556.4646,
a2=787979.79798 etc.)
into lacs (i.e. a1=0.465564646, a2=7.87979.79798 etc.)
at one go and also one
by one. How can I do this? I also want Excel to prompt
me for convesion in
Lacs or Crores or Thousands? Pl. guide.

Ajit
"Jason Morin" wrote:

This should get you started. To count the total # of
P's
for John for January (assuming your data below is just
for January), try:

=SUMPRODUCT((3:3="P")*(1:1="Sun"))

where John is listed on row 3 and the Days are listed
in
row 1.

HTH
Jason
Atlanta, GA

-----Original Message-----
I use Excel 2000
I have sheet muster for my clients of January, 2005
like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4

1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays,
Tuesdays etc. present in
that month. How can I do this?
--
Knowldege is Power
.


.

You can also use the = countif() function
For Sunday (example)
In Cell B50 enter =COUNTIF(B3:B49,"P") <<< Will count the number of
"P"s
where you have data in cells B3:B49


"Jason Morin" wrote in message
...
This should get you started. To count the total # of P's
for John for January (assuming your data below is just
for January), try:

=SUMPRODUCT((3:3="P")*(1:1="Sun"))

where John is listed on row 3 and the Days are listed in
row 1.

HTH
Jason
Atlanta, GA

-----Original Message-----
I use Excel 2000
I have sheet muster for my clients of January, 2005
like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4

1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays,
Tuesdays etc. present in
that month. How can I do this?
--
Knowldege is Power
.

It's great Jason. It was so simple! Now one more help.
I have workbook named News.xls with 3 sheets namely Pricelist, Muster and
Bill for the month of January, 2005.
Muster contains presenty of each client, marked P for present & A for Absent
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4
1 John P A A P
2 Lucy A P P A
3

Pricelist contains prices for news papers (amount in Indian Rupees)
A.....B.....C.....D.......E.........F.......G..... ..H...
Days Sun Mon Tue Wed Thu Fri Sat

Times 5 4 4 4 4 4 3.50

Economic 9 2 2 2 2 2 9

Now I want to prepare bills in sheet "Bill". I want Excel to check first
from Muster whether client is marked as "P" on 1st of Jan, 05 (Saturday), if
so, he should take a price of Times newspaper for Saturday from sheet
Pricelist i.e. Rs.5/-, if not, put 0.00, & so on.. Thus my sheet will show :


A.....B.....C.....D.....E.......F......G.......H.. ....I..Total
Days Sat Sun Mon Tue Wed Thu Fri Sat
Date 1 2 3 4

1 John 4 5 4 4 4 0 0 4
25.00

Note : On Thu & Fri John was marked as "A"
How can I perform this in sheet Bill.



"Jason Morin" wrote:

You divide cells one by one with a formula in another
cell:

=A1/100000

or to do all of them at once, put 100000 in an open cell,
copy it, select your numbers in column A, and go to Edit
Paste Special Divide.

HTH
Jason
Atlanta, Ga

-----Original Message-----
Thanks Jason,It's working.
I also want to convert actual numbers (a1=46556.4646,
a2=787979.79798 etc.)
into lacs (i.e. a1=0.465564646, a2=7.87979.79798 etc.)
at one go and also one
by one. How can I do this? I also want Excel to prompt
me for convesion in
Lacs or Crores or Thousands? Pl. guide.

Ajit
"Jason Morin" wrote:

This should get you started. To count the total # of
P's
for John for January (assuming your data below is just
for January), try:

=SUMPRODUCT((3:3="P")*(1:1="Sun"))

where John is listed on row 3 and the Days are listed
in
row 1.

HTH
Jason
Atlanta, GA

-----Original Message-----
I use Excel 2000
I have sheet muster for my clients of January, 2005
like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4

1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays,
Tuesdays etc. present in
that month. How can I do this?
--
Knowldege is Power
.


.

Dear Jason,
I tried your formula but some problem is there. I have made two sheets namely
Muster & Bill. In Muster, instead of P & A, I have entered the name of
newspaper. Hence I made some change in formula like
:=SUMPRODUCT((Muster!3:3<"")*(Muster!1:1="Sun")), it does not get tallied
with the total number of days in that month. e.g. In month January, 2005
there are 4 instances of Sun-Wed & 5 instances of Sat and Fri i.e. total 31
days.(I have not kept any cell blank as I have put a newspaper from 1 to 31).
After entering formula, it shows total 29 days, i.e. 5 instances of Sat. & 4
instances of Sun-Fri. Where am I going wrong?. Secondly, by refering 4:4 and
2:2, if accidently somebody enter a day and date, suppose in a far away cell
IN2 and IN4, the formula will show additional day. Pl. guide.
ajit

"Jason Morin" wrote:

This should get you started. To count the total # of P's
for John for January (assuming your data below is just
for January), try:

=SUMPRODUCT((3:3="P")*(1:1="Sun"))

where John is listed on row 3 and the Days are listed in
row 1.

HTH
Jason
Atlanta, GA

-----Original Message-----
I use Excel 2000
I have sheet muster for my clients of January, 2005
like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4

1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays,
Tuesdays etc. present in
that month. How can I do this?
--
Knowldege is Power
.