1. S

    Substitute FILTER function in Excel 2007

    I have a table like the one attached. I need to get distinct values in column S (client name) but only of the records having a specific column A (supply code) value (OTT23 for example). Is this feasible without using FILTER function? Any help is much appreciated.
  2. O

    Finding Distinct Values with a Formula

    Hi I need a formula which will count how many distinct values I have in a particular field in my dataset – I envisage something similar to COUNTIFS / SUMIFS. Below is a screenshot of my data: The formula needs to count the number of distinct Customer IDs in a country but have the ability...
  3. O

    Distinct Values

    Hello all, I need to produce a template for a table which shows me the number of distinct customers I have in each country each month. I have a list of global sales transactions with Country, Customer ID and Sales ID: • Each customer has a distinct Customer ID • Each customer may have...
  4. T

    Formula help on distinct value

    Hi everyone Looking for some help on a formula which will return the first unique value in a range. Included an image of what I'd like the formula to return - basically if the value in column A has appeared for the first time, then column B should return the value in column A. However, if...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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...
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. 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.
  20. 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...

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