Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

1 Answer

Assuming the name column is A and the date column is B, and that you have data in rows 2-100, you can paste this formula into C2, then CTRL+SHIFT+Enter to confirm it as an array formula, then fill out the rest of C from C2:

=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))

For each value in A you'll have the same number in C, that being the count of unique values in B adjacent to that value of A.

share|improve this answer
    
Have you tried it? I think it actually relies on that distinction. –  Kev Apr 3 '13 at 1:13
    
Even with data as you described, changing the formula did not seem to make a difference, the results were still correct. I suspect you may be right though, I think I was thinking a different formula where you had to fix one of the parameters in order to count correctly. So I'll make the suggested change. Thanks for your help. –  Kev Apr 6 '13 at 12:23
1  
@pnuts, sorry for my tardiness, life got crazy lately. Thanks for going out of your way to fix things! –  Kev Apr 6 '13 at 22:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.