Excel - Unique count using a criteria 2

[MM1963]

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.

 

[Andrzejek]

Would a COUNTIFS function help?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MM1963]

I don't see how COUNTIFS will work. If I count the "TrackNo" that has a "Priory" of 1, it will return 3. I need it to return 2. Trackno 8710 is listed twice, I only want to count it once.

 

[Deniall]

Are the items in column A always numeric?
Are the items in column A always sorted?

 

[MM1963]

Column A (TrackNo) is numberic and I can make sure the values in column A are soreted

 

[Andrzejek]

If you specify the range of TrackNo with Priority = 1, you can use [tt]=ROWS(UNIQUE(A2:A4))[/tt] and you get 2 as the result:

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Deniall]

You have not specified what happens if you have entries that contradict each other.
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.

 

[MM1963]

The data will refresh every day and the number of rows can and will change, along with the priority. I don't see dynamically specifying a range with priority=1 every time the data refreshes. 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.

 

[MM1963]

Like I said data will change every day. Tomorrow may look like

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)

 

[Deniall]

You still have not answered my 24Jan23@21:04 about contradictions: the same track number appearing multiple times with different priorities.

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

 

[Deniall]

My thoughts so far are that, in the absence of the UNIQUE function a solution should be possible with SUMPRODUCT.

 

[MM1963]

My post from 24 Jan 23@21:13 did answer that question.
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.

 

[Deniall]

Apologies, MM1963.[ ] Sloppiness on my part.

IF your sample table of 24Jan23@21:21 starts in cell A2 of your worksheet, and IF (as you say) your table is sorted by "trackno", and IF (as you say) the table does not contain "contradictions", and IF the priority number whose count you want is in cell D3, then the formula
=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[&nbsp;]-[&nbsp;]as in this case[&nbsp;]-[&nbsp;] obviates the need to use Array Functions.)

 

[combo]

You can also consider pivot table solution. In the linked article you can find various solutions with pivot table.

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

 

[MM1963]

Thanks Denial. Sumproduct works

 

[Andrzejek]

Would the VBA solution be acceptable...

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  [green]'priority 1 in column B[/green]
        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

 

[MM1963]

Thanks, just looking for a formula.

 

[SkipVought]

Here's another very slight twist on Deniall's solution with a visual that may help demonstrate what's going on in the SUMPRODUCT function.

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 I4 is 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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[MM1963]

Thank you very much SkipVought!!All great suggestions. I appreciate the input.

 

[SkipVought]

The previous solution used A1 notation for 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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!