CODEWITHSUNDEEP
excelsumproduct
mbadataguy
mbadataguy

Reputation: 23

Stuck on difficult Excel formula

I am looking for help with a sumproduct function that I cannot seem to figure out.

In the Total Revenue column(column C) I want to write a sumproduct(or any other suggestions) formula to multiply New Customers in Week 3(20,000) by Week 0 Retention(100%) and then also Week 0 Revenue/Week($0.12).

I am looking to use a sumproduct formula because I want to start adding all the cohort revenue together as I go down the spreadsheet. I am currently using the formula below but its getting the wrong output in cell C7. I thought it would yield $2400 for total revenue by multiplying 20,000 customers by 100% retention by $0.12 Revenue per week but instead its using the incorrect revenue per week cell. Instead of using L4 the formula is using L7.

Any guidance would be much appreciated.

=SUMPRODUCT(F$4:F7,SORT(I$4:I7),SORT(L$4:L7))

enter image description here

Upvotes: 0

Views: 132

Answers (1)

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2794

Try this array formula in cell C4:

=SUMPRODUCT((ROW(INDIRECT("1:"&ROWS($C$4:C4)))+(COLUMN(INDIRECT("RC1:RC"&ROWS($C$4:C4),FALSE))-1)=ROWS($C$4:C4))*1,$F$4:F4* TRANSPOSE($I$4:I4)*TRANSPOSE($L$4:L4))

Remember to confirm it with Ctrl+Shift+Enter.

Here an example of the two arrays whose product is sum to obtain the result in cell C8 (formulas are not the same for reproducibility's sake):

0,12 0,13 0,14 0,15 0,16
1 0,36 0,311 0,269 0,232
=(ROWS($A$3:A3)+(COLUMNS($A$3:A3)-1)=5)*1 =(ROWS($A$3:B3)+(COLUMNS($A$3:B3)-1)=5)*1 =(ROWS($A$3:C3)+(COLUMNS($A$3:C3)-1)=5)*1 =(ROWS($A$3:D3)+(COLUMNS($A$3:D3)-1)=5)*1 =(ROWS($A$3:E3)+(COLUMNS($A$3:E3)-1)=5)*1 0 =$G3*H$2*H$1 =$G3*I$2*I$1 =$G3*J$2*J$1 =$G3*K$2*K$1 =$G3*L$2*L$1
=(ROWS($A$3:A4)+(COLUMNS($A$3:A4)-1)=5)*1 =(ROWS($A$3:B4)+(COLUMNS($A$3:B4)-1)=5)*1 =(ROWS($A$3:C4)+(COLUMNS($A$3:C4)-1)=5)*1 =(ROWS($A$3:D4)+(COLUMNS($A$3:D4)-1)=5)*1 =(ROWS($A$3:E4)+(COLUMNS($A$3:E4)-1)=5)*1 0 =$G4*H$2*H$1 =$G4*I$2*I$1 =$G4*J$2*J$1 =$G4*K$2*K$1 =$G4*L$2*L$1
=(ROWS($A$3:A5)+(COLUMNS($A$3:A5)-1)=5)*1 =(ROWS($A$3:B5)+(COLUMNS($A$3:B5)-1)=5)*1 =(ROWS($A$3:C5)+(COLUMNS($A$3:C5)-1)=5)*1 =(ROWS($A$3:D5)+(COLUMNS($A$3:D5)-1)=5)*1 =(ROWS($A$3:E5)+(COLUMNS($A$3:E5)-1)=5)*1 0 =$G5*H$2*H$1 =$G5*I$2*I$1 =$G5*J$2*J$1 =$G5*K$2*K$1 =$G5*L$2*L$1
=(ROWS($A$3:A6)+(COLUMNS($A$3:A6)-1)=5)*1 =(ROWS($A$3:B6)+(COLUMNS($A$3:B6)-1)=5)*1 =(ROWS($A$3:C6)+(COLUMNS($A$3:C6)-1)=5)*1 =(ROWS($A$3:D6)+(COLUMNS($A$3:D6)-1)=5)*1 =(ROWS($A$3:E6)+(COLUMNS($A$3:E6)-1)=5)*1 20000 =$G6*H$2*H$1 =$G6*I$2*I$1 =$G6*J$2*J$1 =$G6*K$2*K$1 =$G6*L$2*L$1
=(ROWS($A$3:A7)+(COLUMNS($A$3:A7)-1)=5)*1 =(ROWS($A$3:B7)+(COLUMNS($A$3:B7)-1)=5)*1 =(ROWS($A$3:C7)+(COLUMNS($A$3:C7)-1)=5)*1 =(ROWS($A$3:D7)+(COLUMNS($A$3:D7)-1)=5)*1 =(ROWS($A$3:E7)+(COLUMNS($A$3:E7)-1)=5)*1 20000 =$G7*H$2*H$1 =$G7*I$2*I$1 =$G7*J$2*J$1 =$G7*K$2*K$1 =$G7*L$2*L$1
=SUMPRODUCT(A3:E7,H3:L7)

Copy and paste it in a new sheet. The first array is an array that will have 0 in each cell but those in the bottom-right/top-left diagonal. This will allow for the multiplication of the last "New Customers" value with the first "Retention" and "Revenue/Week" value, the second last with the second, the third last with the third and so on. The second array is simply a matrix with the multiplied values of each columns.

Upvotes: 0

Related Questions