Hello @Fluff,
This is a follow-up question regarding my earlier thread here, using your following formula:
=LET(a,REDUCE("",t_MUs[[Theme A]:[Theme C]],LAMBDA(x,y,VSTACK(x,IF(SUBTOTAL(103,y),y,"")))),ROWS(UNIQUE(FILTER(a,a<>""))))
The filtered results are working great. However, I wanted to...
Hello,
I have a formula that counts distinct values within a range of columns.
=LET(
ThemeRng,t_MUs[[Theme A]:[Theme C]],
SUM(IF(ThemeRng <> "", 1 / COUNTIF(ThemeRng, ThemeRng), 0)))
However, I'm trying to figure out how to do the same thing when a Table column is filtered. I found a few...
Count DISTINCT in ColumnA IF ColumnE status='QA'
I want to have a new column (RESULTS) where the formula counts only ColumnA (DISTICNLY) IF ColumnE cell = 'QA'
Small example:
Category
Status
RESULTS
blue
QA
3
<=== ONLY 3 DISTINCT Catgories that have status='QA'
blue
QA
blue
no...
Hi all,
I'm struggling to figure this one out...I've searched online and have tried suggestions for other similar requests, but to no avail...
In my data set, I have Survey IDs (unique for each survey), but trying to add a column (Survey #) that indicates what survey # it is for that...
Hi all,
I'm attempting to do a simple 12-month turnover rate:
Employee Separations / Avg Employee Headcount over 12 months
However, the dataset I have has employees appearing multiple times as they are split over multiple roles. Initially, to overcome this issue for getting my rolling...
Hi,
I need a formula that can count distinct number customers (ie: count each customer only once), for each combination of Division and Order Value Tier.
So for the combination of:
Division = East
Order Value Tier = 0 - 100k
Result = 3
So it counts ACME once, ABC once and XYZ.
Thanks John...
I have 2 separate columns in the same table. I would like to have the Distinct Count of Case Owners.
The result from below should be 3: John Smith, Jane Doe, Richard Blank
Field
New Value
Case Owner
John Smith
Status
Active
Status
Pending
Case Owner
Jane Doe
Case Owner
John Smith...
Hello.
I have two simple columns as follow :
A B
city A 130
city A 170
city B 200
city A 300
I need to distinct count the city<200 . i.e =1.
how can I do this? i've tried many ways but got no answer :(
I am trying to do a DISTINCTCOUNT of client reference numbers in a table, but I want to combine the results of two separate filters (to be applied on an "Or" basis).
Here are the two separate MEASURES:
=CALCULATE(DISTINCTCOUNT(Table1[PulseReferenceNumber]),'Table1'[SurplusDeficit]>50)...
Hello,
I essentially have a table with 3 columns (date, customer, product). I am looking to say on x date used as an expression filter in excel do a distinctcount of the customer column if the customer has both of these products (count of customers owning both products). Any thoughts?
Thank you
Hello All!
I'm trying to create a formula that counts the number of unique dates within a date range based on the type of item.
I've tried several different formulas and researched ways to do it, but each formula was so specific to the poster's specific problem, that it did not work so I...
Can some one help me with a VBA code that gives distinct count of data in in "I" Column of sheet1 (dynamic) after filtering "Temp" and "Tem" in A column and "MX" in E column. I have a code as below which gives distinct count from "I" column but not able to add the filters. Would be great if get...
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...
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...
Hi everyone,
I was using the distinct count function in pivot tables so it doesn't count duplicates.
To have it available, the flag "add this data to data model" must be marked when inserting the pivot table from the data source.
This has always been very helpful to me.
I am using Mac now, and...
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,
My background:
I am a strong user of Excel, but not experienced in VBA code. I am proficient in "Recording Macros"
I have a 25,000 row set of data which is replaced each month with new data, but has an identical column structure and formats.
I have several macros built which then cleanse the...
Good Morning,
There is a user who has the Mac version of Excel which doesn't include the option for Distinct Count in Pivot Tables.
Is there an alternative method to get the same result?
Many thanks in advance
Hi all,
I am setting up a macro to run a few pivot tables that from a the same data source in a single worksheet. I know how to manually make the pivot table a data model which will enable me to do the function of distinct count in the pivot table. But I have not been able to make that work in...
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...
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.