Count unique if criteria is met

**mishelc** · 07-06-2015, 06:53 PM

I'm trying to count the number of unique values in a column if a certain name is present in another column.

Example:

Column A Column B
Joanna ECO60610
Joanna ECO60610
Joanna ECO60610
Ilona ECO56003
Sylwia ECO59237
Sylwia ECO59237
Sylwia ECO59237
Sylwia ECO59237
Sylwia ECO59237
Sylwia ECO59237
Marta ECO59824
Marta ECO59824
Ilona ECO60593
Ilona ECO59298

For Joanna, the result would be 1
For Ilona, the result would be 3
For Sylwia, the result would be 1
For Marta, the result would be 1

I've been using the following function for the unique values: =Sum(1/COUNTIF(B:B,B:B)) validated by Ctrl+Shift+Enter
The only way I could think of to do this is to split up the function by having an additional hidden column C with =SUM(1/COUNTIF(B:B,B#) and then in another cell, =SUMIFS(C#:C#,A#:A#,NAME)

Can anyone help with combining this into one function?

Thank you

**AlKey** · 07-06-2015, 07:58 PM

Try this formula

=SUMPRODUCT(($A$2:$A$15=D2)/COUNTIF($B$2:$B$15,$B$2:$B$15&""))

Row\Col	A	B	C	D	E
2	Joanna	ECO60610		Joanna	1
3	Joanna	ECO60610		Ilona	3
4	Joanna	ECO60610		Sylwia	1
5	Ilona	ECO56003		Marta	1
6	Sylwia	ECO59237
7	Sylwia	ECO59237
8	Sylwia	ECO59237
9	Sylwia	ECO59237
10	Sylwia	ECO59237
11	Sylwia	ECO59237
12	Marta	ECO59824
13	Marta	ECO59824
14	Ilona	ECO60593
15	Ilona	ECO59298

**Tony Valko** · 07-06-2015, 08:00 PM

Try this...

Data Range

	A	B	C	D	E
1	------	------	------	------	------
2	Joanna	ECO60610		Joanna	1
3	Joanna	ECO60610		Ilona	3
4	Joanna	ECO60610		Sylwia	1
5	Ilona	ECO56003		Marta	1
6	Sylwia	ECO59237
7	Sylwia	ECO59237
8	Sylwia	ECO59237
9	Sylwia	ECO59237
10	Sylwia	ECO59237
11	Sylwia	ECO59237
12	Marta	ECO59824
13	Marta	ECO59824
14	Ilona	ECO60593
15	Ilona	ECO59298

This array formula** entered in E2 and copied down:

=SUM(IF(FREQUENCY(IF(A$2:A$15=D2,MATCH(B$2:B$15,B$2:B$15,0)),ROW(B$2:B$15)-ROW(B$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

EDIT: Assumes no empty cells within the data range.

**Tony Valko** · 07-06-2015, 08:04 PM

Originally Posted by AlKey

Try this formula

=SUMPRODUCT(($A$2:$A$15=D2)/COUNTIF($B$2:$B$15,$B$2:$B$15&""))

Try that formula on this data:

Data Range

	A	B
1	------	------
2	Joanna	ECO60610
3	Joanna	ECO60610
4	Joanna	ECO59237
5	Ilona	ECO56003
6	Sylwia	ECO59237
7	Sylwia	ECO59237
8	Sylwia	ECO59237
9	Sylwia	ECO59237
10	Sylwia	ECO59237
11	Sylwia	ECO59237
12	Marta	ECO59824
13	Marta	ECO59824
14	Ilona	ECO60593
15	Ilona	ECO59298

**mishelc** · 07-07-2015, 11:26 AM

Thank you very much to both of you.

Both formulas work on the data I provided but there's issue with the =SUMPRODUCT(($A$2:$A$15=D2)/COUNTIF($B$2:$B$15,$B$2:$B$15&"")) if you change the value in B4 to match B6. Why is that?

**AlKey** · 07-07-2015, 12:00 PM

It should work now.

=SUMPRODUCT((A$2:A$15=D2)/COUNTIFS(A$2:A$15,A$2:A$15,B$2:B$15,B$2:B$15&""))

**Tony Valko** · 07-07-2015, 05:05 PM

Just some FYI...

The SUM(IF(FREQUENCY array version is more efficient (faster to calculate) especially on large data sets.

Count unique if criteria is met

LinkBack

Thread Tools

Rate This Thread

Display

Count unique if criteria is met

Re: Count unique if criteria is met

Re: Count unique if criteria is met

Re: Count unique if criteria is met

Re: Count unique if criteria is met

Re: Count unique if criteria is met

Re: Count unique if criteria is met

Thread Information

Users Browsing this Thread

Similar Threads

Count Unique with Criteria

[SOLVED] Count unique entries based off unique criteria in another column

[SOLVED] COUNT IF UNIQUE VALUE and CRITERIA

[SOLVED] Unique Count with Criteria

[SOLVED] changing a current mod (count unique visible cells ->count unique visible cells criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions