Return count of Postal Code based unique value

Hey Excel Pro's

Excel 2010

In column A I have a list of account numbers.

In column B I have a list of Postal codes.

Account numbers could repeat in column A and because of this the postal code could repeat in column B. But a different account could have the same postal code as another account.

I need to count the number of times a postal code shows up based on unique account number.

Sorry should have mentioned excel 2010.

Piviot table returns the number of times the postal code actually shows up.

You must have used 2013+?

Did not think you could do that in 2010?

This is a formula way.

=SUMPRODUCT((D3=$B$2:$B$7)/COUNTIFS($A$2:$A$7,$A$2:$A$7))

Ex 2k16
And you are right on Ex2k10 you can't do that with PT because there is no Data Model
so use Dave's solution

edit: Update your profile about Office version. Will be easier for us and you for the future

Yes. I forgot to mention that part.

If this formula is sluggish on the real data let me know. There are faster (but longer) formulas to do this.

FlameRetired

Just noticed your formula points to D3.

D3 is the sample output I was looking for. Real application would count postal codes in column B based of unique account number in A.

Is there a tweak on that formula you could apply.

Any chance you could plug into my test work book and attach of you have the time.

Thanks.

I suspect I have over complicated this, but I can not seem to come up with a simpler formula.

This uses an array formula in D3. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

=INDEX($B$2:$B$7,SMALL(IF(FREQUENCY(IF(FREQUENCY(MATCH($A$2:$A$7,$A$2:$A$7,0),ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1),MATCH($B$2:$B$7,$B$2:$B$7,0)),MATCH($B$2:$B$7,$B$2:$B$7,0)),ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1),ROWS($3:3)))

The more I look at this the more it appears all you need is unique postal codes. Unless the same account number can have multiple postal codes this should simplify things a bit.

Use a helper column. This formula would go in C2 and filled down.

Formula:

=IF(B2="","",IF(COUNTIF($B$2:B2,B2)=1,ROWS($2:2),""))

Then this formula goes in D3 and filled down until you get blanks.

Formula:

=IFERROR(INDEX($B$2:$B$7,SMALL(C:C,ROWS($3:3))),"")

You might also consider a pivot table. There are no calculations, and they are resource light.

Tested the helper formula does not return count of 2 & 1.

In column c it shows value of 1 and 4?

And in column D it shows the 1 of each postal code but because there is another account with the same postal code it should be duplicated again.

Am i entering it wrong?

Tested the index option only returns the postal code as 1 value but one of them should be repeated.

The reason one should be repeated is because there is another account same postal code which can and will happen. And the postal code will be repeated for some accounts but only want to count once when that happens.

End result I need to count postal code once per account number.

Piviot table option...

Excel 2013+ has data model option built in but we have 2010.

Any other options to get this count in 2010?

Maybe it will help:
try
1. copy postal values into another column
2. select this range
3. from the ribbon: DATA >>> DATA TOOLS >>> Remove Duplicates
4. in the next column use formula:
=SUM(IF(FREQUENCY(IF($A$2:$A$7<>"",MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW($A$2:$A$7)-ROW($A$2)+1)>0,--($B$2:$B$7=H2))) with CSE & drag down

OR

(with your example)
in D2: =IFERROR(INDEX($B$2:$B$7,MATCH(0,COUNTIF($D$1:D1,$B$2:$B$7),0)),"") array entered and drag down
in E2: =IF(D2="","",SUM(IF(FREQUENCY(IF($A$2:$A$7<>"",MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW($A$2:$A$7)-ROW($A$2)+1)>0,--($B$2:$B$7=D2)))) array entered and drag down

CSE or array entered = ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Using column range is not a good idea. Much better to use is defined range (it can be B2:B100000, but defined, if data exist in this range ).
In this case if you try B:B you will get #N/A

note: if you change B range to 10000 you should change all ranges to 10000 also

Perhaps I need to convince my IT to upgrade to 2013 +

very good idea (or 365 plus - subscription)

You've 1,048,576 rows to define ranges

In previous attempts I seem to have had difficulty interpreting the mission.

With that much data would you be willing to reconsider helper columns to lighten the load?

In the attached column C returns postal codes where the pairings are unique. The formula is

Formula:

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,B2,"")

In helper column D my previous formula that returns row numbers for first occurrences of postal codes is

Formula:

=IF(COUNTIF($B$2:B2,B2)=1,ROWS($2:2),"")

Then final output of unique postal codes in column E would be

Formula:

=IFERROR(INDEX($B$2:$B$35000,SMALL($D$2:$D$35000,ROWS($2:2))),"")

and the counts in column F

Formula:

=IF(E2="","",COUNTIF($C$2:$C$35000,E2))

	A	B	C	D	E	F
1	account number	postal code	helper 1	helper 2
2	12345	E2J 4G3	E2J 4G3	1	E2J 4G3	2
3	12345	E2J 4G3			S2F 5T5	1
4	12345	E2J 4G3
5	98756	S2F 5T5	S2F 5T5	4
6	98756	S2F 5T5
7	65467	E2J 4G3	E2J 4G3