Countif(index(match row and column criteria

C

canuduh

New Member
Joined
Nov 12, 2013
Messages
2
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

[TABLE="width: 500"]
<tbody>[TR="bgcolor: #CACACA"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Name2[/TD]
[TD="align: center"]Name1[/TD]
[TD="align: center"]Name4[/TD]
[TD="align: center"]Name3[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]Name1[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]41[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]41[/TD]
[TD="align: center"]64[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]94[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: center"]Yellow[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]87[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"]72[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]81[/TD]
[TD="align: center"]87[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Thanks!
 
Sort by date Sort by votes
canuduh said:
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

[TABLE="width: 500"]
<tbody>[TR="bgcolor: #CACACA"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Name2[/TD]
[TD="align: center"]Name1[/TD]
[TD="align: center"]Name4[/TD]
[TD="align: center"]Name3[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]41[/TD]
[TD="align: center"]47[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]41[/TD]
[TD="align: center"]64[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]94[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]77[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]59[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: center"]Yellow[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]87[/TD]
[TD="align: center"]65[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"]72[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]24[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]81[/TD]
[TD="align: center"]87
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!
Click to expand...
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> [TABLE="width: 325"]
<tbody>[TR]
[TD="class: xl63, width: 65"][/TD]
[TD="class: xl63, width: 65"]Name1[/TD]
[TD="class: xl63, width: 65"]Name2[/TD]
[TD="class: xl63, width: 65"]Name3[/TD]
[TD="class: xl63, width: 65"]Name4[/TD]
[/TR]
[TR]
[TD="class: xl63"]Red[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]Blue[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]Orange[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Yellow[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
</tbody>[/TABLE]

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))
 
Last edited:
Upvote 0
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!!!
 
Upvote 0
canuduh said:
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!!!
Click to expand...

Thanks for the feedback
Glad you found your way.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
conditional formatting question
Replies
2
Views
691
kerm007
K
U
  • Solved
Index formula with matching - Can it look at rows and columns?
Replies
2
Views
466
usnapoleon
U
C
  • Solved
index match excel formula to vba code
Replies
9
Views
623
DanteAmor
DanteAmor
B
  • Question Question
Send email to specific name that meets weeknumber criteria
Replies
0
Views
187
Birdy01962
B
ExcelNewbie2020
  • Solved
sum function that will reset the running total every after 5 counts
Replies
3
Views
328
Cubist
Cubist

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top