Count unique values based on matching criteria

**gromitw** · 01-09-2007, 09:22 PM

Hi

I'm stuck on a formula and would really appreciate some help.

I have a worksheet with two tabs.

First Tab
Account Name
Account Number

Second Tab
Account Name
Account Number
Account Ship Location Number

On the first sheet each account name and number only appears once.

On the second sheet there may be multiple entries for each Account Name and Number.

On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.

I don't know if this requires a frequency formula or a countif or... and I'm really confused.

Thanks

**MSP77079** · 01-09-2007, 10:01 PM

It sounded like a pretty simple COUNTIF formula until you used that word "unique".

I know how to do that in VBA, but I would have to think about how to do it with a formula.

------------- Added Later -----------------

I went to Excel help and searched on COUNTIF unique. Excel help's suggest was to use a pivot table. I should have thought of that.

**Bryan Hessey** · 01-09-2007, 10:01 PM

Originally Posted by gromitw

Hi

I'm stuck on a formula and would really appreciate some help.

I have a worksheet with two tabs.

First Tab
Account Name
Account Number

Second Tab
Account Name
Account Number
Account Ship Location Number

On the first sheet each account name and number only appears once.

On the second sheet there may be multiple entries for each Account Name and Number.

On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.

I don't know if this requires a frequency formula or a countif or... and I'm really confused.

Thanks

Hi,

with a helper column in Sheet2,

=IF(SUMPRODUCT(--(A$2:A2=A2)*(--(B$2:B2=B2)*(--(C$2:C2=C2))))>1,"",1)

and a count in Sheet1

=SUMPRODUCT(--(Sheet2!A$2:A$20=A2)*(Sheet2!B$2:B$20=B2)*(--(Sheet2!E$2:E$20=1)))

as per the attached.

the range :20 will need to be amended to the extent of your data (or use 65000 etc)

hth
---
amended

**gromitw** · 01-09-2007, 10:31 PM

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!

**MSP77079** · 01-09-2007, 10:57 PM

Well, that's pretty much what I thought you had in mind.

Attached is the only way that I know how to do it. See column D.

**MSP77079** · 01-09-2007, 11:02 PM

I was a little too quick on the draw there. Try this one instead.

V2 would return 0 if it encountered an error, this one returns #VALUE if it encounters an error.

**MSP77079** · 01-09-2007, 11:40 PM

I decided to add more error trapping. And also, to make it more like a "normal" Excel function, so you can enter the account number directly into the formula if you really want to (the earlier versions required that you pass a cell reference; so, passing an account number directly gave it fits).

If you haven't noticed already, the length of the range you tell it to search in for customer numbers must exactly match the length of the range you tell it to look in for unique ship-to location numbers. Otherwise, it returns a #REF error to the cell.

If it ever returns an #NA error to the cell, it means you have discovered a way to mess it up that I did not think of. I would like to learn from that if it ever happens.

**gromitw** · 01-10-2007, 12:44 AM

That works - thank you it's great! OK I am going to ask a really stupid question - how did you do it? I can see the formula but not how you created it. I ask both for my own education and so I can transfer it to the real working sheet!

Edited to add... I mean how do I save it so that I can use it in other workbooks - I can see it's VBA, I just don't know enough to make it work within another sheet....

**gromitw** · 01-10-2007, 01:08 AM

OK - scratch that - I got it working and discovered the Developer ribbon for Excel at the same time. Thank you all for your help and the education!

**MSP77079** · 01-10-2007, 08:59 AM

Glad to help. Now you can do a favor for me, perhaps. When you mention "ribbon" I immediately think you must be using Excel 2007, which I have not seen yet.

Can you explain how to find

the Developer ribbon for Excel

Any desription at all would be appreciated.