Help with a simple spreadsheet

  • Thread starter Thread starter Mike W.
  • Start date Start date
M

Mike W.

I want to make a spreadsheet that will be a calendar that will keep
track of days on call. I have 3 partners (doctors) and we have an
"assignment" every day. Assignments are A (call) B (second worker) C
(clinic) and off. To be fair on the schedule, I need to keep a running
tally of each assignment so no one does too many weekends, etc. I
started a calendar by making 7 columns (days) by 4 cells (assignments).I
need help with the following:
1. How do I do a formula to, say, total the number of "MW" in A slots
on Friday, Saturday and Sunday (columns 6, 7, and 1)?
2. Is there a calendar template, or should I just stay with my own?

Thanks for any help.
 
Mike,

=COUNTIF(A2:A5,"MW")+COUNTIF(F2:F5,"MW")+COUNTIF(G2:G5,"MW")
or
=COUNTIF(A2:A5,"MW")+COUNTIF(F2:G5,"MW")
 
"Mike W." ...
....
1. How do I do a formula to, say, total the number of "MW" in A slots
on Friday, Saturday and Sunday (columns 6, 7, and 1)?
Click to expand...

Not sure, just some thoughts ..

If you have set-up in Sheet1, cols A to G, data from row2 down

Sun Mon Tue Wed Thu Fri Sat
MW SE MW SE MW SE MW
SE JC SE JC SE MW JC
etc

In Sheet2,
you have listed in A2:A4: MW, SE, JC

We could put in B2:

=COUNTIF(Sheet1!A:A,A2)+COUNTIF(Sheet1!F:F,A2)+COUNTIF(Sheet1!G:G,A2)

and copy B2 down

Col will return the corresponding counts for MW, SE, JC
from cols 1,6,7 (i.e. cols A,F,G) in Sheet1


--
 
Mike

one way is to use an Array formula


Example in A1 type in
=SUM(IF(B1:B14="MW",1,0)), Press Alt + sHIFT + Enter keys
This will give you an array formula that loooks like
{=SUM(IF(B1:B14="MW",1,0))}
You can not type in {} you must use Alt + Shift+ Enter keys
 
Back
Top