Count for present & absent days

[Ajit Munj]

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?

 

[Jason Morin]

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

 

[Ajit Munj]

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

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

 

[Jason Morin]

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

 

[Jim May]

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

 

[Ajit Munj]

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.

 

[Ajit Munj]

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

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