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

Your query is not clear. To count the number of 'p's, just use
=COUNTIF(B1:E1,"p")
this is considering that your data for john lies in range B1 to E2
similarly for 'a's
=COUNTIF(B1:E1,"a")

If there are any blanks as on holidays, then you can count blanks using
=COUNTBLANK(B1:E1)

The total of all the above 3 should add up to your total days.


- Mangesh

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >.
> >
>

Originally Posted by Ajit Munj

Now I want to calculate the total Sundays, Mondays, Tuesdays etc. present in
that month. How can I do this?
--
Knowldege is Power

in row 1 first cell (A1) is blank (this is where you will your first Sunday's date will be placed. Cell B1 will be =A1+1 (that will give you Monday's date For cell C1 put =A1+2. D1 would read ]=A1+7 this will give you Sundays date. then the next cell (E1) would be ]=D1+1 which is Mondays date. then follow the pattern ( +7,+1 +2,+7 etc) do this about 5 times for a 31 day month.
now if you don't want to see any date other than that month you can use this formula you cant you conditional formatting using formula =OR(DAY(A1)>DAY(B1),DAY(A1)>DAY(C1)) format text to white then use format painter to copy formatting across the cells.

Hope this helps