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

Name	Customer
Employee 1	Customer 1
Employee 1	Customer 1
Employee 2	Customer 1
Employee 2	Customer 2

<tbody>
</tbody>

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

	A	B	C	D
1	Name	Customer		Employee 1
2	Employee 1	Customer 1		1
3	Employee 1	Customer 1
4	Employee 2	Customer 1
5	Employee 2	Customer 2

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula D2 {=SUM(IF(FREQUENCY(IF(B2:B5<>"",IF(A2:A5=D1,MATCH(B2:B5,B2:B5,0))),ROW(A2:A5)-ROW(A2)+1),1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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