Countif unique values

[ian_a91]

I'm trying to create a formula that will count the unique values from one stationary criteria and other data that can change

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Customer[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]Customer 1[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]Customer 1[/TD]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD]Customer 1[/TD]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD]Customer 2[/TD]
[/TR]
</tbody>[/TABLE]

Looking at the data above i want to be able to count the unique values for both columns

A countif "employee 1" would be 2

I'm looking to create a formula which will countif "Employee 1" then look at the values in the Customer column for unique values

so it would be something like =Countif "employee 1" and unique value in Customer column = 1

Any help would be great

 

[peterhinton]

had exact same issue just the other week,

http://www.mrexcel.com/forum/excel-questions/979967-count-uniques-if-criteria-met.html

the solution given worked perfectly

 

[ian_a91]

Thanks for this Peter but my differs from your issue because the data in the the "Customer" column changes day on day so I cant add in "Customer 1" so i need to countif formula to look at Employee 1 and count the unique values in the customer column. I'm sorry if this isn't 100% clear.

 

[Weazel]

maybe...

Excel 2013[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Name[/TD]
[TD]Customer[/TD]
[TD="align: right"][/TD]
[TD]Employee 1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Employee 1[/TD]
[TD]Customer 1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Employee 1[/TD]
[TD]Customer 1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Employee 2[/TD]
[TD]Customer 1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Employee 2[/TD]
[TD]Customer 2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF(B2:B5<>"",IF(A2:A5=D1,MATCH(B2:B5,B2:B5,0))),ROW(A2:A5)-ROW(A2)+1),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[ian_a91]

{=SUM(IF(FREQUENCY(IF(B2:B5<>"",IF(A2:A5=D1,MATCH(B2:B5,B2:B5,0))),ROW(A2:A5)-ROW(A2)+1),1))}

Looking at the formula could the reference A2 and D1 refer to one cell or is there a specific reason they refer to two different cells

 

[Weazel]

it really just depends where on your spreadsheet you have the data.

D1 can go where ever you want it as its just the criteria for column A

A2 would be whatever the start of the range is that you have the data in. so if the data was in column C starting in row 3 then it would be ROW(c3:c6)-ROW(c3)+1

hopefully that makes sense

 

[peterhinton]

Thanks for this Peter but my differs from your issue because the data in the the "Customer" column changes day on day so I cant add in "Customer 1" so i need to countif formula to look at Employee 1 and count the unique values in the customer column. I'm sorry if this isn't 100% clear.

rather than have "customer" in the formula, just use the cell reference where you have the info that would change,

 

[ian_a91]

thats brilliant it's working great thanks for running me through that i really appreciate it

 

[Weazel]

you're welcome