LinkBack URL
About LinkBacks
I need a truck count. Every truck has a number and they are hauling multiple loads. I need a number of trucks only - not loads hauled.
What formula function do i use for this.
your help is greatly appreciated.
Thank you
I need a truck count. Every truck has a number and they are hauling multiple loads. I need a number of trucks only - not loads hauled.
What formula function do i use for this.
your help is greatly appreciated.
Thank you
Need more info. Are these truck id's entered in a column? each ID in a separate cell?
If you want the unique truck ID's in A2:A1000 with no blank cells (very important)
=SUMPRODUCT(1/COUNTIF(A2:A1000,A2:A1000))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Do you literally mean that one cell contains many truck numbers - which is implied from your subject header?
Or do you have a list of cells each containing a single truck number?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I apologies if this reply is posted a 2nd time, I responded, and cant see the reply,
the trucks are in a column, and each id is in a separate cell. I need excel to count every duplicate entry as 1 truck. I tried the countif formula, but cannot identify the criteria
If your ID"s are in A2:A172
=SUMPRODUCT(1/COUNTIF(A2:A172,A2:A172))
You might want to use a dynamic named range if the number of rows is constantly changing.
TRUCKID = OFFSET(A2,0,0,COUNTA($A$2:$A$100000),1)
Didn't Chemist's formula work for you? If not maybe you have a space in there somewhere.
If so try
Formula:
Please Login or Register to view this content.
Both solutions are assuming the truck numbers are indeed regular numerics and not text.
Actually the SUMPRODUCT solution will work with non-numeric values but, as Richard pointed out, will not work if you have any blanks
I searched my computer and found the original template... here was the formula =SUM(IF(FREQUENCY(IF(LEN(K3:K306)>0,MATCH(K3:K306,K3:K306,0),""), IF(LEN(K3:K306)>0,MATCH(K3:K306,K3:K306,0),""))>0,1))
Now I am getting a value error.... can any of you see what may be wrong here?
Try entering it using CNTRL + SHIFT + ENTER. It is an ARRAYED formula which has to be entered that way instead of a simple ENTER.
I tried the formula given here, and I recieved a "not enough information" errorso hopefully the originally formula can be worked with
I could cry!!!! That worked!
Thank you thank you thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks