Duplicate Formula with Multiple Data Cells

[Wayde Morrison]

I have a spreadsheet with multiple duplicates which I need to sort using multiple cells. In the attached spreadsheet I need to count column B input without counting the duplicates (only count same cell info once) then count cell A to correspond with the results of cell B.

How many different places did 10- go to without duplicating the places?

How many different places did 30- go to without duplicating the places?

How many different places did 80- go to without duplicating the places?

I used the following formula to determine how many individual area's were attended without duplicating the information:

{=SUM(IF(FREQUENCY(IF(B80:B390<>"",MATCH(B80:B390,B80:B390,0)),ROW(B80:B390)-ROW(B80)+1),1))}

But I don't know how to break it down further to see how many of those places 10-, 30-, and 80- were at individually?

[#2 · Accepted solution]

I'm a lazy person and don't want to think about complex formulas.

So, insert a helper column with: LEFT(A1,3)

Insert a row above the data and name the columns. My names are: AA, Place and AA-Group.

Insert a pivot table.

Check the option "Add to the data model" in the pivot table dialogue box.

"AA-Group" into rows area. "Place" into values area. Change the summarize function to "Distinct count".

And you're done.

[#3]

This is exactly what I needed. The information was easy to extract without a complicated formula. Thank you for your assistance.