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