Forum Discussion
SUMIF a date falls between to dates
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
=SUMIFS(Tabelle1!$D$1:$D$4,Tabelle1!$A$1:$A$4,"<="&A2,Tabelle1!$B$1:$B$4,">="&A2)
I have tried SUMIF.
And it turns out Zero. I didn't know why, either. Sorry.
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.
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.
Please don't give bad examples.
If I change 01/01/2010 to 20/03/2011 I get 24000000.
SUMIFS() works.
