Countif(index(match row and column criteria

[canuduh]

I am looking for a formula to count number of instances where data >80 if the column = Name1 and row = Red
Thought it would be this formula, but it is not calculating the correct answer 2. It appears to be only pulling the 1st match in C2.

cell I1 =COUNTIF(INDEX(B2:E9,MATCH(H1,A2:A9,0),MATCH(G1,B1:E1,0)),">80")



Sheet1

*	A	B	C	D	E	F	G	H	I
1		Name2	Name1	Name4	Name3	_	Red	Name1	2
2	Red	27	90	41	47	_	_	_
3	Blue	41	64	27	94	_	_	_
4	Orange	69	15	28	77	_	_	_
5	Red	82	80	18	14	_	_	_
6	Blue	60	30	80	59	_	_	_
7	Yellow	48	20	87	65	_	_	_
8	Orange	72	34	25	50	_	_	_
9	Red	56	82	82	24	_	_	_
10	Blue	88	44	81	87	_	_	_

<tbody>
</tbody>

Thanks!

 

[cyrilbrd]

I am looking for a formula to count number of instances where data >80 if the column = Name1 and row = Red
Thought it would be this formula, but it is not calculating the correct answer 2. It appears to be only pulling the 1st match in C2.

cell I1 =COUNTIF(INDEX(B2:E9,MATCH(H1,A2:A9,0),MATCH(G1,B1:E1,0)),">80")



Sheet1

*	A	B	C	D	E
1		Name2	Name1	Name4	Name3
2	Red	27	90	41	47
3	Blue	41	64	27	94
4	Orange	69	15	28	77
5	Red	82	80	18	14
6	Blue	60	30	80	59
7	Yellow	48	20	87	65
8	Orange	72	34	25	50
9	Red	56	82	82	24
10	Blue	88	44	81	87

<tbody>
</tbody>

Thanks!

Hi,
Not sure if this is what you would like:

Given your data in A1.
In A15:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; }</style>

	Name1	Name2	Name3	Name4
Red	3	1	0	1
Blue	0	1	2	2
Orange	0	0	0	0
Yellow	0	0	0	1

<tbody>
</tbody>

would count occurrences of values equal and superior to 80 per color per name category.
Formula in B16 is =SUMPRODUCT(($A$2:$A$10=$A16)*($B$1:$E$1=B$15)*($B$2:$E$10>=80))

 

[canuduh]

I see what you did putting into a new table. Not exactly what I was trying to do by solving for each, but you did have the right idea with the formula. I was able to modify your formula a bit to solve my problem. Thank you very much for you help!!!

 

[cyrilbrd]

I see what you did putting into a new table. Not exactly what I was trying to do by solving for each, but you did have the right idea with the formula. I was able to modify your formula a bit to solve my problem. Thank you very much for you help!!!

Thanks for the feedback
Glad you found your way.