Count unique pos by theater without using pivots

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:22 PM GMT

Question

hope all is well! I just want to count the unique customer POs from the 'Raw Data' tab by theater and show the values in the 'Summary' tab without using pivots. 

Thanks in advance for your help!

Summary Tab Theater Count of Unique Customer PO: US & Canada 4 Europe 2 APJ 3

Raw Data tab: Theater Customer PO Number COLUMN A Column B US & Canada 123 Europe 222 APJ 456 US & Canada 123 Europe 222 APJ 546 US & Canada 111 Europe 222 APJ 456 US & Canada 158 Europe 132 US & Canada 143 Europe 132 APJ 678 Europe 132



Answer

Assume on your sheet named RawData, the data you list begins with e.g. in A2:A4

US & Cananda Europe APJ [etc]

and B2:B4 is

123 222 456 [etc]

On summary sheet, A2:A4 is

US & Cananda Europe APJ

On RawData cell C2 enter

=A2&B2

On RawData cell D2 enter

=IF(COUNTIF(C$1:C2,C2)=1,A2,"")

copy C2:D2 down through C2:D16.

On Summary sheet, cell B2, enter

=COUNTIF(RawData!$D$2:$D$16,A2)

and copy down through B2:B4

Advertisement

©2021 eLuminary LLC. All rights reserved.