1. C

    Should I use Data Model

    I run some analysis that's fairly heavy. And one part involves getting unique instances of a customer where distinct data field would help. However Data Model seems to be really CPU time consuming. Since I know there are never really more than about 50 unique instance per customer per daily...
  2. R

    count distinct from another workbook if between date range

    as the title states im trying it count distinct values from a column in a table from another workbook if the values are between a certain date range(cell a1 & cell a2). my current formula for the most part is =SUM(--(FREQUENCY('FY18-FY19 PPD.xlsx'!$B$2:$B$51003,'FY18-FY19...
  3. N

    Sumifs Distinct Date

    Hello all :) I have a database in excel containing total hours worked by date. On the odd occasion the user will enter there hours worked more than once, if they submit their user form multiple times through out the day. I am trying to build a report that summarises the total number of hours...
  4. G

    DAX and distinct count filter context help

    So I have just one table that I added to the data model, to give me some of the flexibility of dax and afford me some practice time. I have a measure that is a distinct count on my "sites" column, to count unique store numbers in my data set. I would like the measure to ignore the filter...
  5. E

    Count distinct values

    Hi, This formula works fine to count distinct values in a range: =SUMPRODUCT(1/COUNTIF(A2:A13,A2:A13)) But it does not works when there's blank cells in the range. There's a solution for this? Thanks
  6. F

    Distinct Count with filters on the page

    Hi All, I desperatly need help with powerbi. I am new to powerbi and I have 1 table with unqiue ids called headcount data and another table called survey data which has multiple ids. I have a relationship 1 to many from the headcount tble to the survey tble. I then have 3 filters:-...
  7. D

    Count Distinct and subtotal

    I want to count distinct stock numbers J4:J1226 I'm using formula =SUMPRODUCT(1/COUNTIF(J4:J1226,J4:J1226)) and it works pleasantly. Now I want to add one more problem. When I filter out perhaps stock numbers worked by a particular person I want it to return a count distinct subtotal. I can...
  8. M

    Pivot with both count distinct AND calculated fields

    Hey guys first post, good morning (well here anyway) to you all. I have looked for a solution to this and am hitting a bit of a blank, I am sure it must be answered somewhere Excel 2016. I want to add distinct count to a pivot table. in its simplest form, its sales rep, order counts, and...
  9. H

    Summarize min date in data model pivot

    Hello, I have a need in a pivot summarize one column by min and max date and another column by distinct count. In a default pivot the min and max date work fine but I cant get distinct count. So when I create the pivot in data model mode the distinct count works fine but now I can summarize...
  10. X

    Alternative for use of SUMPRODUCT or Pivot Table Distinct Count Function

    Hey Everyone, I would really appreciate if someone can find me solution for this problem. I am using a Pivot table and need to use it as making interconnected graphs and it's just easier. However as when I want a count of something it counts all the rows in that column with the data but not...
  11. E

    Rank by Person, without Repeats across people

    I have a set of data that is salespeople (column A), and how much they sold (column C) by client (Column B). I need to get the top 5 clients by sales for each salesperson; however, no duplicates across salespeople. So if John Wilkes had $10M to Fun Company as his #1 , and Jane Bridges had $8M to...
  12. A


    Hello! I am trying to do a countif that counts one column (column D) to see if it says "face up" but I only want to include those that are distinct in Column C. Is there a way to write this?
  13. E

    Sort Pivot Table my Distinct Count?

    Greetings, I have a pivot table where one of the fields is distinct count but I can't find a way to sort my distinct count. Is it possible? Thanks in advance for any help.
  14. B

    Count Distinct on Multiple columns with criteria

    Hi all, Hi have a table with three columns, one for names (column A) and the other two for dates (Column B and C). I need to count the distinct dates from column B and C based on criteria on column A. For example, if the criteria is Paul the result must be 4 (count of distinct dates for this...
  15. G

    Counting Unique Dates Based on Criteria

    Hi Folks, Struggling with this, even having read threads here about functions that may help. Looking to total unique dates based on criteria in another cell (not an overall total of unique dates). e.g. A B C D E F G Resource Name Position Hours Date...
  16. K

    Count distinct rows with multiple criteria

    I'm having a hard time with this, and I'd really appreciate some help. I've got 8 compounds distributed across 4 containers. I need to see if there are any compounds for which the container is empty. I can use =SUMPRODUCT(1/COUNTIFS(tbl[COMPOUND],tbl[COMPOUND])) to return number of distinct...
  17. M

    How might one generate a distinct list that keeps a static order?

    I am currently generating a distinct list from a list with duplicates using: =IFERROR(INDEX(C15:C50, MATCH(0, INDEX(COUNTIF(D50:D50, C15:C50), 0, 0), 0)), "") However, when the source list changes, the entries of the distinct list will (potentially) reorder. This is messing up another purpose...
  18. T

    Remove duplicates using SQL DISTINCT

    This code is taken from here: https://www.ozgrid.com/forum/forum/tip-tricks-code-no-questions/advanced-excel-integration/7960-create-a-unique-list-with-ado-sql-from-closed-workbooks-xl It removes duplicates using the SQL command DISTINCT. The problem is it works for a small column of...
  19. J

    Distinct X,Y Data Combinations

    I am posting this in lieu of an unanswered thread -- "VBA to List all Possible Pairs of X,Y Data in two Columns" https://www.mrexcel.com/forum/excel-questions/1038311-vba-list-all-possible-pairs-x-y-data-two-columns.html I have two columns of X,Y data. I need to rearrange them so that I get all...
  20. 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...

Some videos you may like

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