What I'm trying to do is create an excel file that our company's stores can use to track newspaper returns and sales.
We then further use this file here at the HQ for reconciling the invoices on a monthly basis when they are sent to us.
ThE tables I'm using have the following data:
A B C D
1) 07/30…..$1.90…..FORMULA
2) 07/31…..$1.60…..FORMULA
3) 08/01…..$2.10…..FORMULA
4) 08/02…..$5.60…..FORMULA
5) 08/03…..$4.70…..FORMULA…… WEEKLY TOTAL(=SUM(B2:B?)
ETC ETC ETC ETC
In this example, my store managers simply track received papers and returned papers which are then totaled by cost to give the data (extended cost) in column B. The excel file is simply an ongoing, never ending list of values that are simply organized by date in the form of weekly blocks, which subtotal each week in column D. My managers need this column for weekly totals, as the weeks don't necessary coincide nicely. (i.e. you may have totals from August and September in 1 week's block)
I'm using the following formula to calculate monthly totals in a hidden column E (these are calendar month totals that run from the 1st of the month to the 31st of the month so its easy to do):
- FORMULA
=IF(MONTH(C5)<>MONTH(C5+1),SUM(H$5:H5)-SUMIF(C$5:C5,"<"&DATE(YEAR(C5),MONTH(C5),1),H$5:H5),"")
That works great, as it basically gives me a running total for the calendar month of the 1ST through the end of the month. It also eliminates all previous monthly bills using the SUMIF formula toward the end of the formula. The problem that I'm having is trying to recreate this with another newspaper that does not run on a set calendar month. The invoice, instead, is billed for the 16th of a month through the 15th of the next month.
The invoices for this other paper typically start on the 16th of a month and end on the 15th of the following month. So what I need is a formula (hopefully as similar to the one I mentioned using earlier so that I can understand how it works) that will sum for a given date range (i.e. 06/16/14 – 07/15/14) that won't show any from outside of that date range, even though the data is a giant list.
I wish that I could post images (need at least 10 reputation) as this would make this explanation 10 times easier, but it won't allow me to do so.
Best Answer
If I understand your question correctly, you should just be able to use SUMIFS to set multiple conditions for your date column. I assume C is your date column while H is your daily sales column.
=IF(DAY($C5)<>15,"",SUMIFS($H:$H,$C:$C,"<="&DATE(YEAR($C5),MONTH($C5),15),$C:$C,">="&DATE(YEAR($C5),MONTH($C5)-1,16)))
The above formula will add all values from the 16th of the previous month up to and including the 15th of the current month. Cells where the day does not equal 15 will be blank.
I tested this on a blank worksheet with about a year's worth of sequential dates in column C and random currency values in column H, with this formula in every cell in column I. So it should work for your purposes unless there's a piece of the puzzle I'm missing, so to speak. Let me know if it works for you!