Count unique values based on matching criteria

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