rankifs with multiple criteria

[nuest12]

<tbody>
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C [/TD]

[TD="align: right"] 10[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]8200[/TD]
[TD="align: right"][/TD]

[TD="align: right"] 2[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]7000[/TD]

[TD="align: right"] 11[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]12000[/TD]
[TD="align: right"][/TD]

[TD="align: right"] 1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]6800[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]14000[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]6600[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]

[TD="align: right"]17[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]11000[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]10000[/TD]

</tbody>

I would like to rank the values in column c based on a few criteria from columns A and B. If column A is greater than 9 and column B is greater than 97 then rank column C accordingly. I used the equation =IF($A$2>A2,"",RANK(C2,$C$2:$C$11,0)) in column D and it only ranked the criteria I wanted but it ranked them based on all values. Here were my results:

<tbody>
[TD="align: right"]10[/TD]
[TD="width: 64, align: right"]97[/TD]
[TD="width: 64, align: right"]8200[/TD]
[TD="width: 64, align: right"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]7000[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]12000[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]6800[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]14000[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]6600[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"]17[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]11000[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]10000[/TD]

</tbody>

I would like to have only those 5 cells ranked but ranked 1-5 excluding the other cells that dont meet the criteria.
Thanks

 

[DRSteele]

This will achieve your objective. Copy formula in D4 downwards.

I<c$4:c$13))+1,"")
<c$4:c$13))+1,"")
<c$4:c$13))+1,"")
<c$4:c$13))+1,"")[ code]
note that your query seems confused: I gather you wish to rank Column C when Column A is greater than or equal to 9 AND when Column B is greater than or equal to 97. That means there are only four items in your data that qualify, not five.

Watch this video for illumination: https://www.youtube.com/watch?v=rCeTG2k2T6I

	A	B	C	D

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]Criteria to rank data[/TD]
[TD="bgcolor: #FCE4D6, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FCE4D6, align: right"]9[/TD]
[TD="bgcolor: #FCE4D6, align: right"]97[/TD]
[TD="align: right"][/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"]10[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]8200[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]7000[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]12000[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]6800[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]4000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]14000[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]6600[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]5000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]3[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]11000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]10000[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

</tbody>

Sheet24​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=IF(AND(A4>=$A$2,B4>=$B$2),SUMPRODUCT(--($A$4:$A$13>=$A$2),--($B$4:$B$13>=$B$2),--(C4<C$4:C$13))+1,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

<strike></strike>
</c$4:c$13))+1,"")[></c$4:c$13))+1,"")
</c$4:c$13))+1,"")
</c$4:c$13))+1,"")

 

[DRSteele]

The system is not adding formulas properly! It is cutting things off.

D4 should be this: just replace the '__less than symbol ___' part with <

IF(AND(A4>=$A$2,B4>=$B$2),SUMPRODUCT(--($A$4:$A$13>=$A$2),--($B$4:$B$13>=$B$2),

--(C4<c$4:c$13))+1,"")
__less than symbol ___</c$4:c$13))+1,"")
C$4:C$13))+1,"")<c$4:c$13))+1,"")<c$4:c$13))+1,"")< html=""></c$4:c$13))+1,"")<c$4:c$13))+1,"")<>

 

[Eric W]

DRSteele: the system tries to interpret < and > symbols as HTML tags. To get around that, just put a space before and behind either of those. Once I saw your correction, I got your version to work.

Here's an alternative that I came up with:
Excel 2010

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #00B050"]A[/TD]
[TD="bgcolor: #00B050"]B[/TD]
[TD="bgcolor: #00B050"]C[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]8200[/TD]
[TD="bgcolor: #FFC000, align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]7000[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]12000[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]6800[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]4000[/TD]
[TD="bgcolor: #FFC000, align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]14000[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]6600[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]5000[/TD]
[TD="bgcolor: #FFC000, align: right"]3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]11000[/TD]
[TD="bgcolor: #FFC000, align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]10000[/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IFERROR(1/(1/SUM(IF(AND(A2>=9,B2>=97),IF(C2<=IF(--($A$2:$A$11>9)*--($B$2:$B$11>=97),$C$2:$C$11,-1),1),0))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

EricW, thanks for the insight. But my formula and yours are full of <> symbols - why did it pick on that particular one? It has happened here before so it seems fickle and without pattern. PS Your formula here is cool.

 

[Eric W]

It is a bit sporadic. I think it depends on what character follows the < . < C might be a predefined tag, but < B is not. I haven't had any issues since I started using the HTML maker, but since you're using the same one, I don't know what to say about that.

Thanks for the nice words about my formula. I think both of ours are similar in concept, but I'd probably give yours the edge since it's not an array formula and doesn't use the 1/1/ construct (simpler is usually better). Not to mention you got yours up first!

 

[nuest12]

Thanks for the help guys. Both of the equations worked great. I am working on a spreadsheet that is taking corn yield data from all over the country and trying to find a good way of analyzing that data. I would like to find the average yield of each of these hybrids without the max and min yields to try and remove some outliers. [TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]Criteria to rank data[/TD]
[TD="bgcolor: #FCE4D6, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="bgcolor: #FCE4D6, align: right"]9
[/TD]
[TD="bgcolor: #FCE4D6, align: right"]97
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]A
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]97
[/TD]
[TD="align: right"]8200
[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]b
[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]7000[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[/TR]
[TR]
[TD="align: center"]b
[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]12000[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[/TR]
[TR]
[TD="align: center"]a
[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]6800[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[/TR]
[TR]
[TD="align: center"]b
[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]4000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]a
[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]14000[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[/TR]
[TR]
[TD="align: center"]a
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]6600[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[/TR]
[TR]
[TD="align: center"]b
[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]5000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]b
[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]11000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]b
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]10000[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[/TR]
</tbody>[/TABLE]
a 6 95 10000
I used the same data but added "a" and "b" to represent two different hybrids. I would like to find an average of each of these hybrids without the max and min yield. The yield column would be the values in column c. I realize that we need to shift all of our columns to the right and call the "a" and "b" column a. It would also be interesting to find the average of "a" and "b" without the max and min value, if column a is less than 9 and column b is greater than 97. Thanks for the help. I am just digging into excel so I apologize for the lack of experience

 

[DRSteele]

This incorporates all of the above requirements.

	A	B	C	D	E	F	G	H
Bee	a	a
Cee	b	b
DRS	EricW
Hybrid	Bee	Cee	Yield	Rank of Yield	Rank of Yield
a
b
b
a
b
a
a
b
b
b
a

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]Criteria to rank yield[/TD]
[TD="bgcolor: #FCE4D6, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]Average yield[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]Average yield w/ Criteria[/TD]
[TD="align: right"][/TD]

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

[TD="bgcolor: #FCE4D6, align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #C6E0B4, align: right"] 9,120.0 [/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #C6E0B4, align: right"] 8,200.0 [/TD]

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

[TD="bgcolor: #FCE4D6, align: right"]97[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #C6E0B4, align: right"] 8,166.7 [/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #C6E0B4, align: right"] 6,666.7 [/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: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

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

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

[TD="align: right"]10[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"] 8,200 [/TD]
[TD="bgcolor: #E2EFDA, align: right"]2[/TD]
[TD="bgcolor: #D9E1F2, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]2[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"] 7,000 [/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]11[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"] 12,000 [/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"] 6,800 [/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]13[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"] 4,000 [/TD]
[TD="bgcolor: #E2EFDA, align: right"]4[/TD]
[TD="bgcolor: #D9E1F2, align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]3[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"] 14,000 [/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]4[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"] 6,600 [/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]15[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"] 5,000 [/TD]
[TD="bgcolor: #E2EFDA, align: right"]3[/TD]
[TD="bgcolor: #D9E1F2, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]17[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"] 11,000 [/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]
[TD="bgcolor: #D9E1F2, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]2[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"] 10,000 [/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]6[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"] 10,000 [/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

rankif​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=AVERAGEIFS($D$7:$D$17,$A$7:$A$17,D2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=AVERAGEIFS($D$7:$D$17,$A$7:$A$17,D3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=AVERAGEIFS($D$7:$D$17,$A$7:$A$17,G2,$B$7:$B$17,">="&$B$2,$C$7:$C$17,">="&$B$3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]=AVERAGEIFS($D$7:$D$17,$A$7:$A$17,G3,$B$7:$B$17,">="&$B$2,$C$7:$C$17,">="&$B$3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E7[/TH]
[TD="align: left"]=IF(AND(B7>=$B$2,C7>=$B$3),SUMPRODUCT(--($B$7:$B$17>=$B$2),--($C$7:$C$17>=$B$3),--(D7 < D$7:D$17))+1,"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E8[/TH]
[TD="align: left"]=IF(AND(B8>=$B$2,C8>=$B$3),SUMPRODUCT(--($B$7:$B$17>=$B$2),--($C$7:$C$17>=$B$3),--(D8 < D$7:D$17))+1,"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E9[/TH]
[TD="align: left"]=IF(AND(B9>=$B$2,C9>=$B$3),SUMPRODUCT(--($B$7:$B$17>=$B$2),--($C$7:$C$17>=$B$3),--(D9 < D$7:D$17))+1,"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E10[/TH]
[TD="align: left"]=IF(AND(B10>=$B$2,C10>=$B$3),SUMPRODUCT(--($B$7:$B$17>=$B$2),--($C$7:$C$17>=$B$3),--(D10 < D$7:D$17))+1,"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E11[/TH]
[TD="align: left"]=IF(AND(B11>=$B$2,C11>=$B$3),SUMPRODUCT(--($B$7:$B$17>=$B$2),--($C$7:$C$17>=$B$3),--(D11 < D$7:D$17))+1,"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E12[/TH]
[TD="align: left"]=IF(AND(B12>=$B$2,C12>=$B$3),SUMPRODUCT(--($B$7:$B$17>=$B$2),--($C$7:$C$17>=$B$3),--(D12 < D$7:D$17))+1,"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E13[/TH]
[TD="align: left"]=IF(AND(B13>=$B$2,C13>=$B$3),SUMPRODUCT(--($B$7:$B$17>=$B$2),--($C$7:$C$17>=$B$3),--(D13 < D$7:D$17))+1,"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E14[/TH]
[TD="align: left"]=IF(AND(B14>=$B$2,C14>=$B$3),SUMPRODUCT(--($B$7:$B$17>=$B$2),--($C$7:$C$17>=$B$3),--(D14 < D$7:D$17))+1,"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E15[/TH]
[TD="align: left"]=IF(AND(B15>=$B$2,C15>=$B$3),SUMPRODUCT(--($B$7:$B$17>=$B$2),--($C$7:$C$17>=$B$3),--(D15 < D$7:D$17))+1,"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E16[/TH]
[TD="align: left"]=IF(AND(B16>=$B$2,C16>=$B$3),SUMPRODUCT(--($B$7:$B$17>=$B$2),--($C$7:$C$17>=$B$3),--(D16 < D$7:D$17))+1,"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E17[/TH]
[TD="align: left"]=IF(AND(B17>=$B$2,C17>=$B$3),SUMPRODUCT(--($B$7:$B$17>=$B$2),--($C$7:$C$17>=$B$3),--(D17 < D$7:D$17))+1,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F7[/TH]
[TD="align: left"]{=IFERROR(1/(1/SUM(IF(AND(B7>=$B$2,C7>=$B$3),IF(D7<=IF(--($B$7:$B$17>=$B$2)*--($C$7:$C$17>=$B$3),$D$7:$D$17,-1),1),0))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F8[/TH]
[TD="align: left"]{=IFERROR(1/(1/SUM(IF(AND(B8>=$B$2,C8>=$B$3),IF(D8<=IF(--($B$7:$B$17>=$B$2)*--($C$7:$C$17>=$B$3),$D$7:$D$17,-1),1),0))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F9[/TH]
[TD="align: left"]{=IFERROR(1/(1/SUM(IF(AND(B9>=$B$2,C9>=$B$3),IF(D9<=IF(--($B$7:$B$17>=$B$2)*--($C$7:$C$17>=$B$3),$D$7:$D$17,-1),1),0))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F10[/TH]
[TD="align: left"]{=IFERROR(1/(1/SUM(IF(AND(B10>=$B$2,C10>=$B$3),IF(D10<=IF(--($B$7:$B$17>=$B$2)*--($C$7:$C$17>=$B$3),$D$7:$D$17,-1),1),0))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F11[/TH]
[TD="align: left"]{=IFERROR(1/(1/SUM(IF(AND(B11>=$B$2,C11>=$B$3),IF(D11<=IF(--($B$7:$B$17>=$B$2)*--($C$7:$C$17>=$B$3),$D$7:$D$17,-1),1),0))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F12[/TH]
[TD="align: left"]{=IFERROR(1/(1/SUM(IF(AND(B12>=$B$2,C12>=$B$3),IF(D12<=IF(--($B$7:$B$17>=$B$2)*--($C$7:$C$17>=$B$3),$D$7:$D$17,-1),1),0))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F13[/TH]
[TD="align: left"]{=IFERROR(1/(1/SUM(IF(AND(B13>=$B$2,C13>=$B$3),IF(D13<=IF(--($B$7:$B$17>=$B$2)*--($C$7:$C$17>=$B$3),$D$7:$D$17,-1),1),0))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F14[/TH]
[TD="align: left"]{=IFERROR(1/(1/SUM(IF(AND(B14>=$B$2,C14>=$B$3),IF(D14<=IF(--($B$7:$B$17>=$B$2)*--($C$7:$C$17>=$B$3),$D$7:$D$17,-1),1),0))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F15[/TH]
[TD="align: left"]{=IFERROR(1/(1/SUM(IF(AND(B15>=$B$2,C15>=$B$3),IF(D15<=IF(--($B$7:$B$17>=$B$2)*--($C$7:$C$17>=$B$3),$D$7:$D$17,-1),1),0))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F16[/TH]
[TD="align: left"]{=IFERROR(1/(1/SUM(IF(AND(B16>=$B$2,C16>=$B$3),IF(D16<=IF(--($B$7:$B$17>=$B$2)*--($C$7:$C$17>=$B$3),$D$7:$D$17,-1),1),0))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F17[/TH]
[TD="align: left"]{=IFERROR(1/(1/SUM(IF(AND(B17>=$B$2,C17>=$B$3),IF(D17<=IF(--($B$7:$B$17>=$B$2)*--($C$7:$C$17>=$B$3),$D$7:$D$17,-1),1),0))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[nuest12]

Thanks again for the help. I was hoping to come up with some sort of olympic averageifs statement. Finding the average of column c without the min and max values considering all the criteria we have set for columns a, b, and d. Thanks again