SUMIF Command

[pimpger]

Hi,

Is is possibe to have a SUMIF with two citeria?

For instance to SUMIF when a range fall in between two values?

Like SUMIF (A1:A7, "AND(>x,<x+5)",B1:B7)??

I've tried and it doesn't seem to work.

Any Suggestions???

P.S. I'm trying to do this with dates, so far I 've tried by converting the dates to numbers and doing the above command

[dominicb]

Good morning pimpger

Is is possibe to have a SUMIF with two citeria?
For instance to SUMIF when a range fall in between two values?

Using Excel 2007 you can use more than one criteria in SumIf formulae.

As we're not, we can use SumProduct, thus :
=SUMPRODUCT(--(B1:B10>DATEVALUE("01/01/09")),--(C1:C10<DATEVALUE("28/07/09")),(A1:A10))

This will total any numbers in column A, where column B is greater than 01/01/09 and column C is less than 28/07/09.

HTH

DominicB

[DonkeyOte]

The other alternative is to use 2 SUMIF's

=SUMIF(A1:A17,">"&x,B1:B7)-SUMIF(A1:A17,">="&x+5,B1:B7)

If you ever find your two criteria to be constant (less common) the above approach can be shortened in terms of syntax, eg:

=SUMIF(A1:A17,{">2",">=7"},B1:B17)*{1,-1}