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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,214,498
Messages
6,119,892
Members
448,929
Latest member
Giovannicavuccio

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
Back
Top