Count if unique

[Jak7217]

I started off by making a formula to count unique values in column B, below.

=counta(unique(B:B))

After that, I want to make the formula more elaborate. I only want to count the unique values in column B if column A = "05". I also have a second formula where I want to count unique values in column B when column A = "05", and column F = "5".

Any help would be appreciated. I can write formulas to count unique or countifs for the 5 and 05 criteria, but I can't seem to successfully merge the two.

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=ROWS(UNIQUE(FILTER(B2:B10000,A2:A10000="05")))

 

[Jak7217]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=ROWS(UNIQUE(FILTER(B2:B10000,A2:A10000="05")))

Thank you for the quick response, I have updated my account details. I tried your formula, but am having issues with the filter function, it is not found. The only filter function available to me is FILTERXML

 

[Fluff]

If you are using xl 2016 how come you posted this formula

=counta(unique(B:B))

as you don't have the Unique function.

 

[Jak7217]

I got myself turned around, I'm sorry. I've been researching how to get this to work and tried various formulas. I accidentally posted one of the formulas that I was trying to get to work.

 

[Fluff]

In that case can you post a sample of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Jak7217]

Hey Fluff:

I have restrictions on download and install rights on my PC, I can't use the add-in you are referring to.

Below is an example of the current formula I am using, but still does not fully work.

I want to count unique values in column C if A = 05.

=SUM(IF("05"=A1:A15,1/(COUNTIFS(A1:A15,"05",C1:C15,C1:C15)),0))

Once I figure out how to fix the formula above, I also want to expand it so that it will only count unique if A = 05 and F = 6.

=SUM(IF("05"=A1:A15,1/(COUNTIFS(A1:A15,"05",F1:F15,"6",C1:C15,C1:C15)),0))

 

[Fluff]

In that case can you please just copy & paste some sample data.

 

[Jak7217]

Sure - so I want a unique count of column C when column A is equal to 05. I would expect to have a count of 5 because every text other than Foxtrot meets the criteria. Then I want to write an additional formula to count unique for column C when A is equal to 05 and F is equal to 6. I would expect a count of 4 because Echo and Foxtrot don't meet the criteria.

05		Alpha			6
05		Alpha			6
05		Bravo			6
05		Bravo			5
05		Bravo			5
05		Charlie			5
03		Charlie			6
05		Charlie			6
05		Delta			5
05		Delta			6
05		Echo			6
05		Echo			5
05		Echo			5
05		Echo			7
02		Foxtrot			7

 

[Fluff]

Thanks for that, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	05		Alpha			6		5
3	05		Alpha			6		4
4	05		Bravo			6
5	05		Bravo			5
6	05		Bravo			5
7	05		Charlie			5
8	03		Charlie			6
9	05		Charlie			6
10	05		Delta			5
11	05		Delta			6
12	05		Echo			5
13	05		Echo			5
14	05		Echo			5
15	05		Echo			7
16	02		Foxtrot			7
17
Data

Cell Formulas
Range	Formula
H2	H2	=SUM(--(FREQUENCY(IF(A2:A16="05",MATCH(C2:C16,C2:C16,0)),ROW(C2:C16)-ROW(C2)+1)>0))
H3	H3	=SUM(--(FREQUENCY(IF((A2:A16="05")*(F2:F16=6),MATCH(C2:C16,C2:C16,0)),ROW(C2:C16)-ROW(C2)+1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.