Formula for incidence frequency based on value in another column

[mrpablissimo]

Hello,

I do bird surveys and I gather a lot of data. I am trying to calculate the number of point counts on which a species is present.

My data looks like this:

I am trying to find the number of times a sample station (column A), has at least one instance of a species (column E). Or put otherwise, for a value in column E, how many distinct values are there in column A?

I think I am looking at a SumIf formula based on distinct values, but I'm new to anything beyond very basic excel formulas. Any help is appreciated.

 

[StephenCrump]

Welcome to the Forum!

Like this perhaps:

	A	B	C	D	E
1	Station				Species
2	1				B-CONI
3	1				B-DUFL
4	1				B-MOBL
5	1				B-VESP
6	1				B-WWPE
7	1				B-MOBL
8	1				B-WETA
9	1				B-WWPE
10	1				B-WWPE
11	1				B-VESP
12	2				B-MOBL
13	2				B-WETA
14	2				B-CONI
15	2				B-DUFL
16	3				B-CONI
17
18	Species	Stations
19	B-CONI	3
Sheet1

Cell Formulas
Range	Formula
B19	B19	=SUM(--(FREQUENCY(IF(E2:E16=A19,MATCH(A2:A16,A2:A16,)),ROW(A2:A16)-ROW(A2)+1)>0))

 

[mrpablissimo]

Hi Stephen,

Thanks for your help. When I tried this into my document, adjusting all the ranges appropriately, I always get the value "1" or an #value error message. I thought it may be an issue on my end, so I copied exactly the format on your screenshot but to the same result. But yours clearly is working on that end. Could it be an excel version issue with the formula? Something else?

 

[StephenCrump]

Sorry, yes it's a version issue. I'm using Excel 365.

You'll need to array-enter this formula, i.e. CTRL-SHIFT-ENTER, rather than just ENTER.

 

[mrpablissimo]

Awesome, thanks! It works well for me now.

 

[StephenCrump]

That's good to hear, thanks. I'm glad we could help.

 

[bebo021999]

Alternative solution, including unique species list extract:

Book1
	A	B	C	D	E
1	Station				Species
2	1				B-CONI
3	1				B-DUFL
4	1				B-MOBL
5	1				B-VESP
6	1				B-WWPE
7	1				B-MOBL
8	1				B-WETA
9	1				B-WWPE
10	1				B-WWPE
11	1				B-VESP
12	2				B-MOBL
13	2				B-WETA
14	2				B-CONI
15	2				B-DUFL
16	3				B-CONI
17
18	Species	Stations
19	B-CONI	3
20	B-DUFL	2
21	B-MOBL	2
22	B-VESP	1
23	B-WWPE	1
24	B-WETA	2
25
26
27
28
Sheet1

Cell Formulas
Range	Formula
A19:A28	A19	=IFERROR(INDEX($E$2:$E$16,MATCH(0,INDEX(COUNTIF($A$18:A18,$E$2:$E$16),),0)),"")
B19:B28	B19	=IF(A19="","",SUM(IF($E$2:$E$16=A19,1/COUNTIFS($E$2:$E$16,A19,$A$2:$A$16,$A$2:$A$16))))
Press CTRL+SHIFT+ENTER to enter array formulas.