So I've got an amount, frequency, start date and end date in columns A, B, C & D respectively. What I've been looking to do (and struggling with immensely) is to find the total amount within a certain month. This is an example of the format Example.xlsx and I'd appreciate any help I can get, thanks.
You need to be more specific about this because the dates for a given Amount can span more than one month, and how you take into account the various frequencies. For example, what is the total in February for this?total amount within a certain month
Please Login or Register to view this content.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Well that's what I need to work out and is why I'm having so much trouble. So if the end date for a row creeps into February for example, I need to be able to work out by how any days and then the amount from that. Is this just beyond the range of excel?
Hi, welcome to the forum
highly unlikely.Is this just beyond the range of excel?
Given your sample data, what would the answer be, and how did you arrive at that?
Last edited by FDibbins; 05-16-2015 at 10:01 PM.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
We can get Excel to do whatever you want, but you have to know what you want first.
If you have an amount of 5 and frequency of "weekly" when do those 5 things happen relative to the week? In the example I gave, there is only one day in February included. Do any of those 5 things happen on that day? If so, how can you tell? If they happen continuously, like $5 paid per week, then the amount for that first day would be prorated at $5/7 = $0.71. But if it's discrete values like phone calls, you can't have 0.71 of a phone call.
To complicate matters the period you gave is 4 weeks plus 1 day for a weekly frequency.
It might help if you told us more about your problem, like what "amount" means and how "frequency" is used.
The attached uses helper columns. I assumed that the values in column A are rates per frequency unit; i.e., 2 per week, 5 per month, etc. Maybe it will give you some ideas for a start. Please click K 1 to select the month.
Ben Van Johnson
Here's my interpretation...
If any dates in the Start/End range span the From/To date range then sum the corresponding values in column A.
Data Range
So, in this example rows 2, 3, 4 and 5 are being summed.
This (crazy complicated) array formula** entered in A10:
=SUM(IF(IF(IF(C2:C7<=C10,C2:C7,C10)-IF(B2:B7>=B10,B2:B7,B10)+1<0,0,IF(C2:C7<=C10,C2:C7,C10)-IF(B2:B7>=B10,B2:B7,B10)+1),A2:A7))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
I'm thinking that DLL (the undisputed master of date formulas) can come up with something a lot less convoluted.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks