sumif criteria rules

[nate]

I'm using the sumif function, but I would like to sum
with in a range of criteria that is between two values.
How can I make the criteria be between a minimum and
maximum value?

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((A1:A100>1)*(A1:A100<20),B1:B100)
this sums column B for all values in which column a is between 1 and 20

 

[Norman Harker]

Hi Nate!

An alternative to Frank's SUMPRODUCT approach is:

=SUMIF(A1:A10,">="&70)-SUMIF(A1:A10,">"&80)

The first SUMIF will total all values greater than or equal to your
minimum, and the second SUMIF will then deduct all values greater than
your maximum.

You need to be careful as to whether you include the minimum / maximum
values in the amounts to be summed. In my example, both values of 70
and 80 will be counted. Usually you count one bound and not the other.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.