×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel - Unique count using a criteria
2

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.

RE: Excel - Unique count using a criteria

Would a COUNTIFS function help?

---- Andy

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

RE: Excel - Unique count using a criteria

(OP)
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.

RE: Excel - Unique count using a criteria

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

RE: Excel - Unique count using a criteria

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

RE: Excel - Unique count using a criteria

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

---- Andy

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

RE: Excel - Unique count using a criteria

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.

RE: Excel - Unique count using a criteria

(OP)
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.

RE: Excel - Unique count using a criteria

(OP)
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)

RE: Excel - Unique count using a criteria

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.

RE: Excel - Unique count using a criteria

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

RE: Excel - Unique count using a criteria

(OP)
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.

RE: Excel - Unique count using a criteria

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 - as in this case -  obviates the need to use Array Functions.)

RE: Excel - Unique count using a criteria

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

RE: Excel - Unique count using a criteria

(OP)
Thanks Denial. Sumproduct works

RE: Excel - Unique count using a criteria

Would the VBA solution be acceptable...ponder

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

(OP)
Thanks, just looking for a formula.

RE: Excel - Unique count using a criteria

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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

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

RE: Excel - Unique count using a criteria

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close