Try this. It seems to work according to my understanding of what you want.

Here's the formula:

=COUNTIF(B$3:B$9,A10)+IF(SUMPRODUCT(--(ISNUMBER(SEARCH(A10,B$3:B$9))))-COUNTIF(B$3:B$9,A10)0,1,0)/IF(SUMPRODUCT(--(ISNUMBER(SEARCH(A10,B$3:B$9))))=0,1,SUMPRODUCT(--(ISNUMBER(SEARCH(A10,B$3:B$9)))))

And here's a screencap to verify that the results are what you're looking
for:

http://img373.imageshack.us/img373/5...rojects5cs.jpg

Biff

"Biff" wrote in message
...
Ok, I think I understand what you want now. People per project rather than
projects per person which is what I was doing.

Hmmm...... this is going to be rather complicated. Let me see what I can
do.

Biff

"Randhir" wrote in message
...
Sorry the table did not come through the way I intended. The screenshot
was a
good idea, so here is the table I tried to create:
http://static.flickr.com/75/166250309_a9648c0f7c_o.jpg

What I am trying to do is find out how many resources Proj1 is consuming.
Since Kevin is working on Proj1 and Proj2 in Jan, I am assuming it is 0.5
resources and since John is full time on it, the total resources for
Project
1 is 1.5. I am now trying to find a way to automate this using your
earlier
suggestion.

One clunky way is to create a parallel table structure using your formula
and then sum it up in the way I want it (a6:d7), but if there are other
simpler and more efficient ways to get there, I would love to learn about
them.