COUNTIFS function with Criteria as a Cell Reference Range

[rohanp16]

I'm trying to use a COUNTIFS function for multiple conditions, one of which is to not count values within a certain cell range (C2:C11) when those values are lesser than those in the adjacent column (i.e., D2:D11), however this doesn't seem to be working.

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]5[/TD]
[TD]1.043[/TD]
[TD]1.2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1.2[/TD]
[TD]1.0235[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1.023[/TD]
[TD]1.2[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]1.2[/TD]
[TD]1.2[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]0.9909[/TD]
[TD]1.2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1.2[/TD]
[TD]0.9915[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1.2[/TD]
[TD]1.0255[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1.2[/TD]
[TD]0.9075[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]1.2[/TD]
[TD]0.9505[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]0.9770[/TD]
[TD]0.9694[/TD]
[/TR]
</tbody>[/TABLE]

The whole formula I'm using (with the other conditions initially, that work fine) is as follows,

=COUNTIFS(B2:B11,">=16",B2:B11,"<=20",C2:C11,"<1.2",C2:C11,"<"&D2:D11)

which returns the value of 2 but the correct answer is 1 (last row should be excluded).

Any idea what I'm doing incorrectly ? Thank you for looking !

 

[AlanY]

try this subproduct() alternative

=SUMPRODUCT((B2:B11 >= 16)*(B2:B11 <= 20)*(C2:C11 < 1.2)*(C2:C11 < D2:D11))

 

[Aladin Akyurek]

Try like this:

=SUMPRODUCT(--(B2:B11>=16),--(B2:B11<=20),--(C2:C11 < 1.2),--(C2:C11 < D2:D11))<d2:d11))< html=""></d2:d11))<>

 

[AlanY]

i believe excel's countifs() function cannot deal with an array as criteria such as "<"&D2:D11 in your formula.

 

[rohanp16]

Thank you AlanY and Aladin, that worked like a charm !

Following on, I tried to average those values with similar criteria but it seems that AVERAGEIFS also cannot handle the "<D2:D11" either. However, I used SUMPRODUCT to calculate the average (i.e., SUM/COUNT) and it works correctly.

For anyone else reading, a handy website for the SUMPRODUCT function,

https://www.ablebits.com/office-addins-blog/2016/08/24/excel-sumproduct-function-formula-examples/

Cheers for the prompt help both of you !

 

[Aladin Akyurek]

Thank you AlanY and Aladin, that worked like a charm !

Following on, I tried to average those values with similar criteria but it seems that AVERAGEIFS also cannot handle the "<d2:d11" i="" correctly.

For anyone else reading, a handy website for the SUMPRODUCT function,

https://www.ablebits.com/office-addins-blog/2016/08/24/excel-sumproduct-function-formula-examples/

Cheers for the prompt help both of you !

It's by design the case that the range-processing functions like COUNTIF(S), SUMIF(S), and AVERAGE(S) do not process array-evaluations like C2:C11 < D2:D11 >> {TRUE;TRUE;FALSE;...}.

By the way, this site contains zillions of SUMPRODUCT applications, including a first write up: http://www.mrexcel.com/forum/hall-f...s-explanation-condition-driven-computing.html

</d2:d11">