Sumif with two Criteria

R

Rob

I am using a Sumif formula to return the sum of values less than 50 in
column D which works fine except that I now need it to return less than 50
but greater than 15. Whist the below is an example only, the spreadsheet
Book2 varies in range week over week which is why I have defined the range
$D:$D.

I have tried ">15#AND#<50 in cell B3 but to no avail. Is there a way to
achive this?

Thanks, Rob

=SUMIF([Book2]Sheet1!$D:$D,B3,[Book2]Sheet1!$D:$D)

B3 contains the text "<50
 
R

ryanb.

Rob said:
I am using a Sumif formula to return the sum of values less than 50 in
column D which works fine except that I now need it to return less than 50
but greater than 15. Whist the below is an example only, the spreadsheet
Book2 varies in range week over week which is why I have defined the range
$D:$D.

I have tried ">15#AND#<50 in cell B3 but to no avail. Is there a way to
achive this?

Thanks, Rob

=SUMIF([Book2]Sheet1!$D:$D,B3,[Book2]Sheet1!$D:$D)

B3 contains the text "<50
Click to expand...
=====================
Try:

=SUMPRODUCT(--([Book2]Sheet1!$D$1:$D$30000>15),--([Book2]Sheet1!$D$!:$D$30000<50),([Book2]Sheet1!$D$1:$D$30000))

You will need to replace $D$1:$D$30000 with the proper range if you have
more than 30000 rows or if your data does not start on row 1. SUMPRODUCT
does not allow for entire column references.

HTH

ryanb.
 
T

T. Valko

Try this:

SUMIF is unreliable between files if one of the files is closed so use
SUMPRODUCT:

A3 = 15
B3 = 50

=SUMPRODUCT(--([Book2]Sheet1!D1:D20>A3),--([Book2]Sheet1!D1:D20<B3),[Book2]Sheet1!D1:D20)

Note that with SUMPRODUCT you can't use entire columns (unless you're using
Excel 2007).

Biff
 
Top