Countif with multiple criteria in one row

[Fishstats]

Excel 2007

	A	B	C	D	E	F	G	H	I	J	L	M
1			Name		Alewife - Alosa pseudoharengus	American Eel - Anguliia rostrata	Banded Darter - Etheostoma zonale	Banded Killifish - Fundulus diaphanus	Bigmouth Buffalo - Ictiobus cyprinellus	Black Bullhead - Ameriurus melas	Black Crappie - Pomoxis nigromaculatus	Blackchin Shiner - Notropis heterodon
2	ex	na	Endemism		ex	na	na	na	na	na	na	na
3	Cyp	Cent	Family								Cent	Cyp
4		Lepo	Lepomis
5	site	site_date	zone	rep	PLANK	OMNI	INVERT	INVERT	OMNI	OMNI	PISC	PLANK
6	8	7/5/2012	SAV	3	1	0.45	.66666	0.33333	0.66666	.333333	0.33333333	.25

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Excel 2007.

I need to calculate a percent of the number of fish species that eat "PLANK" "INVERT" or "PISC". so in line 8 there are 5 species that eat one of the options. So the percentage should be 62.5%. Once I have this value I need to convert it to a score so <60% is 0; 60-70% is 3; and >70% is 5. I can get it to score but I can't get the equation to actually count the number of species eating one of those options. The equation I am using is:

=IF((COUNTIFS(Data!$E6:$CX6,">0",Data!$E$5:$CX$5,{"INVERT","PISC","PLANK"})/COUNTIF(Data!$E6:$CX6,">0")>0.7),5,IF((COUNTIFS(Data!$E6:$CX6,">0",Data!$E$5:$CX$5,{"INVERT","PISC","PLANK"})/COUNTIF(Data!$E6:$CX6,">0")<0.6),0,3))

any help on why the countif function will not count all three options would be greatly appreciated.

Thanks in advance.

 

[Ron Coderre]

With your posted data in A1:M6

This regular formula returns the count of Row_5 items that are one of: PLANK, INVERT, or PISC

P1: =SUMPRODUCT(COUNTIF(E5:M5,{"PLANK";"INVERT";"PISC"}))

This regular formula returns the percentage of non-blank cells in Row_5 for that count

Q1: =P1/COUNTA(E5:M5)*100

This regular formula assigns a category to that percentage

R1: =INDEX({0;3;5},MATCH(Q1,{0;60;70.000000001},1))

For your data, these are the results:
P1: 5
Q1: 62.5
R1: 3

Alternatively, you could create lookup lists for the formulas
T1: PLANK
T2: INVERT
T3: PISC

U1: 0
U2: 60
U3: 70.000000001

V1: 0
V2: 3
V3: 5

and the formulas would be:

P1: =SUMPRODUCT(COUNTIF(E5:M5,T1:T3))
Q1: =P1/COUNTA(E5:M5)*100
R1: =INDEX(V1:V3,MATCH(Q1,U1:U3,1))

with the same results.

Is that something you can work with?

 

[Fishstats]

I was hoping to keep it simple and make it a one cell formula. If that's not possible then I will work with what you've shown me.

Thanks!

 

[Ron Coderre]

Like this?:

=INDEX({0;3;5},MATCH(SUMPRODUCT(COUNTIF(E5:M5,{"PLANK";"INVERT";"PISC"}))/COUNTA(E5:M5)*100,{0;60;70.000000001},1))

 

[Fishstats]

Thanks for the quick response. That doesn't seem to work because on occasion there are zeros in rows similar to 6. I probably should have used a better example. To clarify here is a sample that is more common in the data set.

Excel 2007

	A	B	C	D	E	F	G	H	I	J	L
1			Name		Alewife - Alosa pseudoharengus	American Eel - Anguliia rostrata	Banded Darter - Etheostoma zonale	Banded Killifish - Fundulus diaphanus	Bigmouth Buffalo - Ictiobus cyprinellus	Black Bullhead - Ameriurus melas	Black Crappie - Pomoxis nigromaculatus
2	ex	na	Endemism		ex	na	na	na	na	na	na
3	Cyp	Cent	Family								Cent
4		Lepo	Lepomis
5	site	site_date	zone	rep	PLANK	OMNI	INVERT	INVERT	OMNI	OMNI	PISC
7	10	6/30/2011	SAV	3		3.333333	0	3.33333333	0	0	2.23

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

If the equation works the percent should be 66.67% and the score should be 3. Sorry for not being as clear as I could have been.

 

[Ron Coderre]

Ok...I'm happy to help, but I'm confused. "similar to 6"? There's no row_6 in your example.
Using your example, can you describe exactly what should be calculated and which items to include/exclude...and the results?