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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,356
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
54,356
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,057
Messages
5,835,162
Members
430,343
Latest member
t0m_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top