## Excel: Count and sum values between specific ranges in a column

*Article updated on September 07, 2007*

Question: Count and sum values between 1 and 3 in column A

Answer: Count numbers between 1 and 3.

=SUMPRODUCT((A1:A15>=1)*(A1:A15<=3))

A1 is above or equal 1? Yes. A1 is below or equal to 3? No. **(1*0=0)**

A2 is above or equal 1? Yes. A2 is below or equal to 3? Yes. **(1*1=1)**

A3 is above or equal 1? No. A3 is below or equal to 3? Yes. **(0*1=0)**

.....

.....

.....

The sum of all of these calculations (bold) will give you the counted number.

Sum numbers between 1 and 3.

=SUMPRODUCT((A1:A15>=1)*(A1:A15<=3)*(A1:A15))

A1 is above or equal 1? Yes. A1 is below or equal to 3? No. **(1*0*8=0)**

A2 is above or equal 1? Yes. A2 is below or equal to 3? Yes. **(1*1*2=2)**

A3 is above or equal 1? No. A3 is below or equal to 3? Yes. **(0*1*0=0)**

.....

.....

.....

The sum of all of these calculations (bold) will give you the sum of the counted numbers.

** SUMPRODUCT(**array1;array2;...**)**

Returns the sum of the products of the corresponding ranges or arrays

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article