I have a rather long spreadsheet with historical information of issues solved by some user on a collaborative environment.

The spreadsheet have the following (relevant) columns

date, week no., project, author id, etc...

The week no. is calculated from the date, is basically the year concatenated with the week number within that year; for instance, both 2009-02-18 and 2009-02-20 yield the week number 200908 - the 8th week of year 2009; and 2009-02-23 yields 200909 - the 9th week of year 2009.

I need to count how many different users (given by author id) contributed to some project, on a weekly basis.

I have setup a data pilot with the week as Row Field, the project as the Column Field, and count-author as the Data Field. However, this counts the author id as different instances. This is not what I need. I need to count how many different users contributed to each project on a weekly basis.

I expect to get something like:

week    Project1   Project2   Project3
200901        10          2          
200902         2                     7

Each inner cell containing how many different users contributed. With the count-author configuration, what I get is how many contributions (total) got the project on that week.

Is there a way to tell OpenOffice Calc to do what I want?

UPDATE: Following the suggestion given by other users, this question is now posted on Super User: https://superuser.com/questions/232339/openoffice-calc-how-can-i-count-the-number-of-different-items-with-data-pilot

  • 3
    This question may be better suited for Super User. – Sid Jan 11 '11 at 15:16
  • 1
    @iamsid: I agree, this is a question about OpenOffice, not Ubuntu. It's better suited over at SU. – Bobby Jan 11 '11 at 18:27
  • @iamsid and @Bobby Thanks for the suggestion, I didn't know about Super User. – manu Jan 12 '11 at 16:57
  • Please do not use masked shortened URL as requested in meta.askubuntu.com/questions/881/… Thanks in advance! – papukaija Feb 3 '11 at 2:20
  • I would appreciate if you drop a copy of the spreadsheet or an example data spreadsheet in order to analyze. I think it can be done with a procedure, not a formula. Thanks in advance. (Sorry if I don't answer to your Super User thread. I am not interested on Super User and this question seems to remain unanswered. Thank you for your comprehension. – Geppettvs D'Constanzo Feb 16 '11 at 19:02

Use Ctrl-Shift-Enter when entering it.


=SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0;MATCH(A1:A10;A1:A10;0);""); IF(LEN(A1:A10)>0;MATCH(A1:A10;A1:A10;0);""))>0;1))

where A1:A10 is the range being analyzed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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