Sum & Index

[kellexlsx]

Hey gurus! I always find great help here, so I'm hoping someone can help me streamline an archaic spreadsheet. I've shown a very basic version below to explain exactly what I'm looking for. Each employee will have an amount in the table on the second image for the range of dates specified in column C and column F on the first image. In column G, I need to calculate the sum of the monthly bridge amounts between the dates specified in columns D and E, as that will be the total that gets uploaded into our HR system. I played around with sum and index(match) and v/h lookups, but wasn't able to return a value. Thanks in advance!

 

[Alex Blakenburg]

Thanks for your reply.

1) My first data set, and the one where my formula is ultimately going to be located, is not formatted as a table, which is why the range is set to $A$2:$A$76=$A2.
2) I did not know table headers were always text, so thanks for sharing. I did add the datevalue calculations to the formula, which is now returning a value error.
3) I realized my end dates were going to cause an issue, as the ending dates in the first data set are set to the end-of-month, and the tables headers in the table are the first of the month. As it stood, the formula would incorrectly exclude the last month of the range. I moved the end dates in the first data set forward by one day and changed the <= to < in the formula in an attempt to capture that last month, but maybe that was the wrong way to go about it?

1) My first data set, and the one where my formula is ultimately going to be located, is not formatted as a table, which is why the range is set to $A$2:$A$76=$A2.

This doesn't seem to make sense, you are matching Column A with itself. Surely you are looking up A2 in the Client_Bonus_Payable table to select a row to return ?
Fix item 1 first or show us both sheets preferably using XL2BB but if you do an image show row and column identifiers.

2) I did not know table headers were always text, so thanks for sharing. I did add the datevalue calculations to the formula, which is now returning a value error.

Assuming you still get the value error after fixing 1 then above your first date heading type in =DATEVALUE(drag the headings from the first to last date)
You should get something that looks like this:

=DATEVALUE(Client_Bonus_PayableX[[#Headers],[1/1/2014]:[12/1/2029]])

Which columns are producing #VALUE.
If any do look show #VALUE then the heading is not a valid date. Look at the heading and see if it looks right. Especially ones where the year is 5 characters long. When you accidently duplicate a column it adds a suffix of 2, 3, 4 etc

If we get this far and it is still not producing the result you want we can look at item 3.