Countif with multiple criteria in one row

[Fishstats]

Excel 2007

	A	B	C	D	E	F	G	H	I	J	L	M
Name	Alewife - Alosa pseudoharengus	American Eel - Anguliia rostrata	Banded Killifish - Fundulus diaphanus	Bigmouth Buffalo - Ictiobus cyprinellus	Black Bullhead - Ameriurus melas	Black Crappie - Pomoxis nigromaculatus	Blackchin Shiner - Notropis heterodon
ex	na	Endemism	ex	na	na	na	na	na	na	na
Cyp	Cent	Family				Cent	Cyp
Lepo	Lepomis
site	site_date	zone	rep
SAV

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="bgcolor: #FFFF00"]Banded Darter - Etheostoma zonale[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]PLANK[/TD]
[TD="align: center"]OMNI[/TD]
[TD="align: center"]INVERT[/TD]
[TD="align: center"]INVERT[/TD]
[TD="align: center"]OMNI[/TD]
[TD="align: center"]OMNI[/TD]
[TD="align: center"]PISC[/TD]
[TD="align: center"]PLANK[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7/5/2012[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]0.45
[/TD]
[TD="align: right"].66666
[/TD]
[TD="align: right"]0.33333
[/TD]
[TD="align: right"]0.66666
[/TD]
[TD="align: right"].333333
[/TD]
[TD="align: right"]0.33333333[/TD]
[TD="align: right"].25
[/TD]

</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
Name	Alewife - Alosa pseudoharengus	American Eel - Anguliia rostrata	Banded Killifish - Fundulus diaphanus	Bigmouth Buffalo - Ictiobus cyprinellus	Black Bullhead - Ameriurus melas	Black Crappie - Pomoxis nigromaculatus
ex	na	Endemism	ex	na	na	na	na	na	na
Cyp	Cent	Family				Cent
Lepo	Lepomis
site	site_date	zone	rep
SAV

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="bgcolor: #FFFF00"]Banded Darter - Etheostoma zonale[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]PLANK[/TD]
[TD="align: center"]OMNI[/TD]
[TD="align: center"]INVERT[/TD]
[TD="align: center"]INVERT[/TD]
[TD="align: center"]OMNI[/TD]
[TD="align: center"]OMNI[/TD]
[TD="align: center"]PISC[/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6/30/2011[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3.333333
[/TD]
[TD="align: right"]0
[/TD]
[TD="align: right"]3.33333333[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2.23
[/TD]

</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?