1

I have a spreadsheet of people subscribing to a service with a date column (B) and a 1 in column H for each name. I want to create a run total each month that automatically updates, so I get the sum of all people who joined each month. (I read through much of the information here, but find it very confusing.

Can anybody suggest something?

0

well, based on given + few assumptions (because your question isn't clear enought)... you can drop those "1"s in column H if you use it for SUM purposes, and add dates - either manualy or automaticaly with script (for example based on last row-edit)

script:

function onEdit(event)
{ 

  var sheet = event.source.getActiveSheet();

  // note: actRng = the cell being updated
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  var cindex = actRng.getColumnIndex();

  var dateCol = sheet.getLastColumn();  
  var lastCell = sheet.getRange(index,dateCol);
  var date = Utilities.formatDate(new Date(), "GMT+1", "dd.MM.yyyy");

  lastCell.setValue(date);
}

and then count your ocurances with something like:

=SUMPRODUCT(--ISNUMBER(H1:H);--YEAR(H1:H)=2018;--(MONTH(H1:H)=7))

how this works:

by adding script to the spreadsheet, everytime something in a given row shall change, an actual date will be printed on the end of that given row. then somewhere under all data or preferably in 2nd sheet you will add 12+ of those formulas that will sum your months as you request. (...where H1:H is column with dates, 2018 is for year and 7 is for 7th month)

enter image description here

  • That's an incredibly detailed answer, thanks for taking the time. You're right, my question was unclear. I made a copy of the sheet here: docs.google.com/spreadsheets/d/… - Dates are put in manually - column A for approaches and column B for joined - I'm trying to summarise this for each month, so I can automate some kind of reporting for it. Thanks again for your help – Michael Barker Jul 3 '18 at 4:15
  • @MichaelBarker yeah, just hit that + button on the bottom left to add Sheet2 and then paste this there: =SUMPRODUCT(--ISNUMBER(Sheet1!B2:B);--YEAR(Sheet1!B2:B)=2018;--(MONTH(Sheet1!B2:B)=6)) – user0 Jul 3 '18 at 4:28
  • Works - works really well - one last question - is it possible to count another column like H, I or J against a date range in B? So how many items in column H in June, July etc – Michael Barker Jul 3 '18 at 4:39
  • @MichaelBarker yes: =SUMPRODUCT(--ISNUMBER(Sheet1!J2:J),--(MONTH(Sheet1!B2:B)=6)) – user0 Jul 3 '18 at 5:43

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.