Project Management Dashboards - Lookup/Count Text Values

[mrsjbw]

I have a table of projects that fall into four different "project type" categories - Column A. Then I have the actual project name itself in Column B. Some of the project names are almost identical (except for a course number). I'm trying to count how many different projects I have in each project type--BUT--I want to count the identically named projects as only one. I've created a helper column in which I've pulled the left four characters of the project name to be sure to capture the duplicates. I just can't seem to put the formulas together to pull the Project Type and how many unique projects are within. I don't want to use a filter because I'm trying to show this in a pie chart on a dashboard... am I overly complicating this? Missing something? Help

 

[kchiba]

Have a look at the lik below.


http://office.microsoft.com/en-us/excel-help/count-occurrences-of-values-or-unique-values-in-a-data-range-HP003056118.aspx

 

[mrsjbw]

Thank you for the help, I tried using this formula, but it doesn't give me all I need. Yes, I need to count unique/duplicate values, but I also need to return the name of the unique values and only 1 instance of the duplicate value... I'm thinking I need a lot of helper columns and a few different formulas?

 

[Hui]

How about posting your data and a description of what your after

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[mrsjbw]

Ok, I think I did this right... https://spreadsheets.google.com/spreadsheet/ccc?key=0AvAHPuOT-0hddE83NEJpVU1LdW9sRTliclJXTkNCQ1E&hl=en_US#gid=0 That's my link. I'm SURE I'm making this more complicated than it needs to be? I'm after MANY things as it turns out My boss would like a dashboard of the projects in our unit so that she can quickly determine a staff members capacity and effort towards a project (still working on this), a projects timeline as it relates to everything else in the unit (sort of figured this out), and a glance of how many different types of projects we're working on (sort of figured this out). What I'm really struggling with (among other things) is getting the gantt to work right. I've got several unique projects and several that she wants to consider as one... filtering those out and keeping the dates right is really challenging. I've got tons of helper columns and I think I'm complicating it?

 

[eibi]

Wait! Try to keep your question focused -- if you get onto too many different topics, it's hard to help. Feel free to post the gantt question and others as a separate thread.


Regarding your initial inquiry:


You want to count the quantity of projects that fall into each category, counting only the unique project names?


I suggest a pivot table. Click "Insert, Pivot Table" then select the data range you want to use. (Excel 2010)


In the PivotTable Field List on the right, grab the Column A (Category) title and drag it down into the Row Label box. Then, grab the Column B (Project Name) title and drag it down into the Row Label box.


No helper columns needed.

 

[Luke M]

Can you convince your boss to buy Microsoft Project? The Gantt charts would be a breeze then, and it has the resource tracking is sounds like you need. Can easily indent tasks and substasks as well.

 

[rogerimee]

A B

1 Roger =IF(COUNTIF(A$1:A1,A1)=1,1,0)

2 Imee =IF(COUNTIF(A$1:A2,A2)=1,1,0)

3 Ruth =IF(COUNTIF(A$1:A3,A3)=1,1,0)

4 Roger =IF(COUNTIF(A$1:A4,A4)=1,1,0)

5 Imee =IF(COUNTIF(A$1:A5,A5)=1,1,0)

6 Imee =IF(COUNTIF(A$1:A6,A6)=1,1,0)

7 Merly =IF(COUNTIF(A$1:A7,A7)=1,1,0)

8 Lala =IF(COUNTIF(A$1:A8,A8)=1,1,0)

9 Myla =IF(COUNTIF(A$1:A9,A9)=1,1,0)

10 Lala =IF(COUNTIF(A$1:A10,A10)=1,1,0)

11 1ST OPTION WITH COL HELPER =SUM(B1:B10)

12 2ND OPTION WITHOUT HELPER {=SUM(1/COUNTIF($A$1:$A$10,$A$1:$A$10))}

Enter with array fomula (Ctrl + Shift +Enter)


Please check the formula above using coutif, if and array formula... If you want the cell value replace the formula in if function. Instead of 1 change A1 (ex. =IF(COUNTIF(A$1:A1,A1)=1,A1,""))and instead of 0 replace it by blank("").