0

Say I have in Google Sheets:

  Price per month: $9
  Number of months: 3 (each subscription lasts 3 months)

Number of subscriptions per month. (A = month 1, B = month2…)

A  B  C  D  E
1  0  3  0  2

I’d like to create some routine that will sum the total cost per month.

Month 1 = 9.
Month 2 = 9.
Month 3 = 9 + (3*9).
Month 4 = (3*9)  (since the subscription from Month 1 is not expired)
Month 5 = (3*9) + (2*9)

Is this possible? Ideally, months would be in columns.

So my results would be:

A   B   C   D   E
9   9   36  27  45
  • I'm not sure I understand your comment. Month 1's subscription only impacts month 1/2/3. Not month 4. – Ed Landau Sep 1 '17 at 18:55
  • If subscriptions last 3 months, then subscription #1 is for Months 1,2 & 3. It will have ended by Month 4. Month 4 is (3*9) because there are 3 subscriptions that start in Month 3 (that are in their second month in month-4). – Ed Landau Sep 1 '17 at 19:26
  • Shit :) But you get what I am trying to do?? – Ed Landau Sep 1 '17 at 19:35
1

You just seem to need a three-month running total and a multiplier. In C3 and copied down:

=sum(B1:B3)*$A$2

Ensure the C1:C2 cells do not contain anything that would be taken as a number.

WA10937 example

  • YES. That is EXACTLY what I need except that if my number of months changes from 3 to 4, my equation has to change to "=sum(B1:B4)'. Hence my question. – Ed Landau Sep 1 '17 at 19:40
  • Maybe something like: =sum(B1:B(1+n)) (where "n" is the number of months)? – Ed Landau Sep 1 '17 at 19:41
  • Try =sum(indirect("B1:B"&(1+n))) then. – aknauft Sep 1 '17 at 19:58
  • @pnuts Thanks for catching that, and good point about the transition. – aknauft Sep 1 '17 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.