Count Non-Duplicates based on two criteria

[DeusXv]

Hey Guys,

Have the following data set (see below) and I am looking to count non-duplicates based on two sets of criteria.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]MDU[/TD]
[TD]Status[/TD]
[TD]Internal/External[/TD]
[TD]Premises[/TD]
[/TR]
[TR]
[TD]Saint Annes Court[/TD]
[TD]Complete[/TD]
[TD]Intneral[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Seatown Place[/TD]
[TD]Complete[/TD]
[TD]External[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Seatown Place[/TD]
[TD]Complete[/TD]
[TD]External[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Seatown Place[/TD]
[TD]Complete[/TD]
[TD]External[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Setanta House[/TD]
[TD]Complete[/TD]
[TD]Internal[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Shorts Court[/TD]
[TD]Complete[/TD]
[TD]Internal[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

To count non-duplicates based on one set of criteria I am using the following equation:

[INDENT]{=SUMPRODUCT(IF(FREQUENCY(MATCH($A$2:$A$7,$A$2:$A$7,0),MATCH($A$2:$A$7,$A$2:$A$7,0))>0,1,0), IF($B$2:$B$8="Complete", 1, 0))}[/INDENT]

But now lets say I want to count non-duplicate MDU's based on two sets of Criteria, Complete and Internal. I tried the following equation:

[INDENT]{=SUMPRODUCT(IF(FREQUENCY(MATCH($A$2:$A$7,$A$2:$A$7,0),MATCH($A$2:$A$7,$A$2:$A$7,0))>0,1,0), IF(AND($B$2:$B$8="Complete",$C$2:$C$8="Internal"), 1, 0))}[/INDENT]

But this only returns #VALUE ! - Anyone have any idea what I am doing wrong with the equation above, or if there is an easier equation I can use to achieve this?

Thanks in advance,

Patrick (DeusXv)

 

[bsquad]

this is an array

go nuts

=IF(ROWS(A$2:A3)>SUMPRODUCT(--($B$2:$B$8="Complete"),--($C$2:$C$8="Internal")),"",INDEX($A$2:$A$8,SMALL(IF(($B$2:$B$8="Complete")*($C$2:$C$8="Internal"),ROW($A$2:$A$8)-ROW($A$2)+1),ROWS(A$2:A3))))

 

[DeusXv]

this is an array

go nuts

=IF(ROWS(A$2:A3)>SUMPRODUCT(--($B$2:$B$8="Complete"),--($C$2:$C$8="Internal")),"",INDEX($A$2:$A$8,SMALL(IF(($B$2:$B$8="Complete")*($C$2:$C$8="Internal"),ROW($A$2:$A$8)-ROW($A$2)+1),ROWS(A$2:A3))))

Hi Bsquad - I tried this, but it just returned the Name "Seatown Place" - I am looking for it to return a number, so for the above list it would return 3, as 3 of the places meet the Complete Criteria and the Internal Criteria.

 

[barry houdini]

Try this version, array entered

=SUM(IF(FREQUENCY(IF(($B$2:$B$8="Complete")*($C$2:$C$8="Internal"),MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW($A$2:$A$7)-ROW($A$2)+1),1))

 

[bsquad]

alright so if its just a count use this -

=SUMPRODUCT(--($B$3:$B$8="Complete"),--($C$3:$C$8="Internal"))

however, like you found out, that formula I posted doesn't return a count, it pulls distinct based on your two conditions; so you would have to drag it down. That formula is dynamic, so you could drag it down 10 cells and it will pull the 3 MDU's you are looking for and if you happen to add another distinct MDU it will add to the list. The formula in this post for the count will only pull 2 because you have Internal spelled wrong for Saint Annes Court

 

[barry houdini]

The thread title is "Count Non-Duplicates......" so I'm assuming it isn't just a count. My suggested formula counts non-duplicates in column A where the other 2 criteria are met - unfortunately on the example shown there are no duplicates in column A where the criteria are both met......but if you test with different data you should see that it works.

{=SUMPRODUCT(IF(FREQUENCY(MATCH($A$2:$A$7,$A$2:$A$7,0),MATCH($A$2:$A$7,$A$2:$A$7,0))>0,1,0), IF($B$2:$B$8="Complete", 1, 0))}

Note that this doesn't work to count different values where the criterion is met. Again, it's difficult to tell here because that formula returns 4 based on your test data.....but it only works co-incidentally in that case - if you delete "Complete" in B3 you should still get 4....but the formula actually returns 3.

For the formula to work correctly the MATCH function inside FREQUENCY needs to be dependent on the conditions - you can't put it outside like the above