Get Count of Unique Items Within A Designated Area

SanFelippo

Board Regular
Joined
Apr 4, 2017
Messages
119
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:

AAUnique 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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,178
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Apr 4, 2017
Messages
119
i messed up that bottom table, the counts the formula I need to have should be returning for Prior and NonRoutine:

AAUnique Count
Chicago2
Elgin1
Peoria2
Kenosha1

<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

Board Regular
Joined
Apr 4, 2017
Messages
119
I thank I may have figured it out actually. this is the formula I came up with and it appears to be working.

Code:
{=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

Well-known Member
Joined
Dec 3, 2018
Messages
11,178
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Apr 4, 2017
Messages
119
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.

Code:
=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

Well-known Member
Joined
Dec 3, 2018
Messages
11,178
Office Version
2007
Platform
Windows
Test:
=SUMPRODUCT(($D$2:$D$24>=F2) ...
 

Watch MrExcel Video

Forum statistics

Threads
1,095,748
Messages
5,446,261
Members
405,392
Latest member
Steveoaktree1977

This Week's Hot Topics

Top