SUMIF with multiple criteria

Nomis_Eswod

Board Regular
Joined
Jul 27, 2005
Messages
153
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!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,486
Office Version
  1. 365
Platform
  1. Windows
Assume lower criteria date is in I1, higher criteria date is in J1.

Try
Code:
=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

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
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

Board Regular
Joined
Jul 27, 2005
Messages
153
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,486
Office Version
  1. 365
Platform
  1. Windows
Nomis_Eswod said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,335
Messages
5,571,600
Members
412,407
Latest member
ElmerCC
Top