Get Count of Unique Items Within A Designated Area

[SanFelippo]

So the easiest way to explain what I am trying to do is with an example. See the sample Data below:

Investment <tbody> </tbody>	Current/Prior <tbody> </tbody>	Routine/NonRoutine <tbody> </tbody>	AA <tbody> </tbody>
FHLMC GOLD PL V84191 <tbody> </tbody>	Current <tbody> </tbody>	Routine <tbody> </tbody>	Chicago <tbody> </tbody>
FHLMC GOLD PL V84191 <tbody> </tbody>	Current <tbody> </tbody>	Routine <tbody> </tbody>	Chicago <tbody> </tbody>
FHLMC GOLD PL V84191 <tbody> </tbody>	Prior <tbody> </tbody>	Routine <tbody> </tbody>	Chicago <tbody> </tbody>
FHLMC GOLD PL V84191 <tbody> </tbody>	Prior <tbody> </tbody>	NonRoutine <tbody> </tbody>	Chicago <tbody> </tbody>
FHLMC GOLD PL V84191 <tbody> </tbody>	Current <tbody> </tbody>	NonRoutine <tbody> </tbody>	Chicago <tbody> </tbody>
FHLMC GOLD PL V84191 <tbody> </tbody>	Current <tbody> </tbody>	Routine <tbody> </tbody>	Elgin <tbody> </tbody>
FHLMC GOLD PL V84191 <tbody> </tbody>	Prior <tbody> </tbody>	NonRoutine <tbody> </tbody>	Elgin <tbody> </tbody>
FHLMC GOLD PL V84191 <tbody> </tbody>	Current <tbody> </tbody>	Routine <tbody> </tbody>	Chicago <tbody> </tbody>
FHLMC GOLD PL V84192 <tbody> </tbody>	Current <tbody> </tbody>	NonRoutine <tbody> </tbody>	Chicago <tbody> </tbody>
FHLMC GOLD PL V84192 <tbody> </tbody>	Current <tbody> </tbody>	NonRoutine <tbody> </tbody>	Peoria <tbody> </tbody>
FHLMC GOLD PL V84192 <tbody> </tbody>	Prior <tbody> </tbody>	NonRoutine <tbody> </tbody>	Peoria <tbody> </tbody>
FHLMC GOLD PL V84192 <tbody> </tbody>	Prior <tbody> </tbody>	NonRoutine <tbody> </tbody>	Chicago <tbody> </tbody>
FHLMC GOLD PL V84192 <tbody> </tbody>	Prior <tbody> </tbody>	Routine <tbody> </tbody>	Kenosha <tbody> </tbody>
FHLMC GOLD PL V84192 <tbody> </tbody>	Prior <tbody> </tbody>	NonRoutine <tbody> </tbody>	Chicago <tbody> </tbody>
FHLMC GOLD PL V84192 <tbody> </tbody>	Prior <tbody> </tbody>	Routine <tbody> </tbody>	Kenosha <tbody> </tbody>
FHLMC GOLD PL V84192 <tbody> </tbody>	Current <tbody> </tbody>	NonRoutine <tbody> </tbody>	Chicago <tbody> </tbody>
FHLMC GOLD PL V84193 <tbody> </tbody>	Prior <tbody> </tbody>	Routine <tbody> </tbody>	Kenosha <tbody> </tbody>
FHLMC GOLD PL V84193 <tbody> </tbody>	Current <tbody> </tbody>	NonRoutine <tbody> </tbody>	Peoria <tbody> </tbody>
FHLMC GOLD PL V84193 <tbody> </tbody>	Prior <tbody> </tbody>	Routine <tbody> </tbody>	Chicago <tbody> </tbody>
FHLMC GOLD PL V84193 <tbody> </tbody>	Prior <tbody> </tbody>	NonRoutine <tbody> </tbody>	Kenosha <tbody> </tbody>
FHLMC GOLD PL V84193 <tbody> </tbody>	Prior <tbody> </tbody>	Routine <tbody> </tbody>	Chicago <tbody> </tbody>
FHLMC GOLD PL V84193 <tbody> </tbody>	Prior <tbody> </tbody>	NonRoutine <tbody> </tbody>	Kenosha <tbody> </tbody>
FHLMC GOLD PL V84193 <tbody> </tbody>	Prior <tbody> </tbody>	NonRoutine <tbody> </tbody>	Peoria <tbody> </tbody>

<tbody>
</tbody>

What I need is a formula that will count up the number of unique Investment types that occur in each AA based on a few other data points. For example, if I wanted to find total number of unique investments that occurred in Chicago, Elgin, Peoria, and Kenosha that are also Prior and NonRoutine, the formula I need would spit out these results:

AA	Unique Count
Chicago <tbody> </tbody>	3
Elgin <tbody> </tbody>	1
Peoria <tbody> </tbody>	2
Kenosha <tbody> </tbody>	2

<tbody>
</tbody>

It would return these results because in Chicago, there 12 overall investments designated to that area, but there are really only 3 unique investments designated to that area (see the Investment Name).

I have a massive list with a bunch of different Investments, so going through manually to figure this out is pretty impossible.

I know there is a way to do this, probably with using the frequency function, but haven't figured it out yet. If anyone has any idea, that would be great. I'll keep working at it myself as well.

Thanks

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:223.37px;" /><col style="width:137.82px;" /><col style="width:190.1px;" /><col style="width:108.36px;" /><col style="width:76.04px;" /><col style="width:80.79px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Investment</td><td >Current/Prior</td><td >Routine/NonRoutine</td><td >AA</td><td > </td><td >AA</td><td >Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >FHLMC GOLD PL V84191</td><td >Current</td><td >Routine</td><td >Chicago</td><td > </td><td >Chicago</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >FHLMC GOLD PL V84191</td><td >Current</td><td >Routine</td><td >Chicago</td><td > </td><td >Elgin</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >FHLMC GOLD PL V84191</td><td >Prior</td><td >Routine</td><td >Chicago</td><td > </td><td >Peoria</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >FHLMC GOLD PL V84191</td><td >Prior</td><td >NonRoutine</td><td >Chicago</td><td > </td><td >Kenosha</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >FHLMC GOLD PL V84191</td><td >Current</td><td >NonRoutine</td><td >Chicago</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >FHLMC GOLD PL V84191</td><td >Current</td><td >Routine</td><td >Elgin</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >FHLMC GOLD PL V84191</td><td >Prior</td><td >NonRoutine</td><td >Elgin</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >FHLMC GOLD PL V84191</td><td >Current</td><td >Routine</td><td >Chicago</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >FHLMC GOLD PL V84192</td><td >Current</td><td >NonRoutine</td><td >Chicago</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >FHLMC GOLD PL V84192</td><td >Current</td><td >NonRoutine</td><td >Peoria</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >FHLMC GOLD PL V84192</td><td >Prior</td><td >NonRoutine</td><td >Peoria</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >FHLMC GOLD PL V84192</td><td >Prior</td><td >NonRoutine</td><td >Chicago</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >FHLMC GOLD PL V84192</td><td >Prior</td><td >Routine</td><td >Kenosha</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >FHLMC GOLD PL V84192</td><td >Prior</td><td >NonRoutine</td><td >Chicago</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >FHLMC GOLD PL V84192</td><td >Prior</td><td >Routine</td><td >Kenosha</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >FHLMC GOLD PL V84192</td><td >Current</td><td >NonRoutine</td><td >Chicago</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >FHLMC GOLD PL V84193</td><td >Prior</td><td >Routine</td><td >Kenosha</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >FHLMC GOLD PL V84193</td><td >Current</td><td >NonRoutine</td><td >Peoria</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >FHLMC GOLD PL V84193</td><td >Prior</td><td >Routine</td><td >Chicago</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >FHLMC GOLD PL V84193</td><td >Prior</td><td >NonRoutine</td><td >Kenosha</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >FHLMC GOLD PL V84193</td><td >Prior</td><td >Routine</td><td >Chicago</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >FHLMC GOLD PL V84193</td><td >Prior</td><td >NonRoutine</td><td >Kenosha</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >FHLMC GOLD PL V84193</td><td >Prior</td><td >NonRoutine</td><td >Peoria</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G2</td><td >=COUNTIFS($D$2:$D$24,F2,$B$2:$B$24,"Prior",$C$2:$C$24,"NonRoutine")</td></tr></table></td></tr></table>

 

[SanFelippo]

i messed up that bottom table, the counts the formula I need to have should be returning for Prior and NonRoutine:

AA	Unique Count
Chicago	2
Elgin	1
Peoria	2
Kenosha	1

<tbody>
</tbody>

Do you see what I mean by a "unique count?" The same Investment can be allocated to different areas (even allocated more than once to the same area).

 

[SanFelippo]

I thank I may have figured it out actually. this is the formula I came up with and it appears to be working.

{=SUM(--(FREQUENCY(IF(($D$2:$D$24=$H2)*($C$2:$C$24="NonRoutine")*($B$2:$B$24="Prior"),MATCH($A$2:$A$24,$A$2:$A$24,0)),ROW($A$2:$A$24)-ROW(A2)+1)>0))}

 

[DanteAmor]

This is another (longer) option, but with a "regular" formula.

=SUMPRODUCT(($D$2:$D$24=F2)*($B$2:$B$24="Prior")*($C$2:$C$24="NonRoutine")*(MATCH($A$2:$A$24&$B$2:$B$24&$C$2:$C$24&$D$2:$D$24,$A$2:$A$24&$B$2:$B$24&$C$2:$C$24&$D$2:$D$24,0)=ROW($B$2:$B$24)-1))

 

[SanFelippo]

This is another (longer) option, but with a "regular" formula.

=SUMPRODUCT(($D$2:$D$24=F2)*($B$2:$B$24="Prior")*($C$2:$C$24="NonRoutine")*(MATCH($A$2:$A$24&$B$2:$B$24&$C$2:$C$24&$D$2:$D$24,$A$2:$A$24&$B$2:$B$24&$C$2:$C$24&$D$2:$D$24,0)=ROW($B$2:$B$24)-1))

Is it possible to put an inequality statement in as well? For example, instead of saying =F2, if we pretend F2 were a numeric field, is it possible to have it look for things that are >=F2? How would you put that into the formula?

This isn't directly related to this specific data set, but a very very similar formula needs to be used to look for things that are >= a certain number. This is the formula below. It seems to be ignoring the >= piece all together.

=IF($B$1=2018,SUMPRODUCT(('2018 Prior Investments'!$X$2:$X$500='Tier 1 Cap & Annual Deposits '!$A12)*('2018 Prior Investments'!$D$2:$D$500="Prior")*('2018 Prior Investments'!$E$2:$E$500="NonRoutine")*('2018 Prior Investments'!$Y$2:$Y$500[U][I][B]>=B$1[/B][/I][/U])*(MATCH('2018 Prior Investments'!$A$2:$A$500&'2018 Prior Investments'!$D$2:$D$500&'2018 Prior Investments'!$E$2:$E$500&'2018 Prior Investments'!$X$2:$X$500&'2018 Prior Investments'!$Y$2:$Y$500,'2018 Prior Investments'!$A$2:$A$500&'2018 Prior Investments'!$D$2:$D$500&'2018 Prior Investments'!$E$2:$E$500&'2018 Prior Investments'!$X$2:$X$500&'2018 Prior Investments'!$Y$2:$Y$500,0)=ROW('2018 Prior Investments'!$A$2:$A$500)-1)))

 

[DanteAmor]

Test:
=SUMPRODUCT(($D$2:$D$24>=F2) ...