I am trying to do multiple things in one formula to figure out how much was earned on a project within a specific time period, based on the dates the invoice covers:
1. Look at sheet 1, row A, for any cell containing "Project M" (it will point to a cell here so I can have a drop down to select different projects)
2. For those that do contain "Project M" look in rows B and C (start and end date of invoice) to see if any of these days fall within start and end dates listed in sheet 2, cells a1, b1 (start and end date of period I'm calculating for, which will be month start and end dates)
3. If there are overlapping days, how many
4. Multiply that number by the $/day in Sheet 1, row 4
5. Sum all of these $$ together for any invoice for Project M to get a comprehensive
look at $$ earned month to month (versus received, since we are often paid in lump sums)
From this I will make a chart showing earned to actuals. I have a long complex thing mapped out for figuring out the number of days overlapping, but can't seem to get a few pieces right to make it lookup, sumif, etc. Thank you in advance for you help!
Bookmarks