CODEWITHSUNDEEP
google-sheets
ShaneK
ShaneK

Reputation: 303

Formula To Aggregate Totals for Year

I have a spreadsheet that I use to keep track of climbing progress (snippet shown below). I have formulas and graphs that keep track of counts of specific grades over time, but I am having trouble with a formula to keep a running total (by year) of feet climbed. I intent to put this in another sheet.

enter image description here

Basically I would like a single cell that does something like ... if Sheet1!A:A begins with "21." and if Sheet1!E:E,"<>*%" (which means I actually completed the climb) then add the rows total climb length (Sheet1!J:J * Sheet1!I:I) to the running total for that year.

What is the best way to do this?

Upvotes: 0

Views: 81

Answers (2)

ShaneK
ShaneK

Reputation: 303

Thanks Ale13 ... using your example and adding a couple of things (also needed to parseInt totals) ...

function calculateTotal() {
  
  let ss = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  let s7 = SpreadsheetApp.getActive().getSheetByName('Sheet7');
  let date = ss.getRange('A2:A').getDisplayValues();
  let type = ss.getRange('F2:F').getDisplayValues();
  let tries = ss.getRange('E2:E').getDisplayValues();
  let lengths = ss.getRange('I2:I').getDisplayValues();
  let laps = ss.getRange('J2:J').getDisplayValues();
  
  let btotal = 0;
  let rtotal = 0;
  
  for (let i =0; i<date.length; i++) {
    if (date[i][0].toString().startsWith('21') != false && tries[i][0].toString().includes('%') == false) {
       // Totals for Bouldering 
       if (type[i][0] == "B") {
         btotal = btotal + parseInt(lengths[i][0]*laps[i][0]);
       }
       // Totals for Top Rope or Sport
       else {
         rtotal = rtotal + parseInt(lengths[i][0]*laps[i][0])
       }
    }
  }
  
  console.log("Roped total = " + rtotal)
  console.log("Bouldering total = " + btotal)

  s7.getRange('B2').setValue(rtotal);
  s7.getRange('B3').setValue(btotal);
}

Upvotes: 0

ale13
ale13

Reputation: 6062

You can try using Apps Script and creating a script in order to manage your task.

So for example, you might want to take a look at the snippet below:

Code

function calculateTotal() {
  let ss = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  let date = ss.getRange('A2:A').getDisplayValues();
  let tries = ss.getRange('E2:E').getDisplayValues();
  let lengths = ss.getRange('I2:I').getDisplayValues();
  let total = 0;
  for (let i =0; i<date.length; i++) {
    if (date[i][0].toString().startsWith('21') != false && tries[i][0].toString().includes('%') == false) {
       total = total+lengths[i][0];
    }
  }
  ss.getRange('M2').setValue(total);
}

Explanation

The script above gathers all the values from the Sheet1 and loops through them. If the conditions check (the date should start with 21 and the E column does not contain %) then the corresponding length is added to the total; the total is then saved in the M2 cell in this case.

Further improvement

The advantage of using a script is that it is versatile and easier to manage. In this situation, you can make use of Apps Script's time-driven triggers; so assuming you plan on updating your spreadsheet every day at a specific time, you can create a trigger which will run right after it.

For example, the below function creates a trigger for the function above which will run every day at ~9.

function createTrigger() {
    ScriptApp.newTrigger("calculateTotal")
        .timeBased()
        .atHour(9)
        .everyDays(1)
        .create();
}

Reference

Upvotes: 1

Related Questions