Referencing two (or more) cell values in formula

[willcull]

Hi,

Can anyone tell me the correct syntax for referencing two cells as a
criterion in a formula.

For example if I want to sum cells in b1:b5 if cells a1:a5 are greater
than the value in c2 I would write the following:

=sumif(a1:a5,">"&c2,b1:b5)

If I want to sum b1:b5 if cells in a1:a5 are greater than the value in
c2 and less than c3 what should I write?

=sumif(a1:a5,and(">"&c2,"<"&c3),b1:b5) this doesn't work

and neither does this....

=sumif(a1:a5,and(>c2,<c3),b1:b5).

Any help gratefully received.

Thanks,

Will

 

[az-willie]

Hi,

Can anyone tell me the correct syntax for referencing two cells as a
criterion in a formula.

For example if I want to sum cells in b1:b5 if cells a1:a5 are greater
than the value in c2 I would write the following:

=sumif(a1:a5,">"&c2,b1:b5)

If I want to sum b1:b5 if cells in a1:a5 are greater than the value in
c2 and less than c3 what should I write?

=sumif(a1:a5,and(">"&c2,"<"&c3),b1:b5) this doesn't work

and neither does this....

=sumif(a1:a5,and(>c2,<c3),b1:b5).

Any help gratefully received.

Thanks,

Will

====================
=IF(AND(first condition),(second condition)),then,else)

 

[Bob Phillips]

=SUMPRODUCT(--(A1:A5>C2),--(A1:A5<C3),B1:B5)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)