Count unique text values using criteria

**roadbiker** · 02-27-2013, 09:40 AM

In the attached spreadsheet I need to count the number of unique providers (a text field) for each reviewer (C5 & C6). Named ranges are: demo_Reviewer (J4:J14) & demo_Provider (L4:L13). I know how to count unique providers by formula but need to add reviewer as a criteria so that I get an unique count per reviewer.

**Ron Coderre** · 02-27-2013, 11:24 AM

Using your posted workbook, this regular formula returns the count of unique Providers for the referenced Reviewer

Please Login or Register  to view this content.

• Adjust the range references to suit your actual data

• Copy that formula down as far as you need.

Oh...and turn the Excel calculation mode for your workbook to Automatic (if you want to see the latest actual calculated values)

Is that something you can work with?

**roadbiker** · 02-27-2013, 11:39 AM

Thanks. Certainly something I can work with. I can dynamically code the ranges/formula in VBA, but how can I use named ranges in your formula? I've tried several ways but all give me zero as result.

**Ron Coderre** · 02-27-2013, 11:51 AM

Can you post an example formula that uses named ranges, so we can see what you're attempting?

**roadbiker** · 02-27-2013, 11:53 AM

Look in the Summary Table in spreadsheet - col D-> H

**Ron Coderre** · 02-27-2013, 12:06 PM

Got it...try this regular formula in your workbook:

Please Login or Register  to view this content.

Does that solve the problem?

**roadbiker** · 02-27-2013, 12:13 PM

Yes it does! Thanks again. Excel auto-corrected the formula as below. Only difference I can see is a space after "INDEX(1/" that looks like it was removed...

=COUNT(INDEX(1/((MATCH(B5&"_"&demo_Providers,demo_Reviewer&"_"&demo_Providers,0)=(ROW(demo_Reviewer)-MIN(ROW(demo_Reviewer))+1))),0))