Hello - I am hoping someone can help me create a sumif statement looks a month, compares the month to a date range and pulls back amounts if the month falls within that range.
For example, I have a large list of transactions (see table). I would like to have a months as columns (Dec 15, Jan16, Feb16...) and have the sumif look to these dates, compare to my table and include if it falls within my ranges. My current formula uses >= equality signs but excludes transactions that span multiple months.
This is an example of my current statement (just to show my current line of thought) that excludes time spans over multiple months.
Column AS = volume
Column AP = Begin date
Column AQ = end date
Cell C4 = Start of the month
Cell C3 = End of the month
SUMIFS(Log!$AS$2:$AS$2000,Log!$AP$2:$AP$2000,">="&C$4,Log!$AQ$2:$AQ$2000,"<="&C$3)[TABLE="width: 500"]
<tbody>[TR]
[TD]Volume/month[/TD]
[TD]Begin date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]12/15/15[/TD]
[TD]12/30/15[/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD]12/1/15[/TD]
[TD]2/28/16[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]1/1/16[/TD]
[TD]1/31/16[/TD]
[/TR]
[TR]
[TD]700[/TD]
[TD]1/15/16[/TD]
[TD]6/30/16[/TD]
[/TR]
[TR]
[TD]800[/TD]
[TD]2/1/16[/TD]
[TD]2/28/16[/TD]
[/TR]
</tbody>[/TABLE]
For example, I have a large list of transactions (see table). I would like to have a months as columns (Dec 15, Jan16, Feb16...) and have the sumif look to these dates, compare to my table and include if it falls within my ranges. My current formula uses >= equality signs but excludes transactions that span multiple months.
This is an example of my current statement (just to show my current line of thought) that excludes time spans over multiple months.
Column AS = volume
Column AP = Begin date
Column AQ = end date
Cell C4 = Start of the month
Cell C3 = End of the month
SUMIFS(Log!$AS$2:$AS$2000,Log!$AP$2:$AP$2000,">="&C$4,Log!$AQ$2:$AQ$2000,"<="&C$3)[TABLE="width: 500"]
<tbody>[TR]
[TD]Volume/month[/TD]
[TD]Begin date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]12/15/15[/TD]
[TD]12/30/15[/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD]12/1/15[/TD]
[TD]2/28/16[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]1/1/16[/TD]
[TD]1/31/16[/TD]
[/TR]
[TR]
[TD]700[/TD]
[TD]1/15/16[/TD]
[TD]6/30/16[/TD]
[/TR]
[TR]
[TD]800[/TD]
[TD]2/1/16[/TD]
[TD]2/28/16[/TD]
[/TR]
</tbody>[/TABLE]