Calcaulate # of days worked
 

I have a very large spreadsheet of data for approx 400 employees. Each
employee has a row for each date, and some employees have 2 rows for some
dates. I want to quickly calculate the number of days worked for each
employee, and also calculate the average number of days worked for each
employee.

Is there some way I can do this quickly? I know I can use a pivot table to
make a count by employee, but can I set it up to only count cells that
contain certain characters? Also, if an employee has 2 rows for a specific
date, can it only count that date once?

On the example below, it would count anything with XX or CBUS as a day
worked. since 2/4 has 2 lines, this employee worked a total of 3 days.

ID Date Activity
5 2/1 OFF
5 2/2 XX100
5 2/3 CBUS
5 2/4 XX101
5 2/4 XX102
5 2/5 SICK
5 2/6 OFF

Calcaulate # of days worked
 

You should be able to do this using the COUNTIF or LOOKUP functions. If you
go to the help menu and type in Worksheet Functions, the ensuing list of
choices should include an option for a "list of worksheet functions" with a
basic description of wht each one does. If this gets you no further, let me
know.

"richzip" wrote:

I have a very large spreadsheet of data for approx 400 employees. Each
employee has a row for each date, and some employees have 2 rows for some
dates. I want to quickly calculate the number of days worked for each
employee, and also calculate the average number of days worked for each
employee.

Is there some way I can do this quickly? I know I can use a pivot table to
make a count by employee, but can I set it up to only count cells that
contain certain characters? Also, if an employee has 2 rows for a specific
date, can it only count that date once?

On the example below, it would count anything with XX or CBUS as a day
worked. since 2/4 has 2 lines, this employee worked a total of 3 days.

ID Date Activity
5 2/1 OFF
5 2/2 XX100
5 2/3 CBUS
5 2/4 XX101
5 2/4 XX102
5 2/5 SICK
5 2/6 OFF

Calcaulate # of days worked
 

Try this array formula** :

=COUNT(1/FREQUENCY(IF((ID=5)*(ISNUMBER(SEARCH({"xx","cbus"} ,Activity))),Date),Date))

Assuming there are no empty date cells like this:

ID Date Activity
5 2/1 OFF
5 XX100
5 2/3 CBUS
5 2/4 XX101

it would count anything with XX or CBUS

The above will count entries like: 100XX, 10XX0. Basically, anything that
contains "XX".

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"richzip" wrote in message
...
I have a very large spreadsheet of data for approx 400 employees. Each
employee has a row for each date, and some employees have 2 rows for some
dates. I want to quickly calculate the number of days worked for each
employee, and also calculate the average number of days worked for each
employee.

Is there some way I can do this quickly? I know I can use a pivot table
to
make a count by employee, but can I set it up to only count cells that
contain certain characters? Also, if an employee has 2 rows for a
specific
date, can it only count that date once?

On the example below, it would count anything with XX or CBUS as a day
worked. since 2/4 has 2 lines, this employee worked a total of 3 days.

ID Date Activity
5 2/1 OFF
5 2/2 XX100
5 2/3 CBUS
5 2/4 XX101
5 2/4 XX102
5 2/5 SICK
5 2/6 OFF