Formula To Aggregate Totals for Year

0
votes

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?

2
google-sheets
If column A are true dates then use a pivot table. – Scott Craner
@ScottCraner I will give that a wag. I am in Google Sheets, but I was able to put together a pivot table, I just need to be a bit smarter about how to use it. – ShaneK
if Google-Sheets why the Excel tag? Tag it appropriately and those who know Google-Sheets may be able to find a different or better route. – Scott Craner
share a copy of your sheet – player0
So essentially you want to add all the climbs from this specific year? Do you also have dates from past year? If not, why not just simply add them using an IF and SUM? @ShaneK – ale13

2 Answers

1
votes

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

0
votes

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);
}