Formula to COUNTA distinct values with multiple criteria

**f0urchette** · 02-12-2012, 05:42 PM

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

**Haseeb A** · 02-12-2012, 06:12 PM

Try this,

=SUMPRODUCT(--(E$2:E$2000>0),--(B$2:B$2000="X"),--(C$2:C$2000="A"),--(A$2:A$2000<>""),--(MATCH(A$2:A$2000&"",A$2:A$2000&"",0)=(ROW(A$2:A$2000)-ROW(A$2)+1)))

**f0urchette** · 02-13-2012, 03:41 AM

Hi Haseeb,

Thank you for this fast answer.

I am quite upset. Indeed, your formula works perfectly for the sector calculation, ie:

=SUMPRODUCT(--(E$2:E$2000>0),--(B$2:B$2000="X"),--(A$2:A$2000<>""),--(MATCH(A$2:A$2000&"",A$2:A$2000&"",0)=(ROW(A$2:A$2000)-ROW(A$2)+1)))

But then, for the location one, the number is always underestimated. I don't know why…

=SUMPRODUCT(--(E$2:E$2000>0),--(C$2:C$2000="A"),--(A$2:A$2000<>""),--(MATCH(A$2:A$2000&"",A$2:A$2000&"",0)=(ROW(A$2:A$2000)-ROW(A$2)+1)))

I checked again and again, I don't manage to see what happens… For example, in one location, let say B, all the projects are also implemented in another location, and the formula returns 0, although they are 4 distinct projects in 5 different rows when I filter by this location.

It seems weird to me… and I can hardly understand what happen as I do not fully understand the function you gave. What is the meaning of &"" in the MATCH function ?

If you have an idea about what goes wrong…
Thank you again Haseeb,
Martin

**Haseeb A** · 02-14-2012, 06:56 PM

Probably "A" may have trailing or leading spaces, a NULL or CHAR(10) character.

Try this, select C2:C2000

Go to, Data | text to Columns

Select Delimited, click Finish
Again, text to columns
select Fixed Width, then click finish.

If there is any CHAR(160) character with "A" it doesn't work

Can you please attach a sample file?

**f0urchette** · 02-20-2012, 03:54 AM

Hi Haseeb,

Thank you again. In fact, one of my problem is also that the formula is too big. As I said, my database is quite big - which is another problem, and the formula make the database very slow to manipulate when I change my combobox selections linked to interactive tables with the fomula and other stuffs. So I decided to use your formula with only one criteria, which works well. Furthermore, I realize that every user has a specific requirement (ongoing or finished, location A + B, subsector X but... etc). As I can't edit a formula for each requirement, they will have to count by themselves.

However, I will try your trick to let us know if it is working.

Thank you again for your time Haseeb,
Martin