Hello and thank you in advance for your advice. I am attempting to create a single formula to calculate the total cost in Column Z (hours * rate) where the rate (B5:E6) varies by a data range (B2:E3) and hours (F5:Y6) cross many months. Depending on the date in F4:Y4, multiply by correct rate and sum all of them together.
I have been researching sumproduct and arrays like crazy but have not been able to figure it out.
Capture.PNG
Your rates change mid-month. There's no way to know how many hours in Sep occurred before/after the rate change on the 10th.
Entia non sunt multiplicanda sine necessitate
=sumproduct(f6:y6*lookup($f$5:$y$5+9,$b$2:e6))
That answer is not correct, Tim.
AA5: {=SUMPRODUCT(G5:Z5, INDEX($C5:$F5, N(IF(1, MATCH($G$4:$Z$4, $C$2:$F$2)))))}
Note that I made the rates change at the beginning of the month.
That's it. I knew the first one wasn't correct and my buddies and I were trying to figure it out. I know it's correct and will keep reviewing it to make sure I understand how it works better. I have never used the N function so that, to me, is a key missing piece to the puzzle. Thank you shg.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks