distinct

  1. B

    VBA - Identify / mark distinct values in a range without COUNTIF

    Please see below example table. Column A has random numbers between 1 to 100 Column B has the following formula copied down starting from B2: =IF(COUNTIF($A$2:A2,A2)=1,1,0) I would like to get value of 1 in column B if the row in column A is distinct, else 0. This works well only for small...
  2. G

    DAX Distinct Count Measure

    Hi, would appreciate any help with this, I have a List of Users and Dates they logged on over the last few years, I want to create a meaure which counts the number of Distinct Users who have logged on in the last Calendar Month and the Last 12 Months depending on the Month selected in the...
  3. E

    Sum Distinct Values within Time Constraints?

    Hi All, I'm trying to solve the following problem using some combination of sumproduct, sumif, and countif functions: I have a dataset where people move into and out of areas at different times. Sometimes there are multiple people in an area at a time, other times there are none. Each area has...
  4. D

    Counting distinct values as well as giving a sum

    Hello, I am after some assistance in getting some information out of my database which is causing me some problems. My data shows each individual journey that a truck has taken and I am trying to show the number of journeys each truck did in a given county on a given day and a given shift...
  5. W

    Distinct values - Powerpivot

    I've been doing some work to identify some issues with the data in a couple of my database tables. I've got one table [tbl_customer] with a reference number (unique) and details of customers, and another [tbl_sales] with details of the products that they've purchased (many products and many...
    • Web_Jim
    • Thread
    • customer distinct powerpivot reference table
    • Replies: 0
    • Forum: Power Tools
  6. A

    Removing Duplicates with Timestamp

    Hello, I have a column of datestamps in a sheet and I need to separate the date from the time (=Int or =left both work). Then I copy the formula down. With the column highlighted, I go to Data and use the Remove Duplicates function. The result lists the distinct dates, but when I click OK, the...
  7. J

    Extract unique value from column using DAX

    I've come across what I thought should be a simple problem, but I can't quite figure it out. I have a table that's the result of an expression that could have a column like this in certain instances: Row Key Index <tbody> AH4000 1 AH4000 2 AP9999 3 </tbody> What I want is to keep only...
    • Jorsch
    • Thread
    • dax distinct filter powerpivot
    • Replies: 1
    • Forum: Power Tools
  8. A

    identify distinct names in filtered list

    hi all, i am trying to work out a way of counting unique/distinct names in a list. Unfiltered is straightforward. In each row: countif($a$2:$a2,$a2). I need to be able to have a formula that will also operate on the same data when it is filtered. I aim to be able to identify distinct names by...
    • ajm
    • Thread
    • distinct filtered identify list names
    • Replies: 3
    • Forum: Excel Questions
  9. E

    Distinct Count

    So I have multiple month-end dates in a column. I have the below formula which counts the number of distinct dates and that works, but as I add more month-ends throughout the year I keep having to adjust the below formula. I tried doing =SUMPRODUCT(1/COUNTIF(B:B,B:B)) but that seems to break it...
  10. A

    count distinct values in range multiple criteria

    I am trying to count distinct vehicle numbers in a fleet schedule that have come in for Maintenance between certain dates. I have used the formula below to count the number of distinct vehicles: =SUMPRODUCT(IF((Order_Date<=$O$4)*(Order_Date>=$E$4), 1/COUNTIFS(Order_Date, "<="&O4, Order_Date...
    • ajm
    • Thread
    • count distinct fleet formula job
    • Replies: 5
    • Forum: Excel Questions
  11. H

    Distinct Count based on given criteria

    Hello, I am looking some help in getting distinct count by criteria. For example if you refer the below table I brought the distrinct count by using formala {=Sum(1/Countif(A:A,A2))} however, in additon to this need another conditional criteria. If given % different for no then it should...
    • harinsh
    • Thread
    • count count based on range criteria based distinct distinct count
    • Replies: 3
    • Forum: Excel Questions
  12. T

    Index/Match Next Distinct Value

    Hello - I have two columns: Column A | Column B 6022 WF102 6022 WF102 6023 WF104 I am using index/match to fill Column B using look up values in Column A. My goal is to grab the next distinct value in Column B. There are multiple vendors with the same product code...
    • temerson
    • Thread
    • column distinct distinct values index & match lookup
    • Replies: 1
    • Forum: Excel Questions
  13. T

    Multiple Routes on Single Map

    Good Afternoon, I know this isn't an Excel question, but I was hoping someone on this board could help. I need to create mutiple routes on a map and was wondering if anyone new of a free service to do this task. Basically ill have 41 distinct across the US and the software my company uses you...
  14. megera716

    Pivot Table average works correctly when row is expanded, not when row is collapsed

    I'm using a PowerPivot measure to determine average sale amount by customer for a particular type of product by dividing their total sales of that product by a distinct count of the item. When it's expanded, it works beautifully: When I collapse the row though, it throws off the average...
  15. L

    Count Distinct

    Hi-- I'm trying to count distinct text values in a range. I found this formula on another help site =SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10, A2:A10), 0)) .... but it gives me a self-referencing error and returns 0 (My formula is placed in column B). Thanks in advance.
  16. N

    Count Distinct Unique values with nested IFs Formula

    I'm looking for a formula to count distinct values in column E (Order Numbers) if they are on a specific date in column M and if the orders are complete, or have zero left to build, meaning column R values are equal to zero. I've used...
    • Novice86
    • Thread
    • column countif 2 conditions met distinct distinct count values
    • Replies: 1
    • Forum: Excel Questions
  17. R

    Distinct Result from Multiple Countif

    all, I need help I have 3 columns of data store id, store name and item no if I create a Pivot by store name and count of item no I get 250 store names and counts this works the problem is I am trying to perform a distinct count of SKUs across 5 stores I can do this in a pivot using...
  18. P

    Distinct Count Formula

    Hi All, Is there a formula to find distinct count in a column without using COUNIF function. Currently I'm using the below formula, but it takes more time to complete 9000 rows of data i have. Can someone assist me on this pls. IFERROR(1/COUNTIFS(A:A,A2),"")
  19. J

    Select distinct but return all fields

    I want to select a single distinct example based on one field in a table. Something like: Select table.* From (Select distinct table.[country] from table) Where( . . . .) Is this possible in access sql?
  20. D

    Counting for distinct values from several sheets

    Hello. I have sheets that have some numbers in C column. Then in J column there are some values that are in other sheet, the value depends from number in C column. Some are still have div/0. And i have several sheets that look like that. Now i need to get number of unique distinct values that...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

This Week's Hot Topics

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
Top