none
Count column formula RRS feed

  • Question

  • Hi,

    I hope this is the right forum.

    we have an internal projectnumber which is created from different columns, for example: If column D = "X" then column E = 100 else 1000. Furthermore we count a string and increase the number, for example: string "abc" count 5, our new project has to get a 6. We already have a formula in excel which count given string.

    I want to know if we can do it in project prof? I think that we have to use user defined fields with calculations but I did not find a count or countIf. Or can it be done with linking our excel column to a new project column? 

    Thanks for any input!

    Monday, July 23, 2012 10:18 AM

Answers

  • OK, can't be done in Project formula because each task can only access information about itself and cannot read any data from any other task, so the Countif is not possible.

    This can however be done in a VBA macro which can iterate through each task. This code isn't quick and easy as each unique value is going to be stored and counts kept as there isn't a Countif equivalent. It can be done, but not quick and easy (IE less than an hour).


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Marked as answer by sowahr Monday, July 30, 2012 9:37 AM
    Saturday, July 28, 2012 10:23 PM
    Moderator

All replies

  • Project does nowhere near the same number of functions as Excel. So if you have something complex to do for one cell I suggest copying it manually from Excel. You can paste link, but linked cells have a high file corruption risk so I don't recommend it.

    An alternative is a Project VBA macro to do what you want. Perhaps if you explain exactly what you need we can help. Note Project has fields (EG Number1 or Work etc. not Column D etc.).


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Monday, July 23, 2012 10:59 PM
    Moderator
  • Thanks Rod for your explanation!

    We have three columns (I think we have to use a user defined column for each of that in project)compounding our internal project number. Most important thing is this:

    Column 2: Look at column 3 if there is an "X" then use "100", if not then use "1000" (for data at column 2). Then count string at column 1 and add it (so you have e.g. 106 not only 100).Then put all three columns together: column1 & column2 & column3.

    Thanks for any input!

    Wednesday, July 25, 2012 7:01 AM
  • IIf( [Text1]="X", Val([Text2])+100 , Val([Text2])+1000) should work, but not sure what you mean by put all three columns together. Is this a straight string append after 106?

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Thursday, July 26, 2012 9:44 AM
    Moderator
  • Hi Rod,

    thanks for this!

    In the end we have a project number like e.g. 123451004X: column 1 is 12345, column 2 is 1004 and column 3 is X. Column 2 is a consecutive number in relation of column 1. So I have to count all 12345 in column 1 to get my number for column 2.

    Thursday, July 26, 2012 12:34 PM
  • Sorry, I still don't see clearly what you mean. "count all 12345 in column 1" doesn't mean anything obvious. How does 12345 become 1004? Please provide an exact example and rather than use column1, please use Text1, text2, text3 as that makes it more relevant.

    What's the formual in Excel to "count the string"?


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Thursday, July 26, 2012 8:56 PM
    Moderator
  •  "count all 12345 in column 1" doesn't mean anything obvious. How does 12345 become 1004?

    12345 doesn`t become 1004. 1004 shows me that I have number/string "12345" four times now (including the current one) - a consecutive number - and that in column 3 isn`t a "X" but a "Z" (so we get 1000 and not 100 as basis).

    The formular in excel is like this:

    IF(K34="X";TEXT(COUNTIF(B$3:B34;B34)-1;"1000");TEXT(COUNTIF(B$3:B34;B34)-1;"100"))

    I hope it is more clearly now. Thanks for your time and patience!



    • Edited by sowahr Friday, July 27, 2012 9:12 AM
    Friday, July 27, 2012 9:11 AM
  • OK, can't be done in Project formula because each task can only access information about itself and cannot read any data from any other task, so the Countif is not possible.

    This can however be done in a VBA macro which can iterate through each task. This code isn't quick and easy as each unique value is going to be stored and counts kept as there isn't a Countif equivalent. It can be done, but not quick and easy (IE less than an hour).


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Marked as answer by sowahr Monday, July 30, 2012 9:37 AM
    Saturday, July 28, 2012 10:23 PM
    Moderator
  • Thanks for that explanation!

    I will report to my collegues and discuss if we transfer that to project or keep it at excel. Thanks.

    Monday, July 30, 2012 9:37 AM