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!

 

[DanteAmor]

Try:

=SUMPRODUCT((Sheet2!$B$2:$B$6=B2)*(Sheet2!$C$1:$CR$1>=D2)*(Sheet2!$C$1:$CR$1<=E2)*(Sheet2!$C$2:$CR$6))

Regards
Dante Amor

 

[kellexlsx]

=SUMPRODUCT(('Bridge Amount'!$B$2:$B$6=B2)*('Bridge Amount'!$A$1:$BV$1>=D2)*('Bridge Amount'!$A$1:$BV$1<=E2)*('Bridge Amount'!$A$2:$BV$6))

This is returning a value error on my example workbook?

 

[dreid1011]

Edit: Nvm, Dante's will be the better solution.

 

[dreid1011]

=SUMPRODUCT(('Bridge Amount'!$B$2:$B$6=B2)*('Bridge Amount'!$A$1:$BV$1>=D2)*('Bridge Amount'!$A$1:$BV$1<=E2)*('Bridge Amount'!$A$2:$BV$6))

This is returning a value error on my example workbook?

If you have two different sheets, that may be the issue, as your version of the formula is referencing the same sheet for all ranges. Maybe remove the sheet name (Bridge Amount) from the first range B2:B6.

 

[DanteAmor]

=SUMPRODUCT(('Bridge Amount'!$B$2:$B$6=B2)*('Bridge Amount'!$A$1:$BV$1>=D2)*('Bridge Amount'!$A$1:$BV$1<=E2)*('Bridge Amount'!$A$2:$BV$6))

This is returning a value error on my example workbook?

Check carefully where the ranges begin ($C$1 and $C$2) :

=SUMPRODUCT(('Bridge Amount'!$B$2:$B$6=B2)*('Bridge Amount'!$C$1:$BV$1>=D2)*('Bridge Amount'!$C$1:$BV$1<=E2)*('Bridge Amount'!$C$2:$BV$6))

 

[kellexlsx]

Thanks to both of you, I got it to work in the example. In my real workbook, the second worksheet is in a table format. It winds up looking something like this and returns an N/A error.

=SUMPRODUCT(($B$2:$B$76=B2)*(Client_Bonus_Payable[[#Headers],[1/1/2014]:[12/1/2029]]>=D2)*(Client_Bonus_Payable[[#Headers],[1/1/2014]:[12/1/2029]]<E2)*(Client_Bonus_Payable[[#All],[1/1/2014]:[12/1/2029]]))

 

[kellexlsx]

=SUMPRODUCT(($A$2:$A$76=$A2)*(Client_Bonus_Payable[[#Headers],[1/1/2014]:[12/1/2029]]>=$D2)*(Client_Bonus_Payable[[#Headers],[1/1/2014]:[12/1/2029]]<$E2)*(Client_Bonus_Payable[[1/1/2014]:[12/1/2029]]))

I realized I had included the headers from the table, and removing them eliminates the N/A error, but returns a 0 value. I'm still at a loss, unfortunately.

 

[Alex Blakenburg]

There seems to be a few issue with your table formula:
1) In $A$2:$A$76=$A2
We would expect $A$2:$A$76 to be a table column reference
2) Dates in Table headings are Text and need to be converted to dates before you try comparing to D2 and E2
3) You have <$E2, Dante had <=$E2.
If you don't put <= then if D2 and E2 are the same value it won't pick up anything.

So assuming column A is Name, it should look something like this.

=SUMPRODUCT((
Client_Bonus_Payable[Name]=$A2)
*(DATEVALUE(Client_Bonus_Payable[[#Headers],[1/1/2014]:[12/1/2029]])>=$D2)
*(DATEVALUE(Client_Bonus_Payable[[#Headers],[1/1/2014]:[12/1/2029]])<=$E2)
*(Client_Bonus_Payable[[1/1/2014]:[12/1/2029]]))

 

[kellexlsx]

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?