Hey guys
I am looking for a single formula I can run along a single row that looks at several columns of data, and then checks if the date range falls between the running row, take it's relevant number, and multiply it by a single rate.
The attached probably explains it clearly - I think the solution would be some sort of Sumproduct, but my attempts have failed!Example - Date Range with Calculation.xlsx
Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Or:
Formula:Please Login or Register to view this content.
Regards, TMS
Oh awesome, thanks - is there anyway you can do it so that it uses the dates in row 2 to work out if it's between the period.
So take H3, it would look at if the date is bigger that G2, or =< than H2, rather than match via date function?
hi ps65,
See if using a helper column or two works for you. If not then keep asking and we can find a harder method.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Or, probably worst option, an Array Formula:
committed with Ctrl-Shift-EnterFormula:Please Login or Register to view this content.
Regards, TMS
I'm not entirely sure what you're asking, or why.
All the formulae that I have provided check from the first of the month to the month end date specified in row 2.
Have you tried them in your workbook?
Regards, TMS
If you do want to use just the dates in row 2, rather than the way I have done it, you will either need to include a dummy period end date at the beginning or have an inconsistent formula in the first cell.
I wouldn't be happy with either option but it shouldn't be difficult for you to do if that's what you wish. I repeat, for you to do.
Regards, TMS
OK, summary:
F1 =SUMIFS($C$9:$C$12,$B$9:$B$12,">="&DATE(YEAR(F$2),MONTH(F$2),1),$B$9:$B$12,"<="&F$2)*$B$4
F2 =SUMPRODUCT(($C$9:$C$12)*($B$9:$B$12>=DATE(YEAR(F$2),MONTH(F$2),1))*($B$9:$B$12<=F$2))*$B$4
F3 =SUM(IF($B$9:$B$12>=DATE(YEAR(F$2),MONTH(F$2),1),IF($B$9:$B$12<=F$2,$C$9:$C$12)))*$B$4 with CSE
F4* =SUMIFS($C$9:$C$12,$B$9:$B$12,">"&E$2,$B$9:$B$12,"<="&F$2)*$B$4
* Enter 31/12/2009 in cell E2 and Custom format as "End of Period"
Thanks, F4 was the solution I was looking for!Originally Posted by TMS
You're welcome.
Not sure why you think that's a better option than just working with the date in the header, therefore introducing a second column dependency but, whatever, your choice.
Regards, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks