Help with a count formula

H

Heaney

New Member
Joined
Sep 23, 2005
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I am in need of help with a formula to count unique values that are in column A, based off a value in a different column.

Column A is a list of tracking numbers that are repeated numerous times and I want to count only the unique values. Column D will be the same "scanID" number for each of the tracking numbers in Column A. I'd like to be able to perform a search based on a value in Column D to count in Column A.
 

Attachments

  • Example.png
    Example.png
    91.7 KB · Views: 7

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Please update your profile to reflect what version of Excel you are running as different versions would use different features to solve your problem.
Also, it would help greatly if you used XL2BB to posed your sample data.
 
Upvote 0
It would help if you updated your profile to indicate what version of Excel and what OS you are using. Excel 365 provides much more functionality particularly in this situation than older versions.
Also, that data set would be VERY time consuming to transcribe. Please post it using XL2BB.
 
Upvote 0
Have you tried using the UNIQUE function in 365?

Book2
ABC
11234567891234567897
2123456790123456790
3123456791123456791
4123456792123456792
5123456793123456793
6123456794123456794
7123456795123456795
8123456789
9123456789
10123456789
11123456789
12123456792
13123456792
14123456792
15123456792
16123456792
17123456792
18123456792
19123456793
20123456794
Sheet1
Cell Formulas
RangeFormula
B1:B7B1=UNIQUE(A1:A20)
C1C1=COUNTA(UNIQUE(A1:A20))
Dynamic array formulas.
 
Upvote 0
Heaney said:
Hi all,

I am in need of help with a formula to count unique values that are in column A, based off a value in a different column.

Column A is a list of tracking numbers that are repeated numerous times and I want to count only the unique values. Column D will be the same "scanID" number for each of the tracking numbers in Column A. I'd like to be able to perform a search based on a value in Column D to count in Column A.
Click to expand...
Book1
ABCD
1Inbound Tracking NumberDate Courier Created (MST)Auction SourceScanID
2100190877317000852040077085377820312/23/2022 12:32Auction 115086
3100190877317000852040077085377820312/23/2022 12:32Auction 115086
4100190877317000852040077085377820312/23/2022 12:32Auction 115086
5100190877317000852040077085377820312/23/2022 12:32Auction 115086
6100190877317000852040077085377820312/23/2022 12:32Auction 115086
7100190877317000852040077085377820312/23/2022 12:32Auction 115086
8100190877317000852040077085377820312/23/2022 12:32Auction 115086
9100190877317000852040077085377820312/23/2022 12:32Auction 115086
10100190877317000852040077085056568712/23/2022 12:31Auction 215086
11100190877317000852040077085056568712/23/2022 12:31Auction 215086
12100191377331000852040077079512811412/23/2022 12:32Auction 315086
13100191377331000852040077079512811412/23/2022 12:32Auction 315086
14100190877317000852040077085377820312/23/2022 12:32Auction 115086
15100191377331000852040077079512811412/23/2022 12:32Auction 315086
16100190877317000852040077085377820312/23/2022 12:32Auction 115086
17100190877317000852040077085377820312/23/2022 12:32Auction 115086
18100190877317000852040077085377820312/23/2022 12:32Auction 115086
19100190877317000852040077085377820312/23/2022 12:32Auction 115086
20100190877317000852040077085377820312/23/2022 12:32Auction 115086
21100190877317000852040077084970425312/23/2022 12:32Auction 415086
22100191377331000852040077079512811412/23/2022 12:32Auction 315086
23100191377331000852040077086145251212/23/2022 12:32Auction 515086
24100191377331000852040077079512811412/23/2022 12:32Auction 315086
25100191377331000852040077079512811412/23/2022 12:32Auction 315086
26100191377331000852040077079512811412/23/2022 12:32Auction 315086
27100191377331000852040077079512811412/23/2022 12:32Auction 315086
28100191377331000852040077079512811412/23/2022 12:32Auction 315086
29100191377331000852040077079512811412/23/2022 12:32Auction 315086
30100191377331000852040077079512811412/23/2022 12:32Auction 315086
31100191377331000852040077086417061512/23/2022 12:32Auction 615086
32100191377331000852040077086417061512/23/2022 12:32Auction 615086
33100191377331000852040077086417061512/23/2022 12:32Auction 615086
34100191377331000852040077086690038112/23/2022 12:32Auction 515086
35100191377331000852040077086286302412/23/2022 12:32Auction 715086
36100191377331000852040077086286302412/23/2022 12:32Auction 715086
Sheet1
 
Upvote 0
kweaver said:
Have you tried using the UNIQUE function in 365?

Book2
ABC
11234567891234567897
2123456790123456790
3123456791123456791
4123456792123456792
5123456793123456793
6123456794123456794
7123456795123456795
8123456789
9123456789
10123456789
11123456789
12123456792
13123456792
14123456792
15123456792
16123456792
17123456792
18123456792
19123456793
20123456794
Sheet1
Cell Formulas
RangeFormula
B1:B7B1=UNIQUE(A1:A20)
C1C1=COUNTA(UNIQUE(A1:A20))
Dynamic array formulas.
Click to expand...
I have tried this, but doesn't fit my need. I need to count all unique values is column A based on criteria in column D. I'm trying to do something similar to xlookup.
 
Last edited:
Upvote 0
Heaney said:
I have tried this, but doesn't fit my need. I need to count all unique values is column A based on criteria in column D. I'm trying to do something similar to xlookup.
Click to expand...
Where is a D column value found in the A column?
 
Upvote 0
To compound the problem that I haven't been able to copy the data in Column A as text resulting in a number with scientific notation, there is no occurrence of "15086" in column A.
It looks like the data is in a table, is it? Also, are you looking for a single answer in a column in the table, a cumulative count in the table, just a count?
You might want to try something like this:
Book1
ABC
11001913773310008520400770862863024 86286301
Sheet5
Cell Formulas
RangeFormula
C1C1=COUNTIFS(A1,"*"&B1&"*")
 
Upvote 0
kweaver said:
Where is a D column value found in the A column?
Click to expand...
I'm looking to set up a sheet that points to the one I've provided with XL2BB. Example of this below I want to be able to input a "scanID" that is found in Column D, and then count all unique values in Column A.

MailLogDB_Reports.xlsx
EFG
6ScanID# of Envelopes# of Titles
71517873
Lookup
Cell Formulas
RangeFormula
G7G7=COUNTIF(AFC_MailLog_Details_last30!D:D,Lookup!E7)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Count number of Occurrences of Name in list
Replies
5
Views
91
alansidman
A
drom
  • Solved
Count unique values for filtered column
Replies
1
Views
152
myall_blues
myall_blues
D
  • Question
Check if duplicate projects with dates exist
Replies
2
Views
104
dubyarly
D
S
  • Solved
Count Unique Values in a column based on two other columns
Replies
3
Views
186
Fluff
Fluff
J
  • Question
Getting the count or # of occurrences of a value, but counting up to it
Replies
2
Views
78
joshuaba
J

Forum statistics

Threads
1,215,001
Messages
6,122,648
Members
449,092
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top