SUMIF with multiple criteria

[Nomis_Eswod]

Hi,

I have a spreadsheet, with 2 columns that I'm trying to SUMIF. Column F is amount, and column G is date. I need to SUM column F if the date in column G falls between 2 dates, say 1/1/05 and 31/3/05.

Any ideas on how I can do this??

It would also be good to have the criteria as cell references, so that if I need to change either of the dates, the SUMIF will be calculated automatically.

Any help appreciated!

 

[Peter_SSs]

Assume lower criteria date is in I1, higher criteria date is in J1.

Try

=SUMPRODUCT(--($G$1:$G$100>I1),--($G$1:$G$100<J1),$F$1:$F$100)

Adjust ranges to suit but they must all be the same size and cannot be complete columns.

 

[Krishnakumar]

Hi,

=SUMIF(G1:G26,">"&I1,F1:F26)-SUMIF(G1:G26,">"&I2,F1:F26)

where I1 - 1/1/05 and
I2 - 31/3/05

HTH

 

[Nomis_Eswod]

That's great - thanks!

Any chance you can break it down? E.g. what the -- means? I did look at SUMPRODUCT but couldn't work out how to get it to evaluate something.

 

[Peter_SSs]

That's great - thanks!

Any chance you can break it down? E.g. what the -- means? I did look at SUMPRODUCT but couldn't work out how to get it to evaluate something.

SUMPRODUCT multiplies ranges together and adds the results, so the ranges need to be numbers. $G$1:$G$100>I1 returns a series of TRUEs and FALSEs -- (which is just 2 minus signs) converts TRUEs to 1s and FALSEs to 0s

That's all I have time for now, but if you want a fuller explanation of SUMPRODUCT and --, do a search of this board as the question has been asked many times. Look for answers by Aladin Akyurek as he has given a number of very informative explanations about this.

 

[Domenic]

That's great - thanks!

Any chance you can break it down? E.g. what the -- means? I did look at SUMPRODUCT but couldn't work out how to get it to evaluate something.

Here's an explanation by Aladin...

http://www.mrexcel.com/board2/viewtopic.php?t=133602