Countif unique values

ian_a91

Board Regular
Joined
Apr 23, 2014
Messages
63
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
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.
 
Last edited:
Upvote 0
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]
 
Upvote 0
{=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
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

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.
Go back
Back
Top