# Number of values within each closed interval

1. ## Number of values within each closed interval

Hi,

I'm trying to count the number of values within each closed interval (such that the upper and lower limits are included as well), however it yields 0.
Please find attached the Excel Spreadsheet with my attempt below, including the expected results as well.

``Please Login or Register  to view this content.``
This should give 2, since there are 2 values from rows 1-5 Columns A-L within that closed interval on A17 and B17.

2. ## Re: Number of values within each closed interval

COUNTIFS requires a 2D array, not 3D.

Try this:

=SUMPRODUCT((\$A\$1:\$L\$5>=A17)*(\$A\$1:\$L\$5<=B17))

3. ## Re: Number of values within each closed interval

Nice one, it works! Thanks a lot for your quick reply, much appreciated! Rep+

4. ## Re: Number of values within each closed interval

Pleasure. Thanks for the rep.

5. ## Re: Number of values within each closed interval

=COUNTIFS(\$A\$1:\$L\$5,">=" & \$A17,\$A\$1:\$L\$5,"<="& \$B17)

Wrong quotation marks highlighted in red in your formula

6. ## Re: Number of values within each closed interval

Thanks a lot! Spot on! You're absolutely right, nice one! Rep+

There are currently 1 users browsing this thread. (0 members and 1 guests)