Should this be a SUMIF formula?

Question

I'm trying to make a formula that can recognize in Column A the name Brooke B for instance here, from there I'd like to SUM the values listed in Column I Cash Discounts for that specific user.

(Yes this user has no Cash Discounts, thus column I states "Non-Cash Payment").

There's about 80 users total here, so I'd prefer to automate the name recognition in Column A. Sheet: https://docs.google.com/spreadsheets/d/1xzzHT7VjG24UJ4ZXaiZWsfzroTpn7jCJLexuTOf6SQs/edit?usp=sharing

Desired Results listed in Cash Discounts sheet, listed per user in column C.

Answer 1

You are trying to calculate the total amount of the Cash Discount per person given to people in a list. You have data that has been exported from a POS system to which that you have added a formula to calculate the amout of the discount on a line by line basis. You have speculated whether the discount totals could be calculated using SUMIFS formulae.

In my view, the layout of the spreadsheet and the format of the POS report do not lend themselves to isolating discrete data elements though Google sheets functions (though, no doubt, someone with greater skills than I will disprove this theory). Column A, containing names, also includes sub-groupings (and their sub-totals) as well as transaction dates. There are 83 unique persons and over 31,900 transaction lines.

This answer is a script-based solution which updates a sheet with the names and values of the discount totals. The elapsed execution time is @11 seconds.

---

function so5882893202() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get the Discounts sheet
  var discsheetname = "Discounts";
  var disc = ss.getSheetByName(discsheetname);

  //get the Discounts data
  var discStartrow = 3;
  var discLR = disc.getLastRow();
  var discRange = disc.getRange(discStartrow, 1, discLR-discStartrow+1, 9);
  var discValues = discRange.getValues();

  // isolate Column A
  var discnameCol = discValues.map(function(e){return e[0];});//[[e],[e],[e]]=>[e,e,e]
  //Logger.log(discnameCol); // DEBUG

  // isolate Column I
  var discDiscounts = discValues.map(function(e){return e[8];});//[[e],[e],[e]]=>[e,e,e]
  //Logger.log(discDiscounts); // DEBUG

  // create an array to build a names list
  var names =[]

  // get the number of rows on the Discounts sheet
  var discNumrows = discLR-discStartrow+1;
  // Logger.log("DEBUG: number of rows = "+discNumrows);

  // identify search terms
  var searchPercent = "%";
  var searchTotal = "Total";

  // loop through Column A
  for (var i=0; i<discNumrows; i++){
    //Logger.log("DEBUG: i="+i+", content = "+discnameCol[i]);

    // test if value is a date
    if (Object.prototype.toString.call(discnameCol[i]) != "[object Date]") {
       //Logger.log("it isn't a date") 

      // test whether the value contains a % sign
      if ( discnameCol[i].indexOf(searchPercent) === -1){
        //Logger.log("it doesn't have a % character in the content");

        // test whether the value contains the word Total
        if ( discnameCol[i].indexOf(searchTotal) === -1){
          //Logger.log("it doesn't have the word total in the content");

          // test whether the value is a blank
          if (discnameCol[i] != ""){
            //Logger.log("it isn't empty");

            // this is a name; add it to the list
            names.push(discnameCol[i])

          }// end test for empty

        }// end test for Total

      } // end for percentage

     }  // end test for date

  }// end for

  //Logger.log(names);

  // get the number of names
  var numnames = names.length;
  //Logger.log("DEBUG: number of names = "+numnames)

  // create an array for the discount details
  var discounts=[];

  // loop through the names
  for (var i=0;i<numnames;i++){
    // Logger.log("DEBUG: name = "+names[i]);

    // get the first row and last rows for this name 
    var startrow = discnameCol.indexOf(names[i]);
    var endrow = discnameCol.lastIndexOf(names[i]+" Total:");
    var x = 0;
    var value = 0;
    // Logger.log("name = "+names[i]+", start row ="+ startrow+", end row = "+endrow);

    // loop through the Cash Discounts Column (Column I) for this name 
    // from the start row to the end row
    for (var r = startrow; r<endrow;r++){ 

      // get the vaue of the cell
      value = discDiscounts[r];

      // test that it is a value
      if (!isNaN(value)){

        // increment x by the value
        x = +x+value;
        // Logger.log("DEBUG: r = "+r+", value = "+value+", x = "+x);
      }

    }
    // push the name and the total discount onto the array
    discounts.push([names[i],x]);

  }

  //Logger.log(discounts)

  // get the reporting sheet
  var reportsheet = "Sheet10";
  var report = ss.getSheetByName(reportsheet);

  // define the range (allow row 1 for headers)
  var reportRange = report.getRange(2,1,numnames,2);

  // clear any existing content
  reportRange.clearContent();

  //update the values
  reportRange.setValues(discounts);


}

---

Report Sheet - extract