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.

5	1.043	1.2
8	1.2	1.0235
3	1.023	1.2
21	1.2	1.2
17	0.9909	1.2
6	1.2	0.9915
10	1.2	1.0255
12	1.2	0.9075
14	1.2	0.9505
18	0.9770	0.9694

<tbody>
</tbody>

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">