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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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.
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...
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...
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),"")
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?
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...
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...
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,