## Excel - Unique count using a criteria

## Excel - Unique count using a criteria

(OP)

In Excel, I need to count the unique values in one column based on the values in a second column. Here is my example using data in columns A and B

Column A Column B

TrackNo Priority

8710 1

8710 1

8720 1

8730 0

I want the unique count of "TrackNo" that have a "Priority" of 1

So this example would return the count=2. TrackNo 8710 and 8720 are priority 1.

Any help is appreciated.

Column A Column B

TrackNo Priority

8710 1

8710 1

8720 1

8730 0

I want the unique count of "TrackNo" that have a "Priority" of 1

So this example would return the count=2. TrackNo 8710 and 8720 are priority 1.

Any help is appreciated.

## RE: Excel - Unique count using a criteria

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson## RE: Excel - Unique count using a criteria

## RE: Excel - Unique count using a criteria

Are the items in column A always sorted?

## RE: Excel - Unique count using a criteria

## RE: Excel - Unique count using a criteria

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson## RE: Excel - Unique count using a criteria

Suppose you add an entry "8710 2" into your table between "8710 1" and "8720 1" ??

BTW. The UNIQUE function is available only on recent Excel versions.

## RE: Excel - Unique count using a criteria

## RE: Excel - Unique count using a criteria

trackno priority

8710 1

8710 1

8720 1

8730 0

8740 1

8740 1

8750 0

8750 0

so answer here is 3. Represents 3 unique trackno with priority =1 (8710, 8720, 8740)

## RE: Excel - Unique count using a criteria

No hurry. I've gotta get on with my day now.

## RE: Excel - Unique count using a criteria

## RE: Excel - Unique count using a criteria

The individual TrackNo will always have the same priority. So if 8710 has a priority of 1, then all instances of 8710 will have a priority of 1

There will never be contradicting priorities.

Any help is appreciated.

## RE: Excel - Unique count using a criteria

your sample table of 24Jan23@21:21 starts in cell A2 of your worksheet, andIF(as you say) your table is sorted by "trackno", andIF(as you say) the table does not contain "contradictions", andIFthe priority number whose count you want is in cell D3, then the formulaIF=SUMPRODUCT(--($B$2:$B$9=D3),--($A$2:$A$9<>$A$1:$A$8))

will do what you want.

More work will be needed if any of my above IFs do not apply.

(The SUMPRODUCT function is a much underappreciated tool, which often - as in this case - obviates the need to use Array Functions.)

## RE: Excel - Unique count using a criteria

My addition: with Power Query (excel 2016+ or MS add-in for 2013), after getting table to PQ environment, two additional steps: (1) remove duplicates, and (2) aggregate 'Priority' with count of rows, return summary table with counts of unique values for each priority.

combo

## RE: Excel - Unique count using a criteria

## RE: Excel - Unique count using a criteria

## CODE

`Option Explicit Sub MM1963() Dim colA As New Collection Dim R As Integer On Error Resume Next R = 2 Do While Range("A" & R).Value <> "" If Range("B" & R).Value = 1 Then 'priority 1 in column B colA.Add Range("A" & R).Value, CStr(Range("A" & R).Value) End If R = R + 1 Loop Range("D2").Value = colA.Count End Sub`

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson## RE: Excel - Unique count using a criteria

## RE: Excel - Unique count using a criteria

I also combine Andy's suggestion, using the UNIQUE function to generate a list of Priorities from which to generate a count of unique occurrences of each.

Note that

the formula in cell I4is displayed in the Formula Bar...the BLUE range references the range of the Priority values

the RED range references a Priority value

the PURPLE range references the range of the TrackNo values

the GREEN range references the range of the TrackNo values OFFSET BY ONE ROW.

I differ only slightly with Deniall's formula in that I MULTIPLY each expression in the formula arguments.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...unless you multiply yourself by the speed of light squared, then...

You Energy!## RE: Excel - Unique count using a criteria

## RE: Excel - Unique count using a criteria

notationfor the table. As data is added or deleted from your table, the formula ranges may need to be adjusted.If you were to use Structured Tables, a GREAT feature introduced in 2007, you would have some options that would guarantee that changes in your Structured Table row count would be accomodated in a properly constructed formula using

Structured Table range notation.My Structured Table is named Table1. You can change that name to be more descriptive of your application.

The range of the TrackNo values OFFSET BY ONE ROW can be expressed using the Structured Table TrackNo range in the OFFSET function, replacing the GREEN range in A1 notation.

Notice how much easier it is to see what ranges are actually being referenced in the formula.Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...unless you multiply yourself by the speed of light squared, then...

You Energy!