distinct

  1. J

    Create summary page for multiple sheets that counts unique values in column E by tab

    I have a workbook with 19 tabs(sheets). Each tab has the same column headings but the data is different. I want to create a summary page which would list the name of each tab and unique count of data in column E of each tab. The summary page would look something like this: <tbody> Tab name...
  2. S

    Identify distinct values based upon multiple criteria

    I am trying to use a formula to obtain a count of the number of distinct values in a list of data I have. I can use the follow: =sum(1/countif(ID_Range,ID_Range)) And it returns the correct number of distinct IDs in the range. However, I need to expand my number of criteria to something such...
  3. D

    Grouping in Pivot Table when Add this data to Data Model

    Like the title. I was having trouble grouping after checking the box to add data to Data Model (I wanted to use the Distinct Count not regular count), and came on this article and comment that said it wasn't possible to group in your pivot table if this box was checked...
  4. E

    Countif formula for criteria where only one value is true for seperate column

    Hi all, Is there any way I can create a Countif formula where I can count a distinct ID once where the criteria would be that the criteria range only has one exact value? For example in this case, I'm trying to create a countif for distinct ID's where the sub_category would be "a" only. Since...
  5. D

    VBA summing values in column B grouping sums by value in column A saving values in column C

    I have a sheet that looks something like this: A B 1 15 1 10 1 5 2 1 2 3 2 0 I want my result to look like: A B C D 1 15 1 20 1 10 2 9 1 5 2...
  6. H

    Distinct Count based on two criteria (time involved)

    Please see the spreadsheet below. I've tried multiple array formulas with combination of sum/sumproduct/if/countif and no luck. I am trying to count the number of distinct ANI's that have a call time of greater than 0:00:29 and are of the campaign, Inbound - Live TV5. I've been at this for...
  7. J

    Unique Distinct Lists Quirk?

    Hello Everyone, Im trying too understand whats happening with a small oddity when creating a Unique Distinct list. So in Column 'C' I have list of names with duplicates and in the column 'G' I have a Unique Distinct List created using the red formula at the top. Ive extended the Array beyond...
  8. W

    VBA: How to "Add this data to the data model?" and make data in values distinct?

    I have the code to create a pivot table from my data set however I'm not sure how to include the data in the data model (which is an option you see when clicking on "PivotTable" from the Insert Tab. My goal is to have a Distinct Count of my variable that I'm using in the Values data field...
  9. B

    INDEX, MATCH, LARGE and/or SUMIF/SUMPRODUCT

    I am looking for a formula that will assist in the last two columns -the first two columns is the raw data -the third and fourth columns is a distinct for the class name and a sumproduct -the last two is an Index/match/large I am looking for an all in one formula and haven't gotten any. I...
  10. J

    Finding min difference between any of the values in a list values

    I have about 100 values in column some of which are the same and want to retrieve the smallest difference between any of the values in the list. For the example of the list of numbers below I would like to retrieve the answer = 0.04 <colgroup><col></colgroup><tbody> 15.03 15.11 15.22...
  11. D

    Count Distinct in Pivot Table when there are Page Fields

    Hi, I have a problem with Excel and I'm writing hoping you can help me. I have Excel 2010 (with Excel 2013 problem seems to be resolved), with a large table of data, and a pivot on this table. This pivot contains page fields, and in value fields I have to count infos of a column "NDC". But I...
  12. L

    Count distinct in Pivot table Excel 2013

    hi all I have recently upgraded to excel 2013 from 2010 and I have set up some new pivot tables using the data model function so I can count distinct items. However what I want to know is if I open a report created in 2010 can I then use the count distinct function? I can't seem to get it to...
  13. F

    Using SumIFs within CountIFs

    Hello - for simple use by other users, I'm limited to standard Excel 2010 functionality. I'm trying to do a count of A, where sum of C by unique B are >0. This would be the first step of a much bigger CountIFS. HELP! <colgroup><col><col><col><col><col><col span="2"></colgroup><tbody> A B C...
  14. J

    SUMX + DISTINCT function

    Goodday, I want to use the SUMX function in combination with DISTINCT (PowerPivot + Excel 2010). For example I have this data: <tbody> Productgroup Product Sales Group Y product1 5 Group Y product2 4 Group X product1 5 Group X product3 3 </tbody> Products can be assigned to multiple...
    • jroz2001
    • Thread
    • distinct powerpivot 2010 sumx
    • Replies: 4
    • Forum: Power BI
  15. J

    Pivot Table: Display Values as Average over a range of dates

    Using a very helpful method found here: http://www.contextures.com/pivottablecountunique.html#download I've successfully obtained 'distinct' value counts in Excel 2010 by making a Pivot Table of another Pivot Table. The final Pivot Table's Row Labels are 30-minute increments of time (8:00, 8:30...
  16. A

    Add a Date Range to a Select Distinct Query

    I am having trouble figuring this out. I have a SELECT DISTINCT query, which is working fine. In the table that the query is based on, there is a Date Column. I would like to add BETWEEN DATE RANGE criteria to this query, but I have been unable to do so. If I add the date column to the query...
  17. D

    Return 1st value of duplicates based on multiple criterias

    Hi Experts, Would need to have output as shown below. I am trying to figure out the right formula to be applied in Col "Output" to return value of ColB (those match in namedrange Members) when it finds first time match based on same value in ColA and the earliest datetime in ColC. For example...
  18. S

    Distinct Count for Pivot Tables

    Hi, I'm using the trusted =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) formula but with large amounts of data, it's absolutely crushing system performance. I'm using the formula in a helper column of a source table to populate a column in a Pivot. This is in Excel 2010. Is there a VBA...
  19. H

    Help on avoiding dupplicates (need distinct ID fields)

    Hi all, Due to a corrupt worker_ID table which I don't have write rights to, I get cartesian product on my query table below: workhours_id Date Amount worker_ID Productive 27567 05.07.2014 8,78 sein01 TRUE 27567 05.07.2014 8,78 sein01 FALSE 27568 05.07.2014...
  20. H

    Return Distinct Values Based on Different Criteria

    Hello, I am looking for a formula that will return the distinct values of a list based on a criteria. For example...the formula would go into cell D4, and it would return the distinct "Item Names" in column B based on the "Group" in column A. Any ideas? Thanks! Mike

Some videos you may like

This Week's Hot Topics

  • Date update overflow error ‘6’
    • Started by Tayl4n
    • Replies: 31
    Dear MrExcel community, I have a project search & update & add & delete can do. There are 32 column and 5 of them is date. When I search date...
  • Highlight Row and Column on cell selection
    • Started by EBoudreau
    • Replies: 28
    In the following I have some code I need modified to see if I can do the following: 1) Only have this work when selecting a cell inside a couple...
  • Need Numbers Inputted and Deleted
    • Started by rebel123
    • Replies: 26
    Thanks for your help. Not sure if there is even an = equation for this. Please see attachment. If there is a solution, please respond with = So...
  • Worksheets(x).Select Error
    • Started by aadil_1408
    • Replies: 24
    Hi, I am trying to select multiple sheets using the following code. [CODE=vba] Dim x As Integer ThisWorkbook.Worksheets(3).Select...
  • myFind Range not working
    Please can someone say why this myFindRng goes red in the code? [CODE=vba]Private Sub Model_Chose_Change() With ThisWorkbook.Worksheets("Job...
  • How to identify the last page break in a sheet
    • Started by dpaton05
    • Replies: 22
    How can I find the position of the last page break in my sheet? There may be multiple pages and I want to know how to find the position of the...

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