I have an excel spreadsheet which tracks staff whereabouts, laid out as following
Column A is a list of staff
Column B is a date
Column C is AM
Column D is PM
Column E is Notes
On the same sheet, I want to show total number of days per staff member and total number of instances.
For example staff member a may be sick on the following days
01/05/2011
02/05/2011
03/05/2011
11/11/2011
20/11/2011
21/11/2011
15/02/2012
That is 7 days sick but only 4 instances. how can i show this?
I did try: =MAX(FREQUENCY(IF(C1:D1000="Sick",ROW(B1:B1000)),IF(C1:D1000<>"Sick",ROW(B1:B1000)))) which brings back the total number of sicks in column c and column d but i was this per person
Paul
Bookmarks