Conditional Unique Value Count

[aerook]

Hello Internet!

I'm trying to count unique values in a certain column while applying a conditional to which values it counts. Column A contains ID numbers, column B contains parcel service names, and column C contains pricing.

A B C

546784513 FedEx 142.50

542748945 FedEx 75.86
542748945 USPS 89.50

987125445 UPS 44.20

487654158 USPS 87.22
487654158 FedEx 166.23

That's a rough layout of what my sheets look like. My goal is a function that can return the number of unique orders that shipped via a certain parcel service. I've got a formula that can count the number of unique values on column A, and it looks like this:

=SUM(IF(FREQUENCY($A$2:$A$409,$A$2:$A$409)>0,1))

I've tried combining it with conditionals in several different ways, but haven't gotten anything that accomplishes what I want.

There may be an alternative with PivotTables that someone might have some insight into. I've been able to achieve exactly what I want with a PivotTalble, but the problem is that I need to quickly get this formula into over 300 sheets. Each sheet is the log of a single day's activities, and I can't just ctrl+c ctrl+v the pivot tables between sheets.

 

[Trebor76]

Hi aerook,

Welcome to MrExcel!!

This is almost identical to this recent thread I was involved in.

As such, this array* formula will do the trick:

{=SUM(IF(FREQUENCY(IF(B2:B409="USPS",MATCH(A2:A409,A2:A409,0)),ROW(A2:A409)-ROW(B2)+1),1))}

HTH

Robert

* When creating or editing array formulas, you need confirm the formula via the key combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[aerook]

You are my hero. Thank you!!!

 

[Trebor76]

Thanks for the feedback and glad to have helped