Count between

[Mike7]

Hi.
This formula:
=SUM(INDEX(($AR$2:$AR$1001=5)*(B2:B1001<11),0,1))
I use to count numbers less than 11 in the range B2:AO1001 and if same row number in the column AR = 5.
How can I count greater than 10 and less than 21 instead 0f <11 ?

Thanks.

 

[Aladin Akyurek]

Hi.
This formula:
=SUM(INDEX(($AR$2:$AR$1001=5)*(B2:B1001<11),0,1))
I use to count numbers less than 11 in the range B2:AO1001 and if same row number in the column AR = 5.
How can I count greater than 10 and less than 21 instead 0f <11 ?

Thanks.

You are invoking a syntax that avoids "confirming with control+shift+enter".

Set up in the normal form, that would be:

=SUM(IF($AR$2:$AR$1001 = 5, IF($B$2:$B$1001 < 11, 1)))

which, as said, requires confirming with control+shift+enter, not just with enter.

The new query:

=SUM(IF($AR$2:$AR$1001 = 5, IF($B$2:$B$1001 > 10, IF($B$2:$B$1001 < 21, 1))))

which you need to confirm with control+shift+enter, not just enter.

 

[Mike7]

Thanks!

 

[T. Valko]

Hi.
This formula:
=SUM(INDEX(($AR$2:$AR$1001=5)*(B2:B1001<11),0,1))
I use to count numbers less than 11 in the range B2:AO1001 and if same row number in the column AR = 5.
How can I count greater than 10 and less than 21 instead 0f <11 ?

Thanks.

What version of Excel are you using?

If you're using Excel 2007 or later...

=COUNTIFS(B2:B1001,">10",B2:B1001,"<21",AR2:AR1001,5)

This one will work in any version of Excel:

=SUMPRODUCT(--(B2:B1001>10),--(B2:B1001<21),--(AR2:AR1001=5))



Better to use cells to hold the criteria:

• A2 = 10
• A3 = 21
• A4 = 5

=COUNTIFS(B2:B1001,">"&A2,B2:B1001,"<"&A3,AR2:AR1001,A4)

=SUMPRODUCT(--(B2:B1001>A2),--(B2:B1001 < A3),--(AR2:AR1001=A4))

 

[Aladin Akyurek]

Thanks!

You are welcome. Thanks for providing feedback.