May 09 2019 05:54 AM
Please see attached workbook.
Currently, I calculate this manually using help column and it is very manual and time consuming.
Appreciate your help on a formula that I can place in Column L, so that I get rid of the helper columns of D and E.
Thank you very much.
May 09 2019 06:50 AM
May 09 2019 08:51 AM
Solution@Flora Sim , it's possible to avoid helper columns with formulas like this for the amount
=SUMPRODUCT( ($A$3:$A$20=$F3)*$C$3:$C$20* (OFFSET($B$3:$B$20,-1,0)-$B$3:$B$20)* ($B$3:$B$20<AGGREGATE(14,6,1/($A$3:$A$20=$F3)*$B$3:$B$20,1))* ($B$3:$B$20>AGGREGATE(15,6,1/($A$3:$A$20=$F3)*$B$3:$B$20,1)) )+ ($H3-AGGREGATE(14,6,1/($A$3:$A$20=$F3)*$B$3:$B$20,1))* INDEX($C$3:$C$20, MATCH(1,INDEX( ($A$3:$A$20=$F3)* ($B$3:$B$20=AGGREGATE(14,6,1/($A$3:$A$20=$F3)*$B$3:$B$20,1)), 0), 0))+ (AGGREGATE(15,6,1/($A$3:$A$20=$F3)*$B$3:$B$20,2)-$G3)* INDEX($C$3:$C$20, MATCH(1,INDEX( ($A$3:$A$20=$F3)* ($B$3:$B$20=AGGREGATE(15,6,1/($A$3:$A$20=$F3)*$B$3:$B$20,1) ), 0), 0))
See Sheet1, I added empty row after the headers of main table not to complicate formulas more. Perhaps it could be simplified, but in any case will be bit hard for the maintenance.
It's nothing wrong with helpers column, but only if they are help. In your case you generates it practically manually checking the names and dates and changing formula in helper column depends on their combination. I'd suggest to automate number of days column like
=IF(B2=AGGREGATE(14,6,1/($A$2:$A$19=$A2)*$B$2:$B$19,1), INDEX($I$3:$I$14,MATCH($A2,$G$3:$G$14,0)), $B1)- IF(B2=AGGREGATE(15,6,1/($A$2:$A$19=$A2)*$B$2:$B$19,1), INDEX($H$3:$H$14,MATCH($A2,$G$3:$G$14,0)), B2)
After that use summary formula for the amount as @Geoff1951 suggested, the only I'd avoid array formulas their possible, thus
=SUMPRODUCT(($A$2:$A$19=$G3)*$C$2:$C$19*$D$2:$D$19)
See Sheet2 attached.
May 09 2019 11:00 AM
This is genius work! Thank you so much. I almost gave up, I thought it could not be done without use of helper column. But your formula amazed me.
Thanks so very much.
May 09 2019 11:01 AM
May 09 2019 11:29 AM
You can achieve this by using this formula only. It requires no helper column.
with Control Shift Enter
=SUM(($A$2:$A$19=$E3)*($C$2:$C$19)*(IF($A$1:$A$18=$E3,$B$1:$B$18,$G3)-IF($A$3:$A$20=$E3,$B$2:$B$19,$F3)))
See attached file.
May 09 2019 03:08 PM
Wow! This is amazing. I never thought you could do it with this short formula.
Thanks very much!
May 09 2019 08:51 AM
Solution@Flora Sim , it's possible to avoid helper columns with formulas like this for the amount
=SUMPRODUCT( ($A$3:$A$20=$F3)*$C$3:$C$20* (OFFSET($B$3:$B$20,-1,0)-$B$3:$B$20)* ($B$3:$B$20<AGGREGATE(14,6,1/($A$3:$A$20=$F3)*$B$3:$B$20,1))* ($B$3:$B$20>AGGREGATE(15,6,1/($A$3:$A$20=$F3)*$B$3:$B$20,1)) )+ ($H3-AGGREGATE(14,6,1/($A$3:$A$20=$F3)*$B$3:$B$20,1))* INDEX($C$3:$C$20, MATCH(1,INDEX( ($A$3:$A$20=$F3)* ($B$3:$B$20=AGGREGATE(14,6,1/($A$3:$A$20=$F3)*$B$3:$B$20,1)), 0), 0))+ (AGGREGATE(15,6,1/($A$3:$A$20=$F3)*$B$3:$B$20,2)-$G3)* INDEX($C$3:$C$20, MATCH(1,INDEX( ($A$3:$A$20=$F3)* ($B$3:$B$20=AGGREGATE(15,6,1/($A$3:$A$20=$F3)*$B$3:$B$20,1) ), 0), 0))
See Sheet1, I added empty row after the headers of main table not to complicate formulas more. Perhaps it could be simplified, but in any case will be bit hard for the maintenance.
It's nothing wrong with helpers column, but only if they are help. In your case you generates it practically manually checking the names and dates and changing formula in helper column depends on their combination. I'd suggest to automate number of days column like
=IF(B2=AGGREGATE(14,6,1/($A$2:$A$19=$A2)*$B$2:$B$19,1), INDEX($I$3:$I$14,MATCH($A2,$G$3:$G$14,0)), $B1)- IF(B2=AGGREGATE(15,6,1/($A$2:$A$19=$A2)*$B$2:$B$19,1), INDEX($H$3:$H$14,MATCH($A2,$G$3:$G$14,0)), B2)
After that use summary formula for the amount as @Geoff1951 suggested, the only I'd avoid array formulas their possible, thus
=SUMPRODUCT(($A$2:$A$19=$G3)*$C$2:$C$19*$D$2:$D$19)
See Sheet2 attached.