countifs for unique values

Hello, I'm able to count # of stores assigned to a string of managers, but also want to count # unique stores with a certain billing criteria (therefore not having duplicates).

For example, FRANCISCO CASTILLERO reports to MARC SCHWIESOW who reports to PAULO DIAZ. He has 11 stores total.

=COUNTIFS('SHC EL 1-1 thru 7-24-14'!$D:$D,Summary!$A63,'SHC EL 1-1 thru 7-24-14'!$E:$E,Summary!$B63,'SHC EL 1-1 thru 7-24-14'!$G:$G,Summary!$C63) = 15, but I want to not include duplicate stores, so I'm looking for 8 unique stores out of 10.

Can someone assist?

Seems easiest to take your data sheet ('SHC EL...') and create a list of unique store numbers based on AM, RM, and RVP. Then just do a count based on the unique stores.

Thanks for the feedback, but I'm looking for a formula combination I can produce weekly.

Perhaps someone will come up with a formula that can automatically produce the results you want, though it seems less likely with more passing time.

However, sometimes the manual solution is the most efficient. I made a copy of the data sheet and deleted out the irrelevant columns, then removed duplicates. Updated your formula to reference the 'Unique' worksheet. Took about 3 minutes. If no more efficient solution is provided, spending 3 minutes is surely manageable on a weekly (or even daily) basis.

Did you counted well?

For 15 total I got unique 8

Please Login or Register  to view this content.

Originally Posted by zbor

Did you counted well?

For 15 total I got unique 8

Brilliant. Exactly what rbenguerel was looking for. Definitely saving this one so I can figure out how it works. Well done.