Conditional count of unique values

[Amy]

Using v2003

I'll explain as clearly as possible...

I need a formula that will do a count of unique values in a column based on
the condition of two other columns.

i.e. Col A is financial entity id and has duplicates: there may be 5 line
items in entity 556 and 3 line items in entity 444, 5 line items 768, etc.

Col B has the project impact: one of two values, either "Investment" or "ETC"

Col C has the market ID: NE, SE, NW, SW

I need a formula to return the count of unique financial entities that are
Investment in the SE. For example, if the conditions apply to four of the
five 556 entities, the formula would return "1".

Thanks in advance for any guidance! Hope this is enough info.

 

[Amy]

BTW, I've been trying to use an array formula that looks something like this,
but obviously with no luck...

{=COUNT(IF($B$2:$B$1511="Investment",IF($C$2:$C$1511="NE",SUM(IF(FREQUENCY(IF(LEN(A2:A1511)>0,MATCH(A2:A1511,A2:A1511,0),""),
IF(LEN(A2:A1511)>0,MATCH(A2:A1511,A2:A1511,0),""))>0,1)),0),0))}

 

[Jacob Skaria]

Try the below with your data in the below format. Please note that this is an
array formula. Within the cell in edit mode (F2) paste this formula and press
Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you
can notice the curly braces at both ends like "{=<formula>}"

=SUM(IF(FREQUENCY(IF(B$2:B$100="Investment",IF(C$2:C$100="SE",A$2:A$100)),A$2:A$100),1))

Col A Col B Col C
556 Investment NE
556 Investment SE
556 Investment NW
556 Investment SW
556 ETC NE
444 ETC SE
444 ETC NW
444 ETC SW
768 ETC NE
768 Investment SE
768 ETC NW
768 ETC SW
768 ETC NE


If this post helps click Yes

 

[Amy]

Mental head-slap for making life too complicated! Thanks!!

 

[Amy]

Where would I insert another IF condition? Add column D to your example below
with Area ID in it, each cell with one of three values: Power, Antenna or
Building

Thanks!

 

[Jacob Skaria]

Try
=SUM(IF(FREQUENCY(IF(B$2:B$100="Investment",IF(C$2:C$100="SE",IF(D$2$100="Power",A$2:A$100))),A$2:A$100),1))

If this post helps click Yes