CODEWITHSUNDEEP
google-sheets
S1ARHE1
S1ARHE1

Reputation: 25

Sum between rows based on selected criteria (Google Spreadsheet)

everybody

enter image description here I am attaching the spreadsheet with an example & explanation: I was able to sum from start to some specific value, however I want to sum up values between 2 voluntarily selected rows based on set criteria.

spreadsheet

Thank you

Upvotes: 1

Views: 66

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34285

A basic non-array formula solution:

=sum(index(B:B,match(C8,A:A,0)):index(B:B,match(C9,A:A,0)))

enter image description here

Upvotes: 1

Emel
Emel

Reputation: 2462

This is an approximation using Custom Functions:

/**
 * Sum values based on criteria (only valid for 2 dimensional)
 * @param {Range} The 2 dimensional range
 * @param {string} A comma separated criteria
 * @return Sum based on criteria
 * @customfuncion
 */
function SUMCRITERIA(range, criteria) {
  criteria = criteria.split(',').map((letter) => letter.toUpperCase())
  return (range.reduce((pr, current) => {
    return criteria.includes(current[0].toUpperCase()) ? pr + current[1] : pr
  }, 0))
}

The function simply checks that the values included in the criteria are part of the left hand side of the range, if so, it adds them.

To add this function Extensions > Apps Script and copy paste inside. And then, use as normal:

Using example

Upvotes: 1

Related Questions