# Excel formula creation

I have a spreadsheet with a lot of job related data. One column has names of people in it and another column has the dates on which those people completed jobs. Each person may do more than 1 job per day but each job will have a seperate row so a persons name will appear many times in the name column associated with dulicated dates.

I am trying to count the number of "equivalent" days worked - by identifying the number of seperate dates each person has done at least 1 job on and adding all the number of dates for each seperate person to get the total. This will have no relationship to the total number of jobs done collectively or by any indivdual.

Hope this makes sense and someone can help me..

Optional Information: What have you tried so far?: COUNTIFS, NETWORKDAYS and many combinations of similiar things. I need a formula that works on the data as it is presented. I do not want to have to insert columns to count things seperately or do some workings on hidden sheets etc.

-
Don't you need a column to capture equivalent days worked? Where/how do you want the results to show up? – Carl B Mar 25 '13 at 0:36

``````=SUM(IF(FREQUENCY(IF(A\$2:A\$100=A2,MATCH(A\$2:A\$100&"_"&B\$2:B\$100,A\$2:A\$100&"_"&B\$2:B\$100,0)),ROW(A\$2:A\$100)-ROW(A\$2)+1),1))