Count unique text entries with multiple criteria

meowerson · Nov 14, 2014

I am trying to count unique text 'Species' entires in column 'C' on a separate Worksheet (within the same workbook) with multiple criteria. The data are organized as follows (in 'Worksheet'):

Row#	A	B	C
1	Survey	Site	Species
2	1	a	x
3	1	b	y
4	1	b	y
5	2	a	x
6	2	a	x
7	2	b	z
8	3	a	z
9	3	b	x

I have attempted to calculate the number of unique 'Species' during survey '1' at site 'a' using the following formula:

=SUM(IF('Worksheet'!$B$2:$B$9="a")*('Worksheet'!$A$2:$A$9="1", 1/(COUNTIFS('Worksheet'!$B$2:$B$9, "a", 'Worksheet'!$C$2:$C$9, 'Worksheet'!$C$2:$C$9, 'Worksheet'!$C$2:$C$9, "1"))), 0)

but it says there is an error in my formula. Please help!

Thanks,
meowerson

Domenic · Nov 14, 2014

Try...

=SUM(IF((Worksheet!$B$2:$B$9="a")*(Worksheet!$A$2:$A$9=1), 1/(COUNTIFS(Worksheet!$B$2:$B$9,"a",Worksheet!$A$2:$A$9,1,Worksheet!$C$2:$C$9,Worksheet!$C$2:$C$9))))

...confirmed with CONTROL+SHIFT+ENTER. Although, the following should be more efficient...

=SUM(IF(FREQUENCY(IF(Worksheet!A2:A9=1,IF(Worksheet!B2:B9="a",IF(Worksheet!C2:C9<>"",MATCH("~"&Worksheet!C2:C9,Worksheet!C2:C9&"",0)))),ROW(Worksheet!C2:C9)-ROW(Worksheet!C2)+1)>0,1))

...also confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

meowerson · Nov 15, 2014

Hello Domenic,

Thanks very much for your help but neither formula you suggested worked for me. I had simplified my dataset for the purposes of this forum, but perhaps there is something about my data that is causing the problem. Here is a truer summary that I hope will shed some light (the worksheet is called 'PC Breeders' and has 1357 rows including the column heading):

Row #	D	L	M
1	Survey	Site	Species
2	1	ducx01	CBCH
3	1	ducx01	HETH
4	1	sneco02	CBCH
5	1	sneco02	CBCH
6	2	ducx01	AMRO
7	2	ducx01	VATH
8	2	sneco02	AMRO
9	2	sneco02	CBCH
10	3	ducx01	GCKI
11	3	ducx01	GCKI
12	3	ducx01	AMRO
13	3	sneco02	CBCH
14	3	sneco02	CBCH
...
1357	3	sneco02	VATH

Not sure if the combination of text+numbers in column L (site) might be a problem?

Aladin Akyurek · Nov 15, 2014

meowerson said:
Hello Domenic,

Thanks very much for your help but neither formula you suggested worked for me. I had simplified my dataset for the purposes of this forum, but perhaps there is something about my data that is causing the problem. Here is a truer summary that I hope will shed some light (the worksheet is called 'PC Breeders' and has 1357 rows including the column heading):

Row #
D
L
M

1
Survey
Site
Species

2
1
ducx01
CBCH

3
1
ducx01
HETH

4
1
sneco02
CBCH

5
1
sneco02
CBCH

6
2
ducx01
AMRO

7
2
ducx01
VATH

8
2
sneco02
AMRO

9
2
sneco02
CBCH

10
3
ducx01
GCKI

11
3
ducx01
GCKI

12
3
ducx01
AMRO

13
3
sneco02
CBCH

14
3
sneco02
CBCH

...

1357
3
sneco02
VATH

<TBODY>
</TBODY>

Not sure if the combination of text+numbers in column L (site) might be a problem?

When Survey = 3 and Site = ducx01, you expect a species count of 2, right?

meowerson · Nov 15, 2014

Yes, Aladin, that is correct.

Aladin Akyurek · Nov 15, 2014

meowerson said:
Yes, Aladin, that is correct.

I'm sure Domenic proposed the same set up. Control+shift+enter, not just enter:

Rich (BB code):

=SUM(IF(FREQUENCY(IF($D$2:$D$1357=3,IF($L$2:$L$1357="duckx01",
  IF($M$2:$M$1357<>"",MATCH("~"&$M$2:$M$1357,$M$2:$M$1357&"",0)))),
  ROW($M$2:$M$1357)-ROW($M$2)+1),1))

meowerson · Nov 15, 2014

Hi Aladin,

Thanks again, but it's still not working. I get a #VALUE! message. Note that I am referencing cells on a different worksheet and am putting the reference to that worksheet (written as: 'PC Breeders'!) before every range in the formula. Would this affect your suggested formula in any way?

Aladin Akyurek · Nov 15, 2014

meowerson said:
Hi Aladin,

Thanks again, but it's still not working. I get a #VALUE! message. Note that I am referencing cells on a different worksheet and am putting the reference to that worksheet (written as: 'PC Breeders'!) before every range in the formula. Would this affect your suggested formula in any way?

Control+shift+enter ---> Press down the control and the shift keys at the same while you hit the enter key. When done properly, Excel automatically encloses the target formula between curly braces, i.e., between { and }.

István Hirsch · Nov 15, 2014

This is not a single formula, rather a workaround to get the result required. Try it if there is not a more suitable solution available.
Enter in D2:

=A2&B2&c2

Copy col D and paste special in col E as Value, then remove duplicates in this column.
Enter in cell F2:

=COUNTIF($E$2:$E$14;(LEFT(E2;LEN(E2)-4))&"*")

enter in cell G2:

=LEFT(E2;LEN(E2)-4)

Copy col F and G and paste special as Value in col H and I.
Select col H and I, then remove duplicates in col I.

If it works for you, you can find the Survey+Site group in col I, the number of their unique species in col H.
If the data is in different worksheets, change the references accordingly.

István Hirsch · Nov 15, 2014

This is not a single formula, rather a workaround to get the result required. Try it only if there is not a more suitable solution available.
Enter in D2:
=A2&B2&c2
Copy col D and paste special in col E as Value, then remove duplicates in this column.
Enter in cell F2:
=COUNTIF($E$2:$E$14,(LEFT(E2,LEN(E2)-4))&"*")
Enter in cell G2:
=LEFT(E2,LEN(E2)-4)

Copy col F and G and paste special them as Value in col H and I.
Select col H and I, then remove duplicates in col I.
If it works for you, you can find the Survey+Site group in col I, the number of their unique species in col H.
If the data is in different worksheets, change the references accordingly.

Excel Workbook

A

B

C

D

E

F

G

H

I

1

Survey

Site

Species

No of unique species

Version

2

1

ducx01

CBCH

1ducx01CBCH

2

1ducx01

2

1ducx01

3

1

ducx01

HETH

1ducx01HETH

2

1ducx01

1

1sneco02

4

1

sneco02

CBCH

1sneco02CBCH

1

1sneco02

2

2ducx01

5

1

sneco02

CBCH

1sneco02CBCH

2ducx01AMRO

2

2ducx01

2

2sneco02

6

2

ducx01

AMRO

2ducx01AMRO

2ducx01VATH

2

2ducx01

2

3ducx01

7

2

ducx01

VATH

2ducx01VATH

2sneco02AMRO

2

2sneco02

1

3sneco02

8

2

sneco02

AMRO

2sneco02AMRO

2sneco02CBCH

2

2sneco02

9

2

sneco02

CBCH

2sneco02CBCH

3ducx01GCKI

2

3ducx01

10

3

ducx01

GCKI

3ducx01GCKI

3ducx01AMRO

2

3ducx01

11

3

ducx01

GCKI

3ducx01GCKI

3sneco02CBCH

1

3sneco02

12

3

ducx01

AMRO

3ducx01AMRO

13

3

sneco02

CBCH

3sneco02CBCH

14

3

sneco02

CBCH

3sneco02CBCH

Sheet

Count unique text entries with multiple criteria

meowerson

New Member

Excel Facts

Domenic

MrExcel MVP

meowerson

New Member

Aladin Akyurek

MrExcel MVP

meowerson

New Member

Aladin Akyurek

MrExcel MVP

meowerson

New Member

Aladin Akyurek

MrExcel MVP

István Hirsch

Well-known Member

István Hirsch

Well-known Member

Similar threads

Forum statistics

Share this page

Count unique text entries with multiple criteria

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock