Hi everyone,
I am looking for a formula and face some difficulties...
I use excel as a project management table. The table lists projects by row with information about them in columns (budget, location, status, etc..).
Let have an example:
Column A : Project Name (text)
Column B : Sector (list: X, Y, Z)
Column C : Location (list: A, B, C)
Column D : Budget 2010
Column E : Budget 2011
Column F : Budget 2012
...
Note that the number of rows is unknown. Project list starts in A2.
I try to edit a adaptable formula to count the number of distinct projects with several criteria. Seems easy, but one of my problem comes from the fact that some projects are disaggregated into several rows because of multiple locations or sub-sectors, etc.. Then, same project would be count several times with a classical SUMPRODUCT function..
For example, I want to know the number of distinct ongoing projects in "2011" in "Location A" and "sector X".
I had the initial idea to discount the number of recurrent projects (=same name in column A) from the formula:
=SUMPRODUCT(($E$2:$E$2000>0)*($B$2:$B$2000="X"))*($C$2:$C$2000="A"))
Is it possible ?
Please note that the formula have to be run dozen of times (for each sector, each year, each location), so the lightest way to achieve such a result is welcome, even with help columns... By the way, if that can help, I succeed to create an extra column which gives a unique ID for each project.
Thank you very much in advance, any help would be much appreciated !!
Martin
Bookmarks