SOLVED

Excel formula Help Needed SUMPRODUCT

Brass Contributor

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.

7 Replies
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))
best response confirmed by Flora Sim (Brass Contributor)
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.

@Sergei Baklan 

 

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.

@Geoff1951

thanks for your help as well.

@Flora Sim , you are welcome

@Flora Sim 

 

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.

 

@Jamil Mohammad 

 

Wow!   This is amazing. I never thought you could do it with this short formula.

 

Thanks very much!

1 best response

Accepted Solutions
best response confirmed by Flora Sim (Brass Contributor)
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.

View solution in original post