Forum Discussion
Excel formula Help Needed SUMPRODUCT
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.
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.
7 Replies
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.
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.
Flora Sim , you are welcome
- I don't think you can do away with the days worked helper column because you have information in the summary to the right of the data you use (start and end dates) and it is not possible to manipulate this data the way you have it structured. You could restructure the way you enter data in the sheet!
In your current layout, you can do away with column E, by using an array formula in Column L ie enter it with Shift-Control-Enter
=SUM(($A$2:$A$19=$H3)*($C$2:$C$19)*($D$2:$D$19))
