SUMIF a date falls between to dates

[Chris Sanders]

Hi there,

I have a large data set and would like to sum a column if a certain date falls between two others, here is a sample of the data set;

A                    B                   C        D

18/02/2011	04/03/2011	AUD	10000000
04/03/2011	11/03/2011	AUD	17000000
11/03/2011	25/03/2011	AUD	18000000
18/03/2011	01/04/2011	AUD	6000000

On a second tab I am looking to sum column D if the date in column A on the second tab falls between the dates in Column A and B on the first tab;

Date	Total Out Standing  on date
01/01/2010	0
02/01/2010	0
03/01/2010	0

I tried using SUMIFS to have criteria for the date to be >= the first column date and =< the second column date but it doesn't appear to work. If someone could point out where I am going wrong that would be great thanks.

Kind Regards,

Chris  

[#2]

I have tried SUMIF.

And it turns out Zero. I didn't know why, either. Sorry.

[#3]

Chris,

your dates in the first table are from 2011 and the dates from the second table are from 2010. They will never match.

Thus the result is 0.

[#4]

Hi Detlef, 

I know this, this was just an example sample, the formula I am using also gives be 0 when when the dates are in the range, I'm looking at dates from 2010-2018 and the current set of data I'm testing on goes from 2011-2018, I'll be pulling data from further back when I can get the formula to work so I can see I'm getting the info I expect.

[#5]

Please don't give bad examples.

If I change 01/01/2010 to 20/03/2011 I get 24000000.

SUMIFS() works.

[#6]

Apologies, can you give me an idea of how you used SUMIF() as I can't get it to work?

[#7 · Accepted solution]

=SUMIFS(Tabelle1!$D$1:$D$4,Tabelle1!$A$1:$A$4,"<="&A2,Tabelle1!$B$1:$B$4,">="&A2)