Count column formula
-
lundi 23 juillet 2012 10:18
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!
Toutes les réponses
-
lundi 23 juillet 2012 22:59Modérateur
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
-
mercredi 25 juillet 2012 07:01
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!
-
jeudi 26 juillet 2012 09:44ModérateurIIf( [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
-
jeudi 26 juillet 2012 12:34
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.
-
jeudi 26 juillet 2012 20:56Modérateur
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
-
vendredi 27 juillet 2012 09:11
"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!
- Modifié sowahr vendredi 27 juillet 2012 09:12
-
samedi 28 juillet 2012 22:23Modérateur
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
- Marqué comme réponse sowahr lundi 30 juillet 2012 09:37
-
lundi 30 juillet 2012 09:37
Thanks for that explanation!
I will report to my collegues and discuss if we transfer that to project or keep it at excel. Thanks.
