# distinct

1. ### 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...
2. ### 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...
3. ### 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...
4. ### 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...
5. ### 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...
6. ### 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...
7. ### 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...
8. ### 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...
9. ### 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...
10. ### 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...
11. ### 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...
12. ### 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...
13. ### 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.
14. ### 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...
15. ### 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...
16. ### 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),"")
17. ### 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?
18. ### 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...
19. ### distinct count in pivot table

How does distinct count work in pivot table? If I as summing distinct count of a customer and I showing three years of data for example If customer A bought stuff in all three years will he show three times or will he show in first occurence in data or will he show in first year of data...
20. ### Distinct Count Formula

HI, I am looking for to get a distinct count formula in a given column. For example, I have a column which contains ID's (both text and number - String), I would like to get unique count...if one ID repeated twice then I should get only once in the formula. Thank you,

### This Week's Hot Topics

• Problem in vba vlookup
Dear All Master, The problem I mean is as follows : 1. I want to modify the vba code because it takes too long/very slow to vlookup in VBA code...
• VBA Vlookup
Please for help. Why it`s not works? [CODE=vba]Sub Check_equpment1_click() Worksheets("Production_program").Activate Dim ans1 As Integer ans1 =...
• Nesting INDEX MATCH into SUMIFS
I currently have several formulas pulling in a SUMIFS from a data sheet, beginning with =SUMIFS('datasheet'!\$N:\$N, 'datasheet..... but as I am...
• Extracting number from a range randomly
Hello Expert, please kindly advise what is the best method to extract numbers from a range randomly I have a series of numbers in the range of A1...
• Problem with If Formula
I am using the following formula in cell A5. I would like if N5 is blank then A5 also return blank. If N5 is 0 or is greater than zero then equals...
• Is there a way to make the day's name show up automatically in a header?
I have a form I print out every day..and in the top right custom header I have the &[DATE], which automatically gives the current date in top...