Count unique values based on matching criteria

Thanks - I really appreciate the help.

The reason I'm not using a pivot table is the first sheet has a number of other columns in it with other formulas and I need to present it in a tabular view - when I update the second sheet I want the formulas in the first to automatically update without needing to pivot it.

I think I described badly what I'm looking for so I apologize - I've attached a file that shows the fields I have. The 'sum of locations' column is where I want the formula to 'live'. It should compare the Account Number column adjacent to it to the Account Number column on sheet 2. Where it matches it should then count the number of different values in the 'Account Ship Location Number' column on the second sheet. If the same value appears there twice I only want it counted once, if that makes sense.

I've manually input the values I would expect to see in the 'Sum of Locations' column on the first sheet but this is where I'm struggling to find a formula to do the same thing.

I apologize for all the additional information - again - I'm really thankful for this assistance!