how use distinct in formula

[Forestq]

Hi,

I have functions:

=SUMPRODUCT(1-(Id=""),--(SEC=A2))

it's posible somehow to use here distinct in def.range called "Id" ? because I want sum distinct ID

I know that I can use pivot, but if is posisble please help me.

 

[sergioMabres]

Hi Forest
Let me ask you a question to fully understand the problem, so if Excel had a matrix function to extract distinct (like in sql) values from a range called DISTINCT and I apply to range ID as in 1-(DISTINCT(ID)="") you will not know the size of the resulting range, so how would you match the sizes of SEC range?
Sergio

 

[Forestq]

hi,

I need to count unique value from range called "ID" for proper value(it's stream - range called SEC).
example: how many unique ID are for stream A, B, C (where stream are in range SEC)

if you maybe show me how I just can do it for range "ID" it would be great. I don't know any functions which can I use :/

 

[bjp2185]

what i would like to know is how do i get my customer address to automatically fill in (cell b) if i select them by customer number (drop down menu in cell a) in an different cell (cell c)

[h=2]what i would like to know is how do i get my customer address to automatically fill in (cell b) if i select them by customer number (drop down menu in cell a) in an different worksheet?[/h]

 

[sergioMabres]

I do not know a way to do this with SUMPRODUCT because SUMPRODUct needs the ranges to be of the same size and DISTINCT(ID) has a variable length
I know that you can do that using Microsoft Query, Advanced Filter or Pivot Tables
Sorry
Sergio

 

[sergioMabres]

And here is a page of counting unique values using a formula
Count occurrences of values or unique values in a data range - Excel - Office.com

=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

Count the number of unique text and number values in cells A2:A10 , but do not count blank cells or text values (6)
It may help you
Sergio