Sumif with two Criteria

[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

 

[ryanb.]

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

=====================
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. 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!D120>A3),--([Book2]Sheet1!D120<B3),[Book2]Sheet1!D120)

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

Biff

 

[Rob]

Thanks for the quick replies, Sumproduct works a treat.